Sorting ranks in order automatically

[euronova]

Hi,
I have built a spreadsheet to keep track of 6 key performance indicators for my business, what I am trying to do is to have the sheet auto sort each table (7 of them) by rank so 1 is at the top and so on.

I have 7 tables all laid out as follows:
Store|Target|Actual|Rank with a helper column next to rank that is actual-target*100.

What I want to do is be able to paste the data in to the target and actual box and have the table automatically update to show the top store at the top and then go down in order so the lowest performing store is at the bottom.

There are tables for each of the 6 KPIs then a total table at the end which is just 2 columns, store and rank.
I want this table to display the overall ranks for each store, so maybe each of the 6 ranks added together then ranked again to give the overall ranking, obviously the store numbers would need to change with the ranking so each value was assigned to the correct store.

The layout of my first table is:
A4: Shop title
A6 to A31 is each store number
B5: Target
B6 to B31 is the target for each store
C6: Actual
C7 to C31 is the actual value they achieved
D6: Rank
D7 to D31 is the rank
E6 to E31 is the helper column that is C6-B6*100

I hope someone can help with the formulas to input in the boxes to get this working correctly.

Thanks for taking the time to read my post.

Bob


Edit to add:
I have been trying this some more and I now have the following:
AG6 to AG32 is the list of store numbers on the final ranking table.
AH6 to AH32 is all the ranks for each store added together from the 6 KPI tables.
AI6 to AI32 is the new overall rank for each store.

All I require now is a way to automatically sort the data in AI6 to AI32 but ensure the store codes stay with the correct rank.
So in AG6 is store code 1, their rank is 16. So if this was automatically sorted then the store code 1 would also need to move down the table to be on the correct row for the rank.

I hope that makes some sense!

Any help appreciated, many thanks.

[gko_87]

would kindly upload a sample file?

[euronova]

Of course, thankyou for your reply.
I have created 2 sheets, the master sheet and the desired outcome.

If every table would auto sort it would be great however I am happy to just have the final "overall rankings" table autosorted.

I hope my file works.

Thanks

[Jacc]

Something like this?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[euronova]

Exactly like that, thankyou so much!

[Jacc]

'''''''''''''''''''''

[Jacc]

Could you also marked the thread as solved? It's under Thread tools, up to the right of this page.