CODEWITHSUNDEEP
excelvba
Tony Kirkham
Tony Kirkham

Reputation: 9

Excel: get the address referenced in one formula, and use it in another formula

Is there a way to obtain the address of a referenced cell, in Excel, in a way that it can be used in another formula?

I have a cell that references another, contents of B1 =Address!A4. In another cell I want to get the cell reference contained in B1 and use it to obtain the value in a cell relative to the referenced cell. I do not want to search for the value of the cell Address!A4 because the range in which I would be searching may contain multiple matches. I want a reference to the cell.

Is there a way to do this?

For example:

a row of cells contains values A1="123 E 400 S", B1="123", C1="400"
a cell contains a reference to one of those cells: G8=B1

So in the cell G8 the value "123" is displayed. I want to place in cell F8 something that will use the reference in G8 as a "pointer" or index to B1 that I can then offset to get at A1.

I would like to do this F8=OFFSET(CELL("contents",G8),0,-1) which would cause the value "123 E 400 S" to be displayed. Sadly, this does not work, I get a #REF! error.

This may seem like a candidate for INDEX, VLOOKUP, MATCH, etc., but I do not want to try to define the range of cells to look in, it may change. And the potential search string "123" may be duplicated in the range even if I were to define it.

It seems like I should be able to use the reference I already know, go to that cell, and then work with cells relative to that.

Any ideas? Thanks for the help.

Upvotes: 0

Views: 693

Answers (1)

Wolfie
Wolfie

Reputation: 30046

Edit: An easier method

It might be easier to just have the cell address in a cell somewhere, and use INDIRECT from both cells B1 and C1, then you don't need the extra functions described below!

indirect

VBA method: User-defined functions

If you can save your workbook as a macro-enabled .xlsm then you could use a User-defined function to get the formula, and extract the cell address. Add this to a module in your workbook's VBA editor (press Alt+F11, Insert>Module).

Function GetFormula(rng As Range) As String
    GetFormula = rng.Formula
End Function

Example use, note the formula in C1 is =GetFormula, but the value shown is the formula in B1:

get function

Then you can either use the cell formula RIGHT, or do it directly in VBA with another function. The advantage of doing it in VBA is any parsing you do will be clearer, you could go as far as using RegExp. 

Function GetEndRange(rng As Range) As String
    Dim str As String
    str = rng.Formula
    ' Get the characters to the right of an exclamation point
    ' Assumes formula ended in an address, could do more complicated parsing here
    str = Right(str, Len(str) - InStr(str, "!"))
    GetEndRange = str
End Function

Use:

end range

Now you can use a cell formula (or a more complicated macro) to access the offset cell. Here is the cell formula way to do it:

=OFFSET(INDIRECT("Address!" & GetEndRange(B1),TRUE),1,0)
' Evaluated as:
' =OFFSET(INDIRECT("Address!A4",TRUE),1,0)
' =OFFSET(Address!$A$4,1,0)
' =Address!$A$5

Upvotes: 2

Related Questions