adding max values

[tbasic]

I have a spreadsheet with 27 worksheets in it. Each worksheet contains a list of names in column A and an amount in column F. I am looking to find a function which will check all the worksheets and tell me how many times in each spreadsheet a particular name was the highest value. These worksheets are set up as weeks, so some values in the spreadsheets will be zero until they are filled in week by week. I would like the zeros to be ignored in the totals calculation

Example

Worksheet 1 Worksheet 2 Worksheet 3 Worksheet 4 Desired Total Shee
John 10 John 5 John 15 John 0 John
Pete 5 Pete 2 Pete 7 Pete 0 Pete 0
Bill 2 Bill 10 Bill 10 Bill 0 Bill

Thanks in advance
To

 

[jeff]

Hi,

I think in order to set up functions to do this,
rather than a macro, you'll need to set up some
helper cells on your Totals sheet (which you
can hide) where you set up ranges of 2 columns
for each of the sheets with the 1st scores, then names.
You can then find the Max for each range of numbers,
and beside that cell, do a VLOOKUP for the matching name.

For example, on your totals sheet, (VS1="value from
sheet 1", NS1= "corresponding name from sheet1", etc)

ColA Col B Col C Col D etc...
VS1 NS 1 VS2 NS2 etc.... (Assume row 1)
VS1 NS 1 VS2 NS2 etc....
VS1 NS 1 VS2 NS2 etc....
.... (row 10)

XXXX <== here put =Max(A1:A10) (assume row 12)
XXXX <== here put =VLookup(A12,"A1:A10",2,false)

Put the same things in Col C & D, etc.

If I"m not being clear, send me your email and I'll
send you a sample spreadsheet.

jeff
[email protected] (remove NOSPAM)

-----Original Message-----
I have a spreadsheet with 27 worksheets in it. Each

worksheet contains a list of names in column A and an
amount in column F. I am looking to find a function which
will check all the worksheets and tell me how many times
in each spreadsheet a particular name was the highest
value. These worksheets are set up as weeks, so some
values in the spreadsheets will be zero until they are
filled in week by week. I would like the zeros to be
ignored in the totals calculation.

Example:

Worksheet 1 Worksheet 2 Worksheet 3

Worksheet 4 Desired Total Sheet

John 10 John 5 John

15 John 0 John 2

Pete 5 Pete 2 Pete

7 Pete 0 Pete 0

Bill 2 Bill 10

Bill 10 Bill 0
Bill 1