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. |
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. |
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. |
All times are GMT +1. The time now is 06:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com