Referencing a cell containing a reference formula

[hsandeep]

Thanks to this forum through which I got help & now in my workbook, in Sheet1, in cell address F9, I am able to fill a value present on Sheet2’s cell address M17 using ‘indirect’ function instead of a simple Excel function like F9’=Sheet2!M17’.
In Sheet3, cell H6 I want to do FURTHER REFERENCING such that F the formula needed in H6 should be able to evaluate the cell address to which the formula in Sheet1’s F9 is pointing to (i.e. Sheet2!M17) & then go RIGHT (of Sheet2!M17) by 5 columns & DOWN (of Sheet2!M17) by 4 rows & fill the value of the cell. It would be like instead of directly punching in H6’=Sheet2!R21’, the formula in cell H6 should do this.
Please note: the source cell contains a value which is either numerical or alphabet/(s) or alphanumerical or ‘blank’. The formula should fill the values in case-sensitive form if the sourced value is alphabet/(s) or alphanumerical. For a ‘blank’, the output cell should show a ‘blank’ & not ‘0’ (zero).
What formula should be filled in H6?
I am using Excel 2003 & it runs under Excel 2007 in “compatibility mode”.
Thanks in advance.
hsandeep

 

[Andrew Poulsom]

Based on your other post the formula would be:

=OFFSET(INDIRECT("'"&$B$3&"'!"&F$4&$C9),4,5)

Note that the OFFSET function is also volatile.

 

[hsandeep]

Thanks Andrew for your reply,
Sheet1:
B3=Sheet2, F4=M, C9=17, F9=Old Formula of previous post (F9 shows = 98.129)
Sheet2:
M17=98.129, R21=1ABcd
Sheet3:
H6=NEW FORMULA?
Result: H6=1ABcd
You may ask me any of your queries, Andrew.
hsandeep

 

[Andrew Poulsom]

It would be:

=OFFSET(INDIRECT("'"&Sheet1!$B$3&"'!"&Sheet1!F$4&Sheet1!$C9),4,5)

 

[hsandeep]

Andrew,
Can the formula be made such that F it looks at the cell contents of Sheet1’s F9 ONLY to arrive at that the conclusion that the source cell is Sheet2’s M17 and use 4 & 5 for going columns Right & rows Down to arrive at the final source cell address as Sheet2’s R21?
The formula is to be filled in Sheet3’s H6.
Your latest formula is looking at 3 cell contents in Sheet1 (B3, F4 & C9) and uses 4 & 5 to draw conclusion that the final source cell is Sheet2’s R21.
Please re-build the formula, Andrew.
hsandeep

 

[Andrew Poulsom]

Andrew,
Can the formula be made such that F it looks at the cell contents of Sheet1’s F9 ONLY to arrive at that the conclusion that the source cell is Sheet2’s M17

Sorry, no.

 

[hsandeep]

Andrew,
OK, I'll accept your verdict if that is so. But I hope you have understood my requirement. I thought we can do a sort of 'double referencing' a cell in Excel.
Comments please.
hsandeep

 

[Andrew Poulsom]

In what way do you think that's possible?

 

[hsandeep]

Andrew,
My workbook has lot of worksheets, source cells are in Sheet2 & destination cells are in Sheet3. For referencing a cell’s value, I had been struggling using the most simple function like ‘=Sheet2!R21’ in Sheet3’s H6 & many more cells in Sheet3. For this, like a novice, I fill ‘=Sheet2!R21’ in Sheet3’s H6. Similar formulas are filled in multiple cells of Sheet3 & this count is nearly 60-70 numbers approximately, every day, which kills my concentration & makes my mind numb. Even the chance of human error is high.
With your help (given in my earlier post), through a formula in Sheet1’s F9, instead of using ‘=Sheet2!M17’, I am able to fill the value of Sheet2’s M17 (by referencing B3, F4 & C9 in Sheet1). Currently, in Sheet3, I locate cells & fill formulas in it by taking guidance using headers (headings like ‘SHEET2_R’, ‘SHEET2_S’, etc……………. ).
Can a formula of a cell (cell#1) be diagnosed by a 2<SUP>nd</SUP> formula in another cell (cell#3) so that it is diagnosed that the cell#1 is referring to a 3<SUP>rd</SUP> cell (cell#2) by <sheetname>!<cell_address>? Using this diagnosis, can we refer to the intermediate cell#2 from cell#3 by indirectly referring to cell#1 & then catch the final cell#4. I don’t know whether we can do this in Excel or not even if the formula remains long or a kind of UDF but without VBA.
cell#1 = Sheet1’s F9 (you had build a formula for this cell)
cell#2 = Sheet2’s M17 (1<SUP>st</SUP> source cell, intermediate cell)
cell#3 = Sheet3’s H6 (formula NEEDED in this cell)
cell#4 = Sheet2’s R21 (Final source cell, 5 columns right & 4 rows down from 1<SUP>st</SUP> source cell)
hsandeep

 

[Andrew Poulsom]

Can a formula of a cell (cell#1) be diagnosed by a 2<SUP>nd</SUP> formula in another cell (cell#3) so that it is diagnosed that the cell#1 is referring to a 3<SUP>rd</SUP> cell (cell#2) by <SHEETNAME>!<CELL_ADDRESS>?

Sorry, no.