Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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/ |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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/ |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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/ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CHANGE ONE CELL'S VALUE AND GET THE SAME RETURN - SOME HELP PLEASE | Excel Discussion (Misc queries) | |||
Return cell reference instead of value | Excel Discussion (Misc queries) | |||
Return value in cell above the reference | Excel Discussion (Misc queries) | |||
Changing named range reference depending on a cell's content | Excel Discussion (Misc queries) | |||
check if reference exists, then return its value or return 0 | Excel Worksheet Functions |