Auto sorting league table

Hi,

This forum has so far saved me twice so hoping third time is just as lucky .

I know this has been asked before as I did a search and tried the solution out but couldn't work. I know my way around excel until I get to working with macro's which stumps me.

What I need is to make the league table (MTD columns R - S) auto sort depending on the result of qualifications (agent with highest goes to the top) which is the calculation in column S. This has to work for each weeks tab.

I'm sure it's possible I just can't work out how.

Thanks in advance

Try this with each sheet
In S4

Please Login or Register  to view this content.

In T4

Please Login or Register  to view this content.

Drag/Fill both formulae Down to row 17

Then in the Workbook module

Please Login or Register  to view this content.

Hope this helps

You are amazing, that is pretty much what I'm after, thanks alot.

In order to make the MTD table calculate where they are for the month taking into account previous weeks and the current week's qualifications in weeks 2, 3 and 4 should I simply just do a sum e.g in Week2:

Please Login or Register  to view this content.

Or would there be a simpler way? This does work so I don't mind using this but just wondering if I'll be complicating things further.

Also as the team grows can I just insert rows and amend the code in the module to extend to these extra rows and it'll work or do I need to something extra?

Thanks again.

Try this

I have added cumulative tables to each sheet.

1/. "MTD Week 1" (cumulative) is a direct copy of "Status This Week" and is used to generate the following sheet "Week2"

2/. In W4 "Week2"

Please Login or Register  to view this content.

Drag/Fill down
This could be dynamic, but try it as it stands first.

3/. "Week3" & "Week4" are a "progression" of the above.

Working this way you should get progressive results as the data is entered for each week, and be able to amend late entries or errors and still get the correct results.

I have amended the code to handle any length of table, provided that they are all the same length and have the same agents' names.
There could well be problems if you change names in only some of the tables.

At this stage you will have to watch the formulae if you add rows, but the code should be okay as it stands.

4/. I have "tidied" your conditional formatting, check to see it is okay

Hope this helps.