Get value by referencing a cell

[hsandeep]

In E2 of Worksheet “Sheet2” I am using a formula =’7’!H120 which gives me value of cell H120 of the Worksheet with tab named ‘7” as a result in cell E2.

This formula in E2 is variable…it can be =’7’!B100; or =’7’!C555 or any formula like this.
I require an Excel formula in cell E3 of Worksheet “Sheet2” which should give me value of the cell which is $G$2 row below or above (IF G2=1, then 1 row below, IF $G$2=-1, then 1 row above) of the cell address which is being used in cell E2.
G2= an integer value

How to accomplish?
Thanks in advance.



Cross posted in Get value by referencing a cell

Referencing.xlsx
	E	F	G
2	22		1
3	999
Sheet2

Cell Formulas
Range	Formula
E2	E2	='7'!H120

 

[James006]

Hi,
Not sure to fully understand ...
May be the first step is

=SUBSTITUTE(FORMULATEXT(E2),"='7'!","")

 

[Peter_SSs]

@James006
OP's profile says Excel 2010 so they would not have access to the FORMULATEXT function.

 

[James006]

Solution apparently found in another Forum:

Get value by referencing a cell

Input cell: A1=H140 Output cell: D1=A140 Formula needed for D1 How to accomplish? Thanks in advance

www.excelforum.com

 

[hsandeep]

Solution apparently found in another Forum:

Get value by referencing a cell

Input cell: A1=H140 Output cell: D1=A140 Formula needed for D1 How to accomplish? Thanks in advance

www.excelforum.com

This is solved using vba provided by Peter SS Sir, I don't want vba, I want Excel formula in E3

 

[Peter_SSs]

This is solved using vba provided by Peter SS

As a matter of interest, where did I do that?

In the other forum you wrote:

the Excel formula required should be able to read the formula of cell E2 ...

If you are using Excel 2010 as your profile says, a normal worksheet formula cannot read the formula in another cell. Such a function only became available in Excel 2013.

 

[James006]

With Excel 2010, you should create a Named Range, say, reference and define it with Refers To : =GET.CELL(6,Sheet2!$E$2)

Then wherever you need your result, you can use

=INDIRECT("'7'!"&SUBSTITUTE(SUBSTITUTE(reference,0,1),"='7'!",""))

 

[Peter_SSs]

With Excel 2010, you should create a Named Range, say, reference and define it with Refers To : =GET.CELL(6,Sheet2!$E$2)

Don't know if it will be an issue for the OP, but that method is like a pseudo-macro and the workbook will need to be saved as a macro-enabled workbook.

 

[James006]

Don't know if it will be an issue for the OP, but that method is like a pseudo-macro and the workbook will need to be saved as a macro-enabled workbook.

Unless OP saves his workbook using: Excel 97 - 2003 Workbook (*.xls)

 

[hsandeep]

@hsandeep
As a long time member and user of the forum, you should be familiar with our forum's cross-post policy too: #13 of the Forum Rules. In future please always provide links to threads in any other forum(s) where you have posted the same question.

I did provide a link in the 1st thread itself...here it is once again..... Cross posted in Get value by referencing a cell