Create Leaderboard with growing, non-static list

Ross Novotny

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.

****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.

Genevieve P.

Hi @Ross Novotny

I'm glad you were able to find a solution by @Paul Newcome!

It looks like you've also tweaked it to suit your own use-case, which is great. Do you need any other help or did this resolve your question?

Cheers,

Genevieve

Genevieve P.

Hi @Ross Novotny

I'm glad you were able to find a solution by @Paul Newcome!

It looks like you've also tweaked it to suit your own use-case, which is great. Do you need any other help or did this resolve your question?

Cheers,

Genevieve

Paul Newcome

@Ross Novotny Glad you were able to find one of my posts helpful.

The automation you have set up though almost sounds like you are just replicating the auto-fill feature. My solution was also pre-column formula. There is a way to adapt this so that you can use column formulas instead and not have to rely on autofill of automations. Basically you would insert an auto-number column then a text/number column with a basic MATCH function looking at the Auto-number column to essentially output the row number on each row.

Now you can add in a COLLECT function, reference entire columns, and include the range/criteria set of the row number being less than or equal to the current row "@row". Since you are no longer locking in references, you can convert it to a column formula and should be squared away (assuming that auto-fill wasn't working for you for some reason).

Ross Novotny

@Paul Newcome I am guessing your idea might be more solid, but it seems my automation is working currently. I may have to revisit your idea if it starts failing long term. Thanks for the help!