finding sheet name for max value

finding sheet name for max value

Post by Charlie Ha » Wed, 09 Jul 2003 07:12:06



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

Post by Harlan Grov » Thu, 10 Jul 2003 01:47:53


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.

 
 
 

1. Excel - finding max value

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"?

Please respond via e-mail.  Thanks.

--
Jason Edgington                         Engineering Analyst

PH:(313)594-4069  FAX:(313)594-1193       Dearborn, MI, USA
"Opinions expressed here are my own and not those of Ford Motor Co."

2. Any news of Notes/MVS?

3. How to find MAX value corresponding with time.

4. Trashed HPFS Drive.....Anyone ever heard of this scenario?

5. How to display cell Location of the max [and not the value of max]

6. Scheduling to maximize inter-completion time

7. Find value in Active Cell in another sheet(s) & delete

8. Audio-CD --> Speaker-Box ?

9. Find the cell value from other sheet

10. find sheet name

11. Max Value Min Value

12. Distributing cell values from one Excel sheet to another Excel sheet on another computer - HOW TO

13. *Can I use values from sheet 1 in formula on Sheet 2?