Using the return from ADDRESS within another formula?
 

Hey all,

Wondering if I can get some help with this. Is there any way I can use
the ADDRESS function within another formula?

I have a formula right now which essentially tells me the start of a
range I want to start an offset from So, this formula, using ADDRESS,
currently returns "$B$33." Here's the formula in case you are
interested:

=ADDRESS(MATCH(F14,commandsxp,0)+2,MATCH(E14,rooms xp,0)+1)

(using two named ranges commandsxp and roomsxp)

The locatoin returned from ADDRESS will vary and tell the start
position of an array based on two string inputs.

Anyway, what I want to do is use this returned value of $B$33 in an
offset formula so I can start a new search from this location based on
other criteria. So basically:

=OFFSET(X,1,1), where X is the formula above denoting the reference
cell to begin offsetting from!

Any ideas how to get this "$B$33" used in this manner? Thanks! Only
idea I've had so far is CELL("contents",Y) (Y being the B33) but this
didn't work as it is coming back as text still.

Using the return from ADDRESS within another formula?
 

Alright, found something useful on the groups awhile back that I
forgot about. I am using this custom function (sorry, no credit since
I can't recall who it was!)

****
Function IDR(s As String) As Variant
On Error Resume Next


Set IDR = Application.Range(s)
If Err.Number = 0 Then Exit Function


Err.Clear
IDR = Evaluate(s)
If Err.Number = 0 Then Exit Function


Err.Clear
IDR = CVErr(xlErrRef)
End Function
****

So now, with address returning $B$33, I can use that return in my
offset like so:

=OFFSET(IDR(ADDRESS(MATCH(F14,commandsxp,0)+2,MATC H(E14,roomsxp,0)+1)),
1,1)

Thank you whoever came up with IDR :) (IDR, IIRC, means "Indirect Done
Right" - a method of using named ranges through the INDIRECT function
while inside a vlookup.)

Using the return from ADDRESS within another formula?
 

Wrap it with Indirect():

=OFFSET(Indirect(ADDRESS(MATCH(F14,commandsxp,0)+2 ,MATCH(E14,roomsxp,0)+1)),1,1)

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"S Davis" wrote in message
...
Hey all,

Wondering if I can get some help with this. Is there any way I can use
the ADDRESS function within another formula?

I have a formula right now which essentially tells me the start of a
range I want to start an offset from So, this formula, using ADDRESS,
currently returns "$B$33." Here's the formula in case you are
interested:

=ADDRESS(MATCH(F14,commandsxp,0)+2,MATCH(E14,rooms xp,0)+1)

(using two named ranges commandsxp and roomsxp)

The locatoin returned from ADDRESS will vary and tell the start
position of an array based on two string inputs.

Anyway, what I want to do is use this returned value of $B$33 in an
offset formula so I can start a new search from this location based on
other criteria. So basically:

=OFFSET(X,1,1), where X is the formula above denoting the reference
cell to begin offsetting from!

Any ideas how to get this "$B$33" used in this manner? Thanks! Only
idea I've had so far is CELL("contents",Y) (Y being the B33) but this
didn't work as it is coming back as text still.

Using the return from ADDRESS within another formula?
 

Look in Help for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"S Davis" wrote in message
...
Hey all,

Wondering if I can get some help with this. Is there any way I can use
the ADDRESS function within another formula?

I have a formula right now which essentially tells me the start of a
range I want to start an offset from So, this formula, using ADDRESS,
currently returns "$B$33." Here's the formula in case you are
interested:

=ADDRESS(MATCH(F14,commandsxp,0)+2,MATCH(E14,rooms xp,0)+1)

(using two named ranges commandsxp and roomsxp)

The locatoin returned from ADDRESS will vary and tell the start
position of an array based on two string inputs.

Anyway, what I want to do is use this returned value of $B$33 in an
offset formula so I can start a new search from this location based on
other criteria. So basically:

=OFFSET(X,1,1), where X is the formula above denoting the reference
cell to begin offsetting from!

Any ideas how to get this "$B$33" used in this manner? Thanks! Only
idea I've had so far is CELL("contents",Y) (Y being the B33) but this
didn't work as it is coming back as text still.

Using the return from ADDRESS within another formula?
 

You can avoid ADDRESS, OFFSET and INDIRECT by using INDEX with your
original match functions, e.g.

=INDEX(1:65536,MATCH(F14,commandsxp,0)+3,MATCH(E14 ,roomsxp,0)+2)

On it's own this will return the contents of the cell (C34) in your
example, or it can be used within other functions to return the cell
reference

Using the return from ADDRESS within another formula?
 

Thanks everyone for the responses. I may have been a bit unclear:

What I was trying to achieve by using the ADDRESS command was the
return of "$B$33". This literal string I wanted to then use in a
formula. Basically what I wanted to achieve was this:

=offset($B$33,1,1)

As you can see in the formula above, $B$33 is the reference cell
(where the offset starts from). I was attempting to use the ADDRESS
function to dynamically change the reference starter cell so that,
based on other criteria, the location of the start of the offset could
shift around the worksheet. So, $B$33 could be anything really based
on other criteria. But we'll use $B$33 for discussion.

Essentially, this then becomes:

=offset(ADDRESS(MATCH(F14,commandsxp,0)+2,MATCH(E1 4,roomsxp,0)+1),1,1)
or (just to make it easy to read)
=offset(address(33,2),1,1)

[for simplicity, address(33,2) is equivalent to the dynamic shifting
one]

I use the simple example just to illustrate the point. Try =offset
(address(33,2),1,1) in Excel.

It does not work. This is because ADRESS returns a text string which
can not literally be used within a formula as a reference, or at
least, I have not yet found a way other than through VBA. The same way
you could not expect =offset("$B$33",1,1) to work.

As for indirect, combining indirect and address is useful in a lot of
situations but not here. Simply because, indirect flows through what
is returned by address. If $B$33 contained the word "dog" for
instance, my offset formula would end up being:

=offset(dog,1,1)

Much the same way, if A1 were to contain:

=ADDRESS(MATCH(F14,commandsxp,0)+2,MATCH(E14,rooms xp,0)+1)

.... and return "$B$33", =offset(A1,1,1) would not work as it starts
the offset from A1, not the contents of A1.

Hope that clears that up.
Thanks for the suggestions with index. Im mucking around with it
because Im plainly working with a terrible data setup, trying to
basically do a lookup from a lookup from a lookup. Augh.