I have an Intake Sheet in which someone can select multiple Locations and multiple Fiscal Years that the Initiative will occur in.
What I need is for this row to copy to the Retail Master Sheet a total of 6 times - one for every possible combination of the selected Location and Fiscal Year.
DESIRED RESULT -
My current workaround for the copy is to create a group of Automations for each possible Location, have a COUNTM helper column for the Fiscal Year, and have the Automation run that many times based on the COUNTM number.
- Is there an easier way to do this? In my Intake Sheet, I have 18 different locations and 7 possible Fiscal Years, so I am worried about having too many/too heavy automations doing it the way I am.
- I am aware that I will likely need two helper columns, one each for Fiscal Year and Location, to select one of the copied information to display. I originally thought to do this through creating a Duplicate Index and then using that count to pull which multi-dropdown entry into the helper, but I am unsure how to make that happen with both the FY column and the Location column (I can make it do that for one of the two, but not both).
I do need these broken out into separate rows for each location/year because we will be tracking different information, such as $ Spent, across each location and year.
*It would also be super cool if I could automatically make these copied rows children of a parent row that just has the original info, but I'm 98% sure automatic hierarchy is not a thing Smartsheet can do yet without one of the add-ons (which one?). I may end up doing this through reports though.
