Return the Title of a column if the column is the highest column with data

I wonder if anyone can help me. I have a table of scores with Weeks in columns and Names in rows as follows:


A B C D
Week 1 Week 2 Week 3 Week 4.................Week 33

1 Player 1

2 Player 2

3 Palyer 3

down to player 16

In each column I want to enter the scores for each player each week.

On a separate sheet I would like to write a summary of the scores and at the top I would like it to include the most recent week played, so I need a formula that will return the highest column heading that contains scores (not all the players will have scores entered each week).

So for example if the Sheet is as follows:

A B C D
Week 1 Week 2 Week 3 Week 4.................Week 33

1 Player 1 650 622

2 Player 2 642

3 Palyer 3 678

Then the formular would return "Week 2"

If it was like this

A B C D
Week 1 Week 2 Week 3 Week 4.................Week 33

1 Player 1 650 622 690

2 Player 2 642

3 Palyer 3 678

It would return "Week 3"

PLEASE HELP :-)

Originally Posted by XOR LX

Hi,

Assuming your Week names and Player names are in B1:J1 and A2:A10 respectively, try this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER):

=INDEX($B$1:$J$1,MAX(IF($B$2:$J$10<>"",COLUMN($B$2:$J$10)-MIN(COLUMN($B$2:$J$10))+1)))

Regards

Thanks for this, I have tried this formula, but it always returns the 2nd to last week regardless of what scores are entered!

Originally Posted by XOR LX

Did you make sure you entered it as an array formula as per my instructions?

Regards

Hi Again,

Yes I did, and this is the formula i used cut and pasted directly (confirmed with Ctrl, Shift and Enter)

=INDEX('Score Sheet'!$B$3:$BO$19,MAX(IF('Score Sheet'!$B$4:$BO$19<>"",COLUMN('Score Sheet'!$B$4:$BO$19)-MIN(COLUMN('Score Sheet'!$B$4:$BO$19))+1)))

Originally Posted by XOR LX

"Assuming your Week names and Player names are in B1:J1 and A2:A10 respectively".

If this assumption was not correct, you need to amend the ranges and formulas accordingly. It appears that you have done so with the ranges, but it also appears that your Player Names are contained within a single row, not column (A2:A10) as was given in my assumption. This changes things.

Please post an attachment and I will correct for you.

Regards

I have attached the file, as you can see Sheet 1 includes all the scores, and the points are worked out automatically. Sheet 2 contains a summary all I want is the the highlighted box to contain which week have the latest scores been entered. Junior League Bronze Division.xls

That is fantastic thank you so much for your help