Returning a result related to a cell with a defined name

[HelloAce]

Hi all, I have a cell that is linked to a defined name of a cell from another sheet. Is it possible to use a formula to return the cell to the left of this linked cell from the other sheet?

Thanks

 

[kevin9999]

If you mean return the value to the left of a named range (single cell) where the named range is on another sheet? Short answer is yes, if that's what you mean, using the Offset() function. Here the named range is on sheet 2 (called NamedRange), the offset formula is on sheet 1.

Book1
	A	B
1
2	123
3
Sheet1

Cell Formulas
Range	Formula
A2	A2	=OFFSET(Book1!NamedRange,0,-1,1,1)

Book1
	A	B
1
2	123	named
3
Sheet2

 

[HelloAce]

If you mean return the value to the left of a named range (single cell) where the named range is on another sheet? Short answer is yes, if that's what you mean, using the Offset() function. Here the named range is on sheet 2 (called NamedRange), the offset formula is on sheet 1.

Book1
	A	B
1
2	123
3
Sheet1

Cell Formulas
Range	Formula
A2	A2	=OFFSET(Book1!NamedRange,0,-1,1,1)

Book1
	A	B
1
2	123	named
3
Sheet2

Thank you for your prompt response. I might have done something wrong with your solution or I probably didn't explain it well. As seen in attached images, the cell FP1 in Sheet 1 is linked to cell AM2 in sheet 2. AM2 has the defined name Lee. I want to return the result from AL2, one left, which is 347. Hoping this is possible. Thanks again

 

[kevin9999]

You haven't said where you want to put your formula, so I've put it in FO1 on sheet 1, but you can put it anywhere really.

Book1
	FO	FP
1	347	65.71%
2
Sheet1

Cell Formulas
Range	Formula
FO1	FO1	=OFFSET(Book1!Lee,0,-1,1,1)
FP1	FP1	=Lee

Named Ranges
Name	Refers To	Cells
Lee	=Sheet2!$AM$2	FP1

Book1
	AL	AM
1	21
2	347	65.71%
Sheet2

 

[HelloAce]

You haven't said where you want to put your formula, so I've put it in FO1 on sheet 1, but you can put it anywhere really.

Book1
	FO	FP
1	347	65.71%
2
Sheet1

Cell Formulas
Range	Formula
FO1	FO1	=OFFSET(Book1!Lee,0,-1,1,1)
FP1	FP1	=Lee

Named Ranges
Name	Refers To	Cells
Lee	=Sheet2!$AM$2	FP1

Book1
	AL	AM
1	21
2	347	65.71%
Sheet2

Sorry yes FO1 was correct. What do you mean by Book 1?

 

[kevin9999]

You don't really need that, just use:

=OFFSET(Lee,0,-1,1,1)

 

[kevin9999]

Book1
	FO	FP
1	347	65.71%
2
Sheet1

Cell Formulas
Range	Formula
FO1	FO1	=OFFSET(Lee,0,-1,1,1)
FP1	FP1	=Lee

Named Ranges
Name	Refers To	Cells
Lee	=Sheet2!$AM$2	FP1

 

[HelloAce]

Book1
	FO	FP
1	347	65.71%
2
Sheet1

Cell Formulas
Range	Formula
FO1	FO1	=OFFSET(Lee,0,-1,1,1)
FP1	FP1	=Lee

Named Ranges
Name	Refers To	Cells
Lee	=Sheet2!$AM$2	FP1

Thanks, but am I able to do it if FP1 isn't =Lee but where just the text 65.71 and hyperlinked to AM2?

 

[kevin9999]

It doesn't matter, the formula will always return the value one cell to the left of the range you have named "Lee" - except if Lee is in column A, in which case you'll get an error (because there are no cells to the left of column A).

AM2 has the defined name Lee.

 

[HelloAce]

It doesn't matter, the formula will always return the value one cell to the left of the range you have named "Lee" - except if Lee is in column A, in which case you'll get an error (because there are no cells to the left of column A).

Sorry I didn't articulate that well, instead of typing in Lee, am I able to somehow use the FP2 reference so I am able to autofill for all the rest of the column to match?