1st - 5th highest value that meets criteria - cross-sheet referencing

tina.hutchinson52776

I have a Smartsheet entitled 'MASTER List' with the following information (see Image 1 below). The columns 2021, 2022 and 2023 use cross-reference formulas to count the number of times these courses appear in other sheets for the given years. All courses are categorised into one of seven categories: Health & Safety, Miscellaneous, Business, IT, Plant Training, Business, Highways & Maintenance.

I would like to calculate on a seperate Smartsheet entitled '2023 Top 5 Popular Courses per Category' using cross-reference formulas, the top 5 courses in each category (see Image below). I need one formula to caulculate the 1st, 2nd, 3rd, 4th and 5th highest value in each category, pulling this data from 2023 column from the sheet above.

I also need another formula to pull the Official Course Name which corresponds to the value.

For example, the most popular First Aid Course ranking with Popularity no. 1 in 2023 is First Aid at Work (Official Course Name), with 174 instances (Course Nos):

Nick Korna

Hi @tina.hutchinson52776,

On your Master List, you'll need to add a column for the rank in the category. This would have the formula:

=IFERROR(RANKEQ([2023]@row, COLLECT([2023]:[2023], Category:Category, Category@row)), "")

On your 2023 Top 5 Popular Courses per Category you can then use some INDEX/COLLECT functions to get the course names and numbers (where the cross sheet references refer to the relevant columns).

For course names:

=IFERROR(INDEX(COLLECT({Master List Official Course Name}, {Master List Rank in category}, [Popularity No.]@row, {Master List Category}, Category@row), 1), "")

For course numbers:

=IFERROR(INDEX(COLLECT({Master List 2023}, {Master List Rank in category}, [Popularity No.]@row, {Master List Category}, Category@row), 1), "")

The IFERROR portion of this is just so you can use these as column formulas and not have #INVALID V

Some very simplified & basic sheets (with top 3 in 2 categories) just to show the working:

Master List

Ranking sheet using cross sheet formulas:

Some things that I can see which may throw a slight spanner in the works are:

Your 7 stated categories does not include First Aid, but if this is just an oversight and there are actually 8 categories then all should be well!

If you have 2 courses with the same numbers in 2023 in the same category they will end up ranked the same which will throw things out. In this case I would suggest sorting data by category & rank and deciding which to rank over the other which would be double by making one a decimal and adding the ROUND function into the course number lookup.

Hope this helps, but if you've any problems or questions then just ask! 😊

Nick Korna

Hi @tina.hutchinson52776,

On your Master List, you'll need to add a column for the rank in the category. This would have the formula:

=IFERROR(RANKEQ([2023]@row, COLLECT([2023]:[2023], Category:Category, Category@row)), "")

On your 2023 Top 5 Popular Courses per Category you can then use some INDEX/COLLECT functions to get the course names and numbers (where the cross sheet references refer to the relevant columns).

For course names:

=IFERROR(INDEX(COLLECT({Master List Official Course Name}, {Master List Rank in category}, [Popularity No.]@row, {Master List Category}, Category@row), 1), "")

For course numbers:

=IFERROR(INDEX(COLLECT({Master List 2023}, {Master List Rank in category}, [Popularity No.]@row, {Master List Category}, Category@row), 1), "")

The IFERROR portion of this is just so you can use these as column formulas and not have #INVALID V

Some very simplified & basic sheets (with top 3 in 2 categories) just to show the working:

Master List

Ranking sheet using cross sheet formulas:

Some things that I can see which may throw a slight spanner in the works are:

Your 7 stated categories does not include First Aid, but if this is just an oversight and there are actually 8 categories then all should be well!

If you have 2 courses with the same numbers in 2023 in the same category they will end up ranked the same which will throw things out. In this case I would suggest sorting data by category & rank and deciding which to rank over the other which would be double by making one a decimal and adding the ROUND function into the course number lookup.

Hope this helps, but if you've any problems or questions then just ask! 😊

tina.hutchinson52776

@Nick Korna you are an absolute legend! Thank you so much for your help on this!!

Could you kindly amend the formuals you have provided to add the ROUND function into the course number lookup please as i'm not so good with formulas?

Your help is greatly appreciated. 😀

Nick Korna

No problem at all!

Safest option:

=IFERROR(ROUNDDOWN(INDEX(COLLECT({Master List 2023}, {Master List Rank}, [Popularity No.]@row, {Master List Category}, Category@row), 1), 0), "")

This lets you put any decimal in and it will take only the whole number and always round down.

Adding the first digit of the data as a decimal, for comparison:

If you're certain not to put anything 0.5+ in then you can just use the ROUND function instead (just remove the DOWN part), but if have anything over a half it will get rounded up (in the example above using this, F would be 61, E would be 51, the others would remain as they are).

If there is anything else, just ask! 🙂