using a cell's reference to return a value.

[Bhupinder Rayat]

Hi All,

E.g. in cell A1, sheet1, I have =sheet2!D12 to reference whatever is in
sheet 2, cell D12.

I want a formula that looks into A1 to see what is referenced and then
return the cell 3 rows down from it.

so cell A2 - =sheet2!D15
cell A3 - =sheet2!D18
cell A4 - =sheet2!D21.

I know this is possible using INDIRECT but is it possible to build a dynamic
formula to work for any cell, so that the formula looks at what is referenced
in the cell above (e.g. A3 looks into A2, B17 looks into B16) and then
refences the cell 3 rows down from it as shown above.

Any help would be much appreciated.

Thanks,

B/

 

[Max]

One way

First, install this GetFormula UDF from David McRitchie's:
http://www.mvps.org/dmcritchie/excel/formula.htm

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

Then, with A1 containing: =Sheet2!D12
you could place in A2:
=OFFSET(INDIRECT(MID(getformula($A$1),2,99)),ROWS($1:1)*3,)
and copy down to return required results.

 

[Bhupinder Rayat]

Mate that is genius! Thanks so much and happy new year...

 

[Max]

welcome, glad it worked for you.