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]

No problem.

 

[hsandeep]

In that case try this user-defined function.

Function myOffset(r As Range, RowOffset As Long) As Variant
  myOffset = Sheets(Replace(Split(Mid(r.Formula, 2), "!")(0), "'", "")).Range(Split(r.Formula, "!")(1)).Offset(RowOffset).Value
End Function

Peter S S Sir,
Can your vba code be further extended to return
The values in L3, M3 & N3 (in the same row)

Note: E2=’7’!H120
Therefore E3 gets the value from the cell address =’7’!H118
Thus, required is: L3=7
M3=H
N3=118
Regards

Referencing.xlsx
	H
117	466
118	115
119	913
120	121
121	296
122	812
123	578
7

Referencing.xlsx
	E	F	G	H	I	J	K	L	M	N
1			-2
2	121
3	myOffset(E2,G1)							7	H	118
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)

 

[hsandeep]

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

This is okay. But I also require formulas in L3, M3 & N3.
In above case, for E2='7'!H120,
E3 is '7'!H118 & therefore required
L3=7
M3=H
N3=118

How to accomplish?

 

[Peter_SSs]

Use $G$1 wherever I had G1 or $G1

 

[hsandeep]

Use $G$1 wherever I had G1 or $G1

I had done the above changes, but correct L3, M3, N3 are not getting populated for the respective cell address E3 which has =myOffset(E3,$G$1)

 

[Peter_SSs]

Then you'll have to show me a mini sheet with some relevant sample data and the expected results as I am lost.

 

[hsandeep]

I am lost.

Sir, please leave it. I am well satisfied with the 2 user defined functions vba provided by you in this post. Many thanks for your efforts & zeal shown

 

[Peter_SSs]

OK. Good luck!