Show sheet name with highest value

[MorphiusX]

So I have 101 sheets where 100 of these sheets are players names and 1 sheet will be the master ranking sheet.

Each sheet has an entry every day of there growth and % grown that day.

What I would like to have is a leaderboard type setup where the people have have grown the most in % that day be displayed in the ranking sheet

Here are the player sheets and ranking sheet with what I want. Any advice would be much appreciated

playerSheet.png

rankSheet.png

[zbor]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[MorphiusX]

So in the example workbook I had added comments to the required cells and also some text boxes to explain what is happening where needed.

ExampleWorkbook.xlsx

[zbor]

It's never good idea to use same numbers in dummy data because now I cannot see what number goes where.
But try this, maybe I've guess.

Press RUN button and check result.

I've removed your merged cells and your data must be in order as in example.

[zbor]

I see now I would change this part of code:

Please Login or Register  to view this content.

• If you want to write both sheet name (now is one if date in sheet is missing) but without data into:

Please Login or Register  to view this content.

• If you want don't want sheet name:

Please Login or Register  to view this content.

[MorphiusX]

Hey mate,

This works a treat, Is there a way to implement that it will auto sort highest to lowest instead of me manually doing it with the build in Sort & Filter function

[zbor]

Sure, try this:

Please Login or Register  to view this content.

[MorphiusX]

I entered all the data and if the power gained is 0 it throws an error I can fix this by just making the powered gained 1 however is there a way to make it not throw the error and stop the script

[zbor]

When you divide with 0 you will have error.

You can try this:

Instead of

Please Login or Register  to view this content.

Use something like:

Please Login or Register  to view this content.

That will return 0 instead of #DIV/0!.
However if you enter something else (like character) you will also get #VALUE! error.

To avoid all error use just:

Please Login or Register  to view this content.

[MorphiusX]

Thank zbor very knowledgeable. That works for the #DIV/0 error however I was refering to when you run the macro it will give an error Run time 1004 on this line

Please Login or Register  to view this content.

[MorphiusX]

I seem to have found the issue, I simply removed the other 2 weeks of rankings

[MorphiusX]

So now that I have reached the end of the first week how would I go about collecting the new data for the following week?

Here is the completed workbook
gow2.xlsm

[MarvinP]

Hey MorphiusX,

I've written a little VBA code to put all your individual players info in a single table. See the first tab in the attached workbook. Then I did a few Pivot Tables on the data that duplicates what you have on each page. See if this helps you display or analyze your data. Playing/learning Pivots may really help with this problem. BTW - I've created a Dynamic Named Range so the Pivot Table will use all the data on the new sheet after you rebuild it. You may need to refresh all Pivots to get them to display correctly after running the new macro again.