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

 

[hsandeep]

Do you mean this 2 and (1)?
myOffset = Sheets(Replace(Split(Mid(r.Formula, 2), "!")(0), "'", "")).Range(Split(r.Formula, "!")(1)).Offset(RowOffset).Value

2 says to start looking at the formula from the 2nd character. That is, start looking after the "=" sign

(1) Says that after splitting the formula at the "!" sign, look at the 2nd part - The first part is index (0) and the second part is index (1)


G1 is the second argument in the formula =myOffset(E2,G1) so it becomes the second argument in the function Function myOffset(r As Range, RowOffset As Long) As Variant
So in the function it is RowOffset and is used here
myOffset = Sheets(Replace(Split(Mid(r.Formula, 2), "!")(0), "'", "")).Range(Split(r.Formula, "!")(1)).Offset(RowOffset).Value


The worksheet formula was ='7'!H120
The function looks at that formula from character 2 (as explained above) and splits it into 2 pieces at the "!" as explained above and removes the single quotes from the first part. So the function obtains
7 and H120

The function then goes to the sheet given by the first part and then to the cell given by the second part then moves up or down (negative number moves up, positive number moves down) by RowOffset which is the value from G1 as explained above and returns the value from that cell

Absolutely clear. Many thanks Sir

 

[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

 

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