using a cell's reference to return a value.
 

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/

using a cell's reference to return a value.
 

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bhupinder Rayat" wrote:
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/

using a cell's reference to return a value.
 

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

"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bhupinder Rayat" wrote:
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/

using a cell's reference to return a value.
 

welcome, glad it worked for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Bhupinder Rayat" wrote in
message ...
Mate that is genius! Thanks so much and happy new year...