Automatically update Unique names in a ranking sheet from a survey intake sheet
Hi All,
I am currently building a system to rank speakers. I am try to copy unique names from the survey sheet to the ranking sheet so that I can sort them by some statistics. However, I am having trouble figuring out an automation that correctly identifies the unique names in the survey sheet and also updates the ranking sheet automatically with said unique name. Any suggestions?
Answers
-
You will first need to establish the maximum number of unique speakers and then decide on a number greater than that so that you have a buffer included.
From there you would have a text/number column (called "Number" in this example) with the numbers one through whatever number you decided on manually entered going down the rows.
Then in the column you want the unique speaker names in, you would use something along the lines of:
=INDEX(DISTINCT(COLLECT({Source Sheet Speaker Column}, {Source Sheet Speaker Column}, @cell <> "")), Number@row)
-
What if I dont know what the maximum will be. The list of speakers grows over time. And would there be any other way of putting those numbers in automatically because inputting 600+ numbers in is a bit of a hassle
-
You can include a flag type column with a formula counting how many unique names are in the source and flagging rows whenever you get within a certain threshold. You would then create an automation to run daily (since they can't be set to trigger off of a field containing a cross sheet reference) to alert you if you are starting to get close. Let's say you wanted an alert if you were within 10 of how many you have already pre-populoated. That formula would look like this:
=IF(COUNT(DISTINCT(COLLECT({Source Sheet Speaker Column}, {Source Sheet Speaker Column}, @cell <> ""))) > MAX(Number:Number) - 10, 1)
-
Hey Paul, so i trying to get your way to work. However, it is leaving this massive gap in my sheet with errors,
There are unique names before and after this gap of error and im not sure why it is like that. do you know what is going on here?
-
You can wrap your formula in an IFERROR statement to get it to output a blank until there is a name.
=IFERROR(current_formula, "")
-
There are values after the errors too. Shouldnt it replace the error cells above with the correct info? doesnt the fact that there is a error value there mean that it is reading the name from the source sheet and not somehow not printing it out correctly?
-
It is because your number column is out of order. Once you sort the Number column so that it is 1 at the top and the last number at the bottom, the errors will only be at the bottom of the list indicating there aren't enough distinct names to fill that far down.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives