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

 

[Peter_SSs]

Use this function instead of the one in that other thread.

Function FormulaPt(r As Range, lPart As Long, RowOffset As Long) As String
  Dim s As String
  
  s = r.Formula
  Select Case lPart
    Case 1: FormulaPt = Mid(s, 3, 1)
    Case 2: FormulaPt = Mid(s, 6, 1)
    Case 3: FormulaPt = Right(s, 3) + RowOffset
  End Select
End Function

hsandeep.xlsm
	E	F	G	H	I	J	K	L	M	N
1			-2
2	121							7	H	118
3	115
Sheet2

Cell Formulas
Range	Formula
L2:N2	L2	=FormulaPt($E2,COLUMNS($L:L),$G1)
E2	E2	='7'!H120
E3	E3	=myOffset(E2,G1)

.. or keep using that previous function and just adjust for the row offset when retrieving the third formula part

hsandeep.xlsm
	E	F	G	H	I	J	K					P	Q	R
1			-2
2	121											7	H	118
3	115
Sheet2

Cell Formulas
Range	Formula
P2	P2	=FormulaPart(E2,1)
Q2	Q2	=FormulaPart(E2,2)
R2	R2	=FormulaPart(E2,3)+G1
E2	E2	='7'!H120
E3	E3	=myOffset(E2,G1)

 

[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)