CODEWITHSUNDEEP
excel
itzy
itzy

Reputation: 11755

Find cell in position relative to address in other cell

Is there a function I can put in cell A2 that will return whatever is in the cell below whatever cell is written in A1?

So for example, if A1 contains =B35 then A2 will return the value contained in cell B36 and if I change A1 to =C17 then A2 will now contain whatever is in C18?

I've been trying to use combinations of indirect(), offset() and address(), but can't figure it out.

Upvotes: 1

Views: 18549

Answers (3)

Rodro
Rodro

Reputation: 1

I think I might have solved this one. In Excel 2013, My problem is similar to yours:

E107 contains =A100, I want E108 to return the value contained in cell B100, so if I change E107 to =A101 then E108 will now contain whatever is in B101.

My solution:

=INDIRECT(CHAR(CODE(RIGHT(LEFT(FORMULATEXT(E107),2),1))+1)&RIGHT(FORMULATEXT(E107),LEN(FORMULATEXT(E107))-2))

I'm getting the text of the formula in the cell with FORMULATEXT and applying ASCII operations with CODE and CHAR to add up to A, so that it becomes B, and then returning the value with INDIRECT. Some string operations needed, with LEFT and RIGHT functions.

Upvotes: 0

NickSlash
NickSlash

Reputation: 5077

Assuming your not wanting to do anything more complex than offsetting a single cell reference you could mix in a little VBA.

In a new module:

Public Function tFormula(ByVal Target As Range) As String
    tFormula = Mid(Target.FORMULA, 2)
End Function

B1 = "=C1"

C2 = "Hello"

A1 = "=Offset(tFormula(B1),1,0)"

Offset(tFormula(B1),1,0) = "Hello"

Upvotes: 1

Doug Glancy
Doug Glancy

Reputation: 27478

I don't think it can be done quite like you want. The closest I can come is putting the "target" address in a cell, e.g., in this picture A2 just contains the string "b3". You can then point indirect functions at that. So the formula in D2, for the value of the cell below B3, is:

=OFFSET(INDIRECT(A2),1,0)

enter image description here

Upvotes: 3

Related Questions