[Solved] Leaderboard

[Gina]

Hello, I am trying to create a leaderboard that will use data from another spreadsheet and will be able to populate based on the month selected on the leaderboard.
I would like to be able to click on the month tab and the leaderboard automatically shows the ranking for that month. I have the leaderboard set up with ranking, but I do not know how to get the data (name and score) from one spreadsheet to go over to the leaderboard.

 

[shamimarita]

Hello Gina,

You can set up a dynamic leaderboard that pulls data from another spreadsheet and updates based on the selected month. Here’s a step-by-step approach:

1. Use the INDIRECT or IMPORTRANGE Function (depending on your Excel version): If your data is in another workbook, you can use formulas like
=[WorkbookName.xlsx]SheetName!A1

For Excel: Go to your leaderboard sheet and use a formula such as:

=VLOOKUP(Name, '[SourceWorkbook.xlsx]Sheet1'!A:B, 2, FALSE)

2. Make Your Leaderboard Month-Responsive: Add a dropdown or slicer to select the month.

Use the FILTER or SUMIFS function to pull only the data relevant to the selected month.
=FILTER(SourceData!A:B, SourceData!C:C = SelectedMonth)
or
=SUMIFS(SourceData!B:B, SourceData!C:C, SelectedMonth)

3. Automatically Rank the Scores: Use the RANK or SORT function to display the ranking based on the pulled data.

4. Combine Everything for Auto-Updating: When you select a month, the leaderboard will automatically update to show names and scores for that month, ranked accordingly.

 

[Gina]

Hello Gina,

You can set up a dynamic leaderboard that pulls data from another spreadsheet and updates based on the selected month. Here’s a step-by-step approach:

1. Use the INDIRECT or IMPORTRANGE Function (depending on your Excel version): If your data is in another workbook, you can use formulas like
=[WorkbookName.xlsx]SheetName!A1

For Excel: Go to your leaderboard sheet and use a formula such as:

=VLOOKUP(Name, '[SourceWorkbook.xlsx]Sheet1'!A:B, 2, FALSE)

2. Make Your Leaderboard Month-Responsive: Add a dropdown or slicer to select the month.

Use the FILTER or SUMIFS function to pull only the data relevant to the selected month.
=FILTER(SourceData!A:B, SourceData!C:C = SelectedMonth)
or
=SUMIFS(SourceData!B:B, SourceData!C:C, SelectedMonth)

3. Automatically Rank the Scores: Use the RANK or SORT function to display the ranking based on the pulled data.

4. Combine Everything for Auto-Updating: When you select a month, the leaderboard will automatically update to show names and scores for that month, ranked accordingly.

Thank you, I am still confused as I am guessing my setup is incorrect with the data. I know the functions but not how to properly use them.

 

[shamimarita]

Hello Gina,

You're welcome! No worries, setting up the structure can be a bit tricky at first. Here’s a simple example to help you visualize how your data and leaderboard can be set up:

1. Data Source Sheet Structure (e.g., “Data” sheet):

Name	Month	Score
Alex	Jan	50
Maria	Jan	60
Alex	Feb	40
Maria	Feb	70
…	…	…

2. Leaderboard Sheet Structure:

• Have a cell (like B1) where you select the month (e.g., using a dropdown with Data Validation).
• Use a formula to pull the names and scores for the selected month.

Example Formula to List Names for Selected Month:
=FILTER(Data!A2:A100, Data!B2:B100=B1)

Example Formula to List Scores:
=FILTER(Data!C2:C100, Data!B2:B100=B1)

To Rank the Scores Automatically:

• Use SORT to sort by the filtered scores:

=SORT(FILTER(Data!A2:C100, Data!B2:B100=B1), 3, -1)
Here, 3 is the column number for scores, and -1 means descending order.

 

[Gina]

YES! I got it to work! The only problem is that the formula seems to require a Month column o the leaderboard.

 

[shamimarita]

Hello Gina,

Yes, the formula typically relies on the Month column in your source data (the table with Name, Month, Score), but you do not need to display a Month column in your actual leaderboard.
The only place you need the month on the leaderboard sheet is for the dropdown (to select which month to show).

How to set it up:

• Keep the Month column in your source data (as in your second screenshot).
• On your leaderboard, just use the dropdown or cell for selecting the month.
• Your formula (like FILTER, SORT, or similar) should reference that dropdown cell and filter data from your main table accordingly.

If your current setup is requiring a Month column in the leaderboard itself, you can simplify it by:

• Making sure the formula looks up the selected month from your dropdown and matches it only against the source data’s Month column.
• Hiding or removing any unnecessary Month columns from your leaderboard display.

 

[Gina]

Thank you so much Shamimarita,
I got it all figured out and it looks great. I am now trying to complete a message board for when the spreadsheet is clicked on. I am looking at a message board because I think I can change the font and the color.

 

[shamimarita]

Hello Gina,

You’re very welcome, and I’m so glad to hear you got everything working and looking great!

For your message board idea, you can set one up in Excel! Here are a couple of simple ways to create a message or notice board that lets you customize font and color:

1. Use a Text Box (Insert > Text Box):

• You can type any message, format the text (font, size, color), and move the box anywhere you want on your sheet.
• To make it stand out, right-click the text box, choose “Format Shape,” and adjust fill, border, and shadow styles.

2. Use a Cell Range for Your Message Board:

• Merge a group of cells, type your message, and use the Home tab to format font style, size, and cell fill color.
• You can also add borders for emphasis.