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

1. ## 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"

2. ## Re: Return the Title of a column if the column is the highest column with data

Something like:

=INDEX('Sheet1'!\$A\$1:\$Z\$1,MATCH(9.99999E+307,'Sheet1'!\$A2:\$Z2)) copied down

where the rows in active sheet correspond directly to rows in main sheet.

3. ## Re: Return the Title of a column if the column is the highest column with data

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

4. ## Re: Return the Title of a column if the column is the highest column with data

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!

5. ## Re: Return the Title of a column if the column is the highest column with data

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

Regards

6. ## Re: Return the Title of a column if the column is the highest column with data

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)))

7. ## Re: Return the Title of a column if the column is the highest column with data

"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

8. ## Re: Return the Title of a column if the column is the highest column with data

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

9. ## Re: Return the Title of a column if the column is the highest column with data

Ok,

I've had to make a slight adjustment as well since the actual final column that will contain a value will always be a Pts column (since you have formulas calculating in there).

Try this formula (again, array-entered) and let me know how you get on:

=INDEX('Score Sheet'!\$B\$3:\$BO\$3,,MAX(IF('Score Sheet'!\$B\$4:\$BO\$19>0,COLUMN('Score Sheet'!\$B\$4:\$BO\$19)-MIN(COLUMN('Score Sheet'!\$B\$4:\$BO\$19)))))

Regards

10. ## Re: Return the Title of a column if the column is the highest column with data

That is fantastic thank you so much for your help

11. ## Re: Return the Title of a column if the column is the highest column with data

You're welcome.

12. ## Re: Return the Title of a column if the column is the highest column with data

Yes, you're welcome!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1