Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find address
I have a formula using a 3D range, =max(Oct:Sep!AB124). Is there anyway I can
return the address of the max value, which could be on any one of 12 worksheets? Formulae based on Address+Index don't work with a 3D range. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find address
Hi,
For the example you gave: =IF(Oct!AB24=MAX(Oct!AB24,Sep!AB24),"Oct!AB24","Se p!AB24") Can't use most of Excel's functions across 3D. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "RJW999" wrote: I have a formula using a 3D range, =max(Oct:Sep!AB124). Is there anyway I can return the address of the max value, which could be on any one of 12 worksheets? Formulae based on Address+Index don't work with a 3D range. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find address
Here's an attempt to accomplish your task. It works, with a few assumptions:
There are 12 sheets, Jan-Dec. and based on your formula, you're referring to cell "AB124" on all 12 sheets. Should the sheet Order or Names change, it would be necessary to modify the list and formula. On a Summary sheet, in cells A1:A12, list the other 12 sheet names in the order they appear in the WB. In cell B1 enter the formula =INDIRECT(A1&"!"&"AB124") Copy down to B12. In another cell on the same sheet enter the formula =INDEX($A$1:$B$12,MATCH(MAX(Jan:Dec!$AB$124),$B$1: $B$12,0),1)&"!"&"AB124" This matches the FIRST occurrence of MAX(Jan:Dec!$AB$124) with the values in B1:B12 and returns the corresponding sheet name along with the text "!AB124". This may be a bit crude but it may get you started with a solution. Best wishes. DK "RJW999" wrote in message ... I have a formula using a 3D range, =max(Oct:Sep!AB124). Is there anyway I can return the address of the max value, which could be on any one of 12 worksheets? Formulae based on Address+Index don't work with a 3D range. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
where i can find the address tool bar? | Excel Discussion (Misc queries) | |||
find name and insert address | Excel Worksheet Functions | |||
Find address of active cell | Excel Worksheet Functions | |||
Is there a way to find the address of a cell with a certain value? | Excel Worksheet Functions | |||
how do i find my e-mail address & add one more | Setting up and Configuration of Excel |