Pull data from excel and update Smartsheet

AML
AML ✭
edited 12/09/19 inSmartsheet Basics

Hi everyone..

I am new to the community..so hopefully my question is not too stupid:D

I am currently using Excel to calculate a value by using the normal distribution formula. I would like to pull this value from excel and then update a cell in Smartsheet. It is an on-going exercise so the fetch should be done regularly (ideally real-time). May I know which is the best way to achieve this? Data uploader? Data connector?

Thank you.

Tags:
· Share on Facebook Share on Twitter

Comments

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi,

    我们lcome to the community!

    Is it just one cell?

    It sounds to me that Zapier would be an excellent option for this scenario. Is that an option for you?

    有一个很好的一天!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • AML
    AML ✭
    edited 07/04/19

    hello Andrée!

    Thank you for your prompt response. It will be multiple cells..for every row..

    My objective is to get around the fact that Smartsheet doesn't support 'Normal Distribution', my idea is like below:

    1. Whenever user updates column A in Smartsheet

    2. It triggers to copy and paste the row in excel with the value from Smartsheet.

    3. Excel calculates the required value based on the Smartsheet values by using Normal Distribution formula

    3. Smartsheet pulls the calculated data from Excel and update column C on Smartsheet

    Am I over-complicating the whole setting? haha

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    No, I don't think you're over-complicating it because it's something that you need.

    I think it would be possible to set it up with the help of Zapier or similar.

    Would that work?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • AML
    AML ✭

    Thank you very much for your confirmation.

    I will take a look at Zapier:)

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Let me know how it goes!

    Have a fantastic weekend!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Categories

How exactly is your Data Shuttle set up? If it is set to replace everything in the sheet when it runs, it is deleting the cell being referenced by the widget and then putting new data in new cells.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":292,"urlcode":"Dashboards","name":"Dashboards"},{"tagID":474,"urlcode":"data-shuttle","name":"Data Shuttle"}]},{"discussionID":108213,"type":"question","name":"How to spread costs evenly across months, if they fall within those dates","excerpt":"I have various financial line items that span different dates. I'd like the costs to be spread per month automatically based on the date and cost entry. I've tried various functions already, IF, AND, and it says Unpearsable. I'd like to spread out the costs in the Cost column to the months on the right, based on the start…","snippet":"I have various financial line items that span different dates. I'd like the costs to be spread per month automatically based on the date and cost entry. I've tried various…","categoryID":321,"dateInserted":"2023-07-27T19:03:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T21:12:20+00:00","insertUserID":164209,"insertUser":{"userID":164209,"name":"AshwiniBiotech2023","title":"Sr. Director, Project Management","url":"https:\/\/community.smartsheet.com\/profile\/AshwiniBiotech2023","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T23:10:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164209,"lastUser":{"userID":164209,"name":"AshwiniBiotech2023","title":"Sr. Director, Project Management","url":"https:\/\/community.smartsheet.com\/profile\/AshwiniBiotech2023","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T23:10:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":14,"countViews":63,"score":null,"hot":3381071762,"url":"https:\/\/community.smartsheet.com\/discussion\/108213\/how-to-spread-costs-evenly-across-months-if-they-fall-within-those-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108213\/how-to-spread-costs-evenly-across-months-if-they-fall-within-those-dates","format":"Rich","lastPost":{"discussionID":108213,"commentID":387925,"name":"Re: How to spread costs evenly across months, if they fall within those dates","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387925#Comment_387925","dateInserted":"2023-07-28T21:12:20+00:00","insertUserID":164209,"insertUser":{"userID":164209,"name":"AshwiniBiotech2023","title":"Sr. Director, Project Management","url":"https:\/\/community.smartsheet.com\/profile\/AshwiniBiotech2023","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T23:10:16+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/IBAVVE3YLO8D\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T09:38:32+00:00","dateAnswered":"2023-07-27T19:21:12+00:00","acceptedAnswers":[{"commentID":387646,"body":"

You would use the below for Jan 2023 and adjust the month and year numbers accordingly for each of the other months.<\/p>

=IF(AND(MONTH([Start Date]@row)<= 1, YEAR([Start Date]@row)<= 2023, MONTH([End Date]@row)>= 1, YEAR([End Date]@row)>= 2023), [$ per month]@row)<\/p>"},{"commentID":387901,"body":"

Lets try a different approach.<\/p>

