How do I get my excel formulas to work in smartsheet. I am getting #blocked & #unparseable

Options

Answers

  • Jen Lange
    Jen Lange ✭✭✭✭✭
    Options

    @Katherine Byrne , can you paste in the formula that your trying to put into Smartsheet?

  • Katherine Byrne
    Options

    I am very much a newbie here. I appreciate the response!

    ='Store Capacity'!H10

    =SUM(D7,E7)

  • Jen Lange
    Jen Lange ✭✭✭✭✭
    Options

    @Katherine Byrne the main difference I've found between Excel and Smartsheet formula writing is the references to cells, rows and columns. The naming convention is different. This article is great for learning the Smartsheet convention for cell and column references - https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula.

    Also, some of the functions are named differently. A great reference to the available formulas (or functions) is located here - https://help.smartsheet.com/functions.


    To truly help revise your formulas listed above, I would need to know your column and/or row names or see a snippet of your sheet. However, I'll take a stab to see if I can guide you in the right direction without it.

    Just like in Excel, if you want one cell (or field) to equal a cell, you can type "=" in the cell/field and then click on the cell you want it to equal or pull from. So instead of the single quote and exclamation mark and cell name format, it would be written as:

    If in the same sheet and the column name includes a space...

    =[Store Capacity]@row or =[Store Capacity]10

    If in a different sheet, assuming your cell or range selection is named "Store Capacity"...

    ={Store Capacity}


    Same process for the structure of the SUM formula. Since I don't know the names of your columns, replace "Column D" and "Column E" with the actual column names:

    =sum([Column D]7,[Column E]7)


    I hope this is helpful. If you need more guidance, attach a screenshot of the cells or ranges your trying to reference and I can adjust the formulas more precisely.


    *Side note: are you in retail? Tracking store capacity values is right up my alley ;-)

  • Katherine Byrne
    Options

    Thanks so much Jen. I have a meeting later this week with a co- work to get a bit more guidance. If i don't get what I need I will likely reach back about.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    For that first one, you are going to want to look into Cross Sheet References.


    The second one should translate directly as long as you update the column names as @Jen Lange suggested.

  • Jen Lange
    Jen Lange ✭✭✭✭✭
    Options

    Good luck, @Katherine Byrne. Don't hesitate to reach out it you have any follow up questions.

    Also, thank you for your engagement and contribution to our community. Your inquiry is likely to assist other members across time.

    If you appreciate my response, please recognize the effort by accepting my answer, as well as with an "Insightful" or "Vote Up" selection. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!