Best formula to use, maybe VLOOKUP

Carol-Anne Cerbone

Hi!

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.

L_123

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å

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!

Best,

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!

L_123

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.

Carol-Anne Cerbone

Thank you for the suggestion! I think adding in the conditional paths within the workflow will work.

Andrée Starå

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!

Best,

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!

Carol-Anne Cerbone

Thanks Andree! Another great suggestion.

Andrée Starå

@Carol-Anne Cerbone

Excellent!

You're more than welcome!