=IF(AND(VALUE(YEAR([Start Date]@row) + IF(MONTH(Start Date]@row)< 10, \"0\", \"//www.santa-greenland.com/community/discussion/52236/\") + MONTH([Start Date]@row))<= 202307<\/strong>, VALUE(YEAR([End Date]@row) + IF(MONTH([End Date]@row)< 10, \"0\", \"//www.santa-greenland.com/community/discussion/52236/\") + MONTH([End Date]@row))>= 202307<\/strong>), [$ per month]@row)<\/p>


<\/p>

Basically we are creating a yyyymm stamp from the start and end dates and comparing them to the yyyymm stamp for that year\/month combo. The above is for July 2023 (202307).<\/p>"},{"commentID":387906,"body":"

@AshwiniBiotech2023<\/a> Check out my last comment.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108239,"type":"question","name":"Is an automated export of a Smartsheet to an Excel file possible?","excerpt":"Hi all, I have been having the most frustrating conversation with Smartsheet support on this topic and not getting answers to the question that I asked. I would like to set up an automation that will email me an Excel copy of my project plans on a weekly basis (or when triggered with a change - I don't really care) as I…","snippet":"Hi all, I have been having the most frustrating conversation with Smartsheet support on this topic and not getting answers to the question that I asked. I would like to set up an…","categoryID":321,"dateInserted":"2023-07-28T02:36:12+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T06:42:13+00:00","insertUserID":164243,"insertUser":{"userID":164243,"name":"Candace G","title":"Senior Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/Candace%20G","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T06:50:48+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":137040,"lastUser":{"userID":137040,"name":"Gia Thinh","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Gia%20Thinh","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pXMt6Y5Kqjs!ZHDtmZp0Yms!Dwma-t47hzh","dateLastActive":"2023-07-28T23:47:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":41,"score":null,"hot":3381040105,"url":"https:\/\/community.smartsheet.com\/discussion\/108239\/is-an-automated-export-of-a-smartsheet-to-an-excel-file-possible","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108239\/is-an-automated-export-of-a-smartsheet-to-an-excel-file-possible","format":"Rich","tagIDs":[268,334,440,445,510],"lastPost":{"discussionID":108239,"commentID":387741,"name":"Re: Is an automated export of a Smartsheet to an Excel file possible?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387741#Comment_387741","dateInserted":"2023-07-28T06:42:13+00:00","insertUserID":137040,"insertUser":{"userID":137040,"name":"Gia Thinh","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Gia%20Thinh","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pXMt6Y5Kqjs!ZHDtmZp0Yms!Dwma-t47hzh","dateLastActive":"2023-07-28T23:47:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T06:32:42+00:00","dateAnswered":"2023-07-28T05:49:42+00:00","acceptedAnswers":[{"commentID":387737,"body":"

Hi Candace,<\/p>

The answer is YES. You can use \"Send as Attachment\" feature in the File menu of your sheet to setup the schedule for your need. You should be a licensed user to use this feature as well (I remembered that). <\/p>

Hope this works for you.<\/p>

\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n
\n
\n \n \"image.png\"<\/img><\/a>\n <\/div>\n<\/div>\n


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":268,"urlcode":"Attachments","name":"Attachments"},{"tagID":334,"urlcode":"automations","name":"Automations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"},{"tagID":445,"urlcode":"importing-and-exporting","name":"Importing and Exporting"},{"tagID":510,"urlcode":"email-notifications","name":"Email Notifications"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=321&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":4892,"limit":3},"title":"Trending in Smartsheet Basics","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Smartsheet Basics

  • Dashboard Metric Breaks on Update of DataShuttle

    I am sure there is something I am missing. But I try to set a Dashboard Matrix on a field that is refreshed by Data Shuttle. But when the Grid refreshes it breaks the Metric and says Data missing even though there is data in the field. Is there anyway around this or does Data Shuttle always break a Metric Cell?

  • How to spread costs evenly across months, if they fall within those dates

    I have various financial line items that span different dates. I'd like the costs to be spread per month automatically based on the date and cost entry. I've tried various functions already, IF, AND, and it says Unpearsable. I'd like to spread out the costs in the Cost column to the months on the right, based on the start…

  • Is an automated export of a Smartsheet to an Excel file possible?

    Hi all, I have been having the most frustrating conversation with Smartsheet support on this topic and not getting answers to the question that I asked. I would like to set up an automation that will email me an Excel copy of my project plans on a weekly basis (or when triggered with a change - I don't really care) as I…

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video