Alternative to ADDRESS function
The Address() function returns a *text* reference.
Wrap it in the Indirect() function to make it a viable cell reference.
Say H2 contains 50.
=2*Address(2,8)
returns a #Value! error.
=2*Indirect(Address(2,8))
returns 100.
--
HTH,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"DoubleZ" wrote in message
...
In Excel 2007, I am using the following formula to search for and return
all
cells in the array that meet the criteria (as opposed to vlookup, which
only
returns one ):
=IFERROR(INDEX($A$5:$A$100,SMALL(IF($B$6:$B$101=$D $3,ROW($B$1:$B$96)),ROW(1:1))),"")
Then, using the ADDRESS and MATCH functions, I can return the address of
all
the non ERROR values output from the formula above.
I need to reference a certain array of cells based upon the address of the
non ERROR values. For instance, if the first non ERROR cell is A5, then I
need to reference cells A5:Z7. If the next non ERROR cell is A10, then I
need to reference cells A10:Z12.
I need to reference those arrays because I need to use the MATCH and INDEX
functions, which require an array. If I try to put
=INDEX(ADDRESS(...):ADDRESS(...)...) that doesn't work.
I'm sure there are many alternate routes to get where I need to go, but I
can't seem to figure them out. Thanks for you help.
DoubleZ
|