Best formula to use, maybe VLOOKUP


Need some help. I'm hoping to describe my issue the best I can....

I have created a form that end users will complete. Based on information in the form, I will need to move the row to a new sheet. I have over 400 sheets (each sheet represents a store / location) and I didn't realize that you are only able to have 150 automated workflows setup so that didn't work. So, I thought about using nested IF statements but realized you can only have a maximum of 4,000 characters, so that won't work.

I could create multiple forms based on each region of the USA but that could become messy for the end user.

Is there another formula I could use? I thought about using VLOOKUP but not sure how that would work. I could put all of the store / locations into a sheet for the VLOOKUP but I haven't used VLOOKUP before so I'm not sure how to do it. The end user is required to select their store in a drop down list on the form.

Best Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    You can only have 150 automated workflows, but you can send to different sheets depending on the criteria in the same workflow. I don't know what the max is, but I have a few that are ~30. You'll have to build a few workflows, but smartsheet should be able to handle it.

    If you don't want to do that, you can use an index(collect()). I can go into more detail on this if you like, but the data would stay on the main sheet, and just be referenced on the destination sheets.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @Carol-Anne Cerbone

    I hope you're well and safe!

    To add to L_123's excellent advice/answer.

    Another way could be to use multiple so-called helper sheets, move the rows to them, and move them from them to the Destination Sheets.

    Would that work/help?

    I hope that helps!

    Have a fantastic week & Happy Holidays!


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

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!


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

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

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!