+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    08-20-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2002
    Posts
    8

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

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    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.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,706

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  4. #4
    Registered User
    Join Date
    08-20-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2002
    Posts
    8

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

    Quote Originally Posted by XOR LX View Post
    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. #5
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,706

    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. #6
    Registered User
    Join Date
    08-20-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2002
    Posts
    8

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

    Quote Originally Posted by XOR LX View Post
    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. #7
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,706

    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. #8
    Registered User
    Join Date
    08-20-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2002
    Posts
    8

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

    Quote Originally Posted by XOR LX View Post
    "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. #9
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,706

    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. #10
    Registered User
    Join Date
    08-20-2013
    Location
    Barnsley
    MS-Off Ver
    Excel 2002
    Posts
    8

    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. #11
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,706

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

    You're welcome.

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

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

    Yes, you're welcome!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Find highest value in unsorted column and return data of other cell
    By vergrootglas in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-07-2012, 11:56 AM
  2. Return highest value, except from one cell i same column
    By Hjahren in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-26-2010, 09:54 AM
  3. Return column title if value present
    By ChrisMattock in forum Excel General
    Replies: 13
    Last Post: 08-14-2009, 11:48 AM
  4. Return Title to matched column
    By matt@inntact.com in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 02-21-2006, 02:10 AM
  5. [SOLVED] choose the highest value in a row and return column number
    By jlburke4 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-15-2005, 02:50 AM

Tags for this Thread

Bookmarks

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