Best Of

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! 😊

Hi @Ipshita ,

Thank you so much for your time and response and yes, you understood correctly. Fortunately, I was given guidance on this question elsewhere recently and was able to figure out the solution to my problem. The formula I had used was:

IF(COUNTIF(CHILDREN()), 1) / COUNT(CHILDREN()) = 1, 1, 0).

This worked flawlessly for both the CHILD row and PARENT row as it helped automate the checking process.

That limit isn't strictly relegated to cross sheet references (blue or black arrows). If I have a column formula referencing a column in the same sheet [Column Name]:[Column Name], that counts towards that limit as well.

@Genevieve P. I remember we recently had a discussion on another thread where you were able to provide some really good insight into the cells being referenced limitations, but I can't seem to find it again. Would you happen to have a link handy?

You are missing a closing parenthesis on the first line - ISDATE([Payment Start Date]@row, - and you have an extra closing parenthesis at the very end. This should fix it:

=IF(AND(ISDATE([Payment Start Date]@row), ISNUMBER([Payment Term Months]@row)), DATE(YEAR([Payment Start Date]@row) + FLOOR((MONTH([Payment Start Date]@row)) / 12, 1), MOD(MONTH([Payment Start Date]@row) + [Payment Term Months]@row, 12), DAY([Payment Start Date]@row)), "")