Using the return from ADDRESS within another formula?

[S Davis]

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,roomsxp,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.

 

[S Davis]

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,MATCH(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.)

 

[RagDyeR]

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!
=====================================================

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,roomsxp,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.

 

[Niek Otten]

Look in Help for the INDIRECT() function

 

[barry houdini]

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

 

[S Davis]

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(E14,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,roomsxp,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.