How to Structure Formulas so if dropdown options change, it auto-updates?

kss5229

I am trying to compile metrics and get them to auto-update to reflect the values of a drop down. For instance, if the drop down has options like "Physical Exam", "Digital Exam", "Advanced", I'm building columns in my metrics sheet to tabulate the totals for each using countif statements (for each respective month). E.g.:

=COUNTIFS({Exam}, @cell = "Digital Exam", {Date}, AND(IFERROR(MONTH(@cell ), 0) = MONTH([Start Date]@row), IFERROR(YEAR(@cell ), 0) = YEAR([Start Date]@row)))@cell

I would like to find a way to make the formulas robust such that revisions to the drop-down list options will automatically update. So if "Digital Exam" gets updated at a later date to "dig. exm", the formula will automatically update as well.

The only thing I can think of to do this is if I were able to populate the drop-down options in a range of cells and then have the formulas pull from within that specific range. I know you can do that type of thing thing in excel, but I'm uncertain if you can do that in Smartsheet. From looking online, it seems like DataShuttle might allow that type of thing, but I only have an Enterprise account.

markkrebs

No easy way to do this automatically. To prevent renames I'd suggest locking the drop-down to values only.

kss5229

The drop down is locked to values only, but the names may officially change, requiring the drop down options to be updated.

Jason Tarpinian

INDEX(DISTINCT may help you here. It technically isn't looking at the actual dropdown options, but the values themselves. But it will return to you the nth value within a range, and from there, you could do all the metrics that you want. If you change a name in the dropdown, it won't show up on the DISTINCT range until it's been selected as a value. You can wrap it in an IFERROR to get rid of the blanks (since in my solution, there is no 5th distinct value) that way your list would still have room to grow as well.

=INDEX(DISTINCT([Dropdown List]:[Dropdown List]), [nth Dropdown Selection]@row)

kss5229

Hmm, that might be my best option. It's not ideal for this situation because several of the drop down options that have never been selected will still be tabulated to show that they are zero. So that solution won't automatically accommodate calculating the metrics for those.