Create Leaderboard with growing, non-static list

Ross Novotny
Ross Novotny โœญโœญโœญโœญโœญ
edited 10/29/21 in Formulas and Functions

I would like to create a leaderboard report using a list of names that is non-static. Meaning I have a list of 20 names today and I could create a leaderboard from that data, but the source sheet is going to have names added to it over time and I would like to the leaderboard to reflect that growth. So when 21st, 22nd, etc name is added to the list, those individuals would automatically appear on the leaderboard. Each name on the list could be listed from 1 to 100+ times.

I would imagine that I would have a secondary grid that would do a vlookup on the master grid column and find any name that is not blank, but also not repeat the entry. So it would find each name once on the master sheet, then the second column would just be a CountIf. Below is a look at the idea, except the "Source list" will continue to grow so I can't predict the future names on that list. "Leaderboard" and "Sales" would be on a different sheet.

image.png


****UPDATE*******

I found related help in another discussion by the brilliant @Paul Newcome here (https://community.smartsheet.com/discussion/57396/generate-list-of-unique-values-index-distinct-vlookup). But I did add one enhancement on the master sheet. In the helper row I added an automation that whenever a new row is added (I just selected a specific field that will always exist when a new row is added), then "Change Cell Value".

=IF(COUNT(DISTINCT([Name]$1:[Name]@row)) <> COUNT(DISTINCT([Name]$1:[Name]@row - 1)), COUNT(DISTINCT([Name]$1:[Name]@row))). This will ensure as your master list grows it continues to add the helper row number if its a unique name.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!