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

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?

Find more posts tagged with

Formulas

Dashboards

Accepted answers

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.