Help with Football Pool Spreadsheet

[sullivanjay]

I’m trying to make a spreadsheet to handle a weekly football pool. I’
entry-level experience with Excel. In the rows I have the names of th
players and in the columns week 1, week 2 etc. I used the DMAX functio
in the week 1 column to find the high score. Now I’m trying to find
way to reference the DMAX cell value with the high score and go bac
and get the name or names of the weekly high score from the rows.
don’t know what function if any can do that. If someone can point me i
the right direction I could probably figure it out. Thanks / Jay

Microsoft Excel 2000 (9.0.2720

 

[Max]

Assuming no ties, and you have in Sheet1:
player names in col A, A2 down
"week1", "week2" across in B1, C1, D1, etc

Try say, in another sheet, Sheet2 (say):

Put in B1: =OFFSET(Sheet1!$A$1,MATCH(A1,Sheet1!B:B,0)-1,0)
assuming A1 contains your DMAX value

Alternatively, try anywhere in Sheet2:
: =OFFSET(Sheet1!$A$1,MATCH(LARGE(Sheet1!B:B,1),Sheet1!B:B,0)-1,0)

(above will also return the player name with the highest score in "week1"
col in Sheet1)

 

[sullivanjay]

Thanks Max, Was able to make the second formula work.

 

[Max]

You're welcome, sullivanjay !
Thanks for feedback.

The first one should also work, actually.
Just a bit of clarification..

The line:

assuming A1 contains your DMAX value

means:

assume A1 in Sheet2 contains
the DMAX value from Sheet1 for "week1" col

cheers
Max

 

[sullivanjay]

Hi Max,
Have not had the time to work with the first formula just yet. You did
make it a little clearer in your last post. Right now the spreedsheet
is giving me the weekly winner (high score) and keeping track of the
runing total at the end of the rows for each individual. It's very
workable right now. Next step will be to sort the names for the weekly
standings but I have till next year to figure that out. This is going
to save a lot of busy work as there are over 50 people in this pool.
Thanks Again / J

 

[Max]

Thanks, sullivanjay. Your feedback is appreciated.

Pleased to know that you got the 2nd one working.