Can I get Unique values on a metric sheet without a complicated work around or report?

Aaron Gilbert

I have a column with associate names, over 3,000 rows with over 100 unique names. In order to provide dashboards, I need a dynamic list of unique names listed in that column on a sheet I use to build our KPI metrics. Easy in excel or any other dashboarding tool I use, is this now possible in Smartsheet without using the complicated helper column workaround?

There are well over 100 associates, frequent turnover, and I can not ask the business unit to maintain an active roster when they already have to in 3 other systems. Because of the the limitations reports have when using graphs with more complexity than a simple count, I can't use a report. Once I help build the dashboard I will be passing it off to the business unit and not involved with any maintenance or QA, so I would prefer to avoid complicated workarounds if at all possible.

Has this functionality been added through either native column formulas or workflows?

Paul Newcome

There is a method using a single helper column. it is a text/number column where you manually enter the numbers one through however many you anticipate needing (plus a buffer). If you expect a maximum of 150 unique entries, I would suggest going to 200 just to be safe. Really though you can go into the thousands to allow for expansion.

From there you only need a single column formula along the lines of

=IFERROR(INDEX(DISTINCT(COLLECT({Name Range}, {Name Range}, @cell <> "")), [Helper Column]@row), "")

Aaron Gilbert

https://community.smartsheet.com/discussion/comment/417140#Comment_417140

Thanks Paul! I need to keep you on speed dial, I feel like you answer all the questions in the SS Community.

This is likely the solution I will have to use, but I would love to see Smartsheet eventually build this functionality. It could be easily added as a native function, workflow even by allowing for sheet formulas to reference reports. I do appreciate the help while we wait for that though.