Ranking of Positions and Hiding of Unused Rows in Calculated League Table

Hi,

I'm not sure exactly what you want in AE, as there's no data there at the moment and I don't know what 'Alpha' means to you - sorry .

However, here's how to get the rankings working.

First, in AC3 and AD3, use these formulae, which will blank the cells if there's no team listed in C3:

Formula:

Please Login or Register  to view this content.

They're just simple IF statements to check for a blank in C3, with your formulae on the end - drag down to row 30 and you'll see rows 13-30 go blank. If you don't do this, you'll get some errors showing later - though they won't actually affect the results.
You can do the same in columns D to AA - just add IF(C3="","", to the start of each formula and a close bracket ) at the end. Again, this is just aesthetic - the results won't be affected if you don't.

Now the actual ranking - put this in AB3 and drag down:

Formula:

Please Login or Register  to view this content.

It first checks if there's something in C3 (team name). If not, it returns a blank. If there is something in C3, then it does the ranking.
Instead of specifying K3 to K30, the range for the ranking is set by starting at K$3 then offsetting by the number of rows from 3-30 which are greater than 0 - that is, it counts the rows containing something, then adds that to row 3.

Now put this formula in AF3 and drag down:

Formula:

Please Login or Register  to view this content.

Because AB/AC/AD can be blank, this checks if you get an error from the calculation and gives you a blank instead of a #VALUE! error.

Lastly, put this in B3 and drag down:

Formula:

Please Login or Register  to view this content.

The Rank function here is set up the same as that in AB3, plus there's a check if an error is returned, as in AF3.

I hope that all makes sense - here's your file with the formulae applied: 2015-16 _ ranking for essxmark _ AS.xlsx

There isn't a formula-based way (that I know of) to automatically hide unused rows, but it can be done with VBA.
(VBA = Visual Basic for Applications - the programming language built-in to MS Office programs. Macros are procedures which are run using VBA.)

Right-click on 'Main Table' and select 'View Code'. This will open up the VBE (Visual Basic Editor). You'll see a list of your sheets on the left-hand side, with 'Sheet3 (Main Table)' highlighted with a very pale blue background. On the right-hand side is a blank area where the programming is typed. Copy the code below and paste it there. The lines starting with an apostrophe are comments which actually don't do anything, but help explain what's going on - you can delete those if you want, but they don't slow things down, so I recommend leaving them there as reminders.

Please Login or Register  to view this content.

This code will run whenever this worksheet is re-calculated - which will happen whenever you change the selection in the drop-down box on the 'Select League' sheet. (By the way, 'tab' is just a commonly used word for 'worksheet' or 'sheet' - officially they're sheets, but everyone understands what's meant by 'tab'.)
If you need to amend the sheet manually, enter 'X' (without the inverted commas) into cell A2 and the code won't run. Without this, every time you try to change it at all, the code will run, which can get annoying .

A word of warning - for macros to run, the file has to be saved as a macro-enabled workbook (.xlsm). Because it's possible for malicious code to be run from them, macros are normally disabled, with a warning to users, who can then choose whether to allow macros to run or not. (See here: https://support.office.com/en-us/art...f-e8621e8fe373)
If the macros are disabled, your formulae will all still work, but the 'unused rows' macro won't run.

Bonus code :
I don't know if you would find this useful, but if you want the file to always open on the 'Select League' sheet, then do this:
In the VBE (which you can also open by pressing Alt-F11), right-click 'This Workbook' and select 'View Code'. Copy the code below and paste it in on the right-hand side:

Please Login or Register  to view this content.

Here's your file again, with the code added: 2015-16 _ ranking & hiding rows for essxmark _ AS.xlsm
The bonus code to always open on the first sheet is there but not activated - if you want to use that, you can just remove the apostrophes from the beginning of each line in that code, so that it looks like the code above.

I hope that does what you want.

You're welcome and thanks for the rep.