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]

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

Yes, but that is a macro-enabled workbook.
I wasn't sure why the OP is avoiding macros, so I just raised the issue that the old GET.CELLs really are a form of macro.

I did provide a link in the 1st thread itself.

You did indeed - I just missed it and I apologise. I will remove my earlier comment.

 

[hsandeep]

I apologise.

It is shame on me if you apologise for my comeents. Sir I really have a great respect for you since you had helped me way back (I don't remember the exact date) on this forum with a vba which removes formula from a cell & fills it back.....

 

[Peter_SSs]

It is shame on me if you apologise for my comeents.

No shame on you and I apologised for my poor checking, not your comments. Anyway, all good now.

 

[hsandeep]

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

Here is the link found Break 1 formula in 3 cells
This solution was provided by you Sir (in thread#6)

 

[Peter_SSs]

Here is the link found Break 1 formula in 3 cells
This solution was provided by you Sir (in thread#6)

Thanks for the link. I had forgotten about that one. I help in a lot of threads so it is pretty easy to forget just which ones I have helped in or who posted them.

 

[hsandeep]

I had forgotten about that one.

That's okay..what I really need is a solution for my "THIS" Post..even now I will bear a vba solution, preferably if this new vba is merged with the old one, else a separate Worksheet vba

 

[Peter_SSs]

even now I will bear a vba solution

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

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

hsandeep.xlsm
	E	F	G
1			-2
2	121
3	115
Sheet2

Cell Formulas
Range	Formula
E2	E2	='7'!H120
E3	E3	=myOffset(E2,G1)

 

[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

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

hsandeep.xlsm
	E	F	G
1			-2
2	121
3	115
Sheet2

Cell Formulas
Range	Formula
E2	E2	='7'!H120
E3	E3	=myOffset(E2,G1)

Sir, it works perfectly well.
Btw, what is being returned by 2 & (1) in the formula?
Where is cell G1 in the code?
Will you explain in detail the actions of the code, please, so that I can manoeuvre it as per my requirements in future?

 

[Peter_SSs]

Btw, what is being returned by 2 & (1) in the formula?

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)

Where is cell G1 in the code?

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

Will you explain in detail the actions of the code

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

 

[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