## finding sheet name for max value

### finding sheet name for max value

I have a book with 12 sheets - one for each month. There are columns
for different catagories for that month. For each month after Jan I
show the highest monthly value; e.g.,  =MAX(Jan!A21,Feb!A21)
What I would like to do it display the sheet (month) name of this
highest value, but can't find a function that does this. Is it
possible?  thanks for any help...charlie

### finding sheet name for max value

Quote:"Charlie Hart" wrote...
>I have a book with 12 sheets - one for each month. There are columns
>for different catagories for that month. For each month after Jan I
>show the highest monthly value; e.g.,  =MAX(Jan!A21,Feb!A21)
>What I would like to do it display the sheet (month) name of this
>highest value, but can't find a function that does this. Is it
>possible?  thanks for any help...charlie

If you define a name referring to an array of the worksheet names, e.g., WSNL
referring to ={"Jan";"Feb";"Mar";"Apr";...}, then you could use an array formula
like

=INDEX(WSNL,MATCH(MAX(N(INDIRECT("'"&WSNL&"'!A1"))),
N(INDIRECT("'"&WSNL&"'!A1")),0))

If you don't want to use an array of constant worksheet names, you'll need to
use VBA to write a user-defined function.

--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something else.

Is it possible to return the cell reference that contains the max value of a given range?  Not the value itself, but the CELL REFERENCE that contains the maximum value of a given range.

For example, let's say that the maximum value for a range is located in cell \$C\$4.  Is there a function that will return "\$C\$4"?