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]

You're starting to confuse me, but do you mean something like this:

Book1
	FO	FP
1	347	65.71%
2	347	65.71%
3	347	65.71%
4	347	65.71%
5	347	65.71%
6	347	65.71%
7	347	65.71%
8	347	65.71%
Sheet1

Cell Formulas
Range	Formula
FO1:FO8	FO1	=Sheet2!$AL$2
FP1:FP8	FP1	=Sheet2!$AM$2

 

[HelloAce]

You're starting to confuse me, but do you mean something like this:

Book1
	FO	FP
1	347	65.71%
2	347	65.71%
3	347	65.71%
4	347	65.71%
5	347	65.71%
6	347	65.71%
7	347	65.71%
8	347	65.71%
Sheet1

Cell Formulas
Range	Formula
FO1:FO8	FO1	=Sheet2!$AL$2
FP1:FP8	FP1	=Sheet2!$AM$2

Sorry, I am still learning so finding it difficult to explain myself. I was just hoping that instead of

FO1

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

I could do something like

FO1

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

 

[kevin9999]

FP2 is on sheet 1. The Offset formula you'd need would be:

=OFFSET(Sheet2!$AM$2,0,-1,1,1)

which is a long way of saying:

=Sheet2!AL2

 

[HelloAce]

FP2 is on sheet 1. The Offset formula you'd need would be:

=OFFSET(Sheet2!$AM$2,0,-1,1,1)

which is a long way of saying:

=Sheet2!AL2

I understand that FP2 is on sheet 1 but was hoping to use that in the formula. To manually reference AM2 in each formula would take a lot of time. The only common denominator between what I am trying to do I FP2.

 

[kevin9999]

What do you currently have in the cell FP2?

 

[HelloAce]

What do you currently have in the cell FP2?

Just the text 65.71 and hyperlinked to AM2

 

[kevin9999]

OK, hyperlinking it to AM2 does nothing useful in terms of referencing in this case. What I don't understand is why

To manually reference AM2 in each formula would take a lot of time.

whereas to reference FP1 each time wouldn't?

If you absolute reference AM2 like this: $AM$2 (as I showed you in post #11) then you use the same formula in every cell in the column - you just autofill it down.

 

[HelloAce]

OK, hyperlinking it to AM2 does nothing useful in terms of referencing in this case. What I don't understand is why

whereas to reference FP1 each time wouldn't?

If you absolute reference AM2 like this: $AM$2 (as I showed you in post #11) then you use the same formula in every cell in the column - you just autofill it down.

I understand but in sheet 2 there is no order to any of the data, it is all over the place, where sheet 1 continues as a set. I am thinking it isn't possible as you said by hyperlinking it is not useful in referencing.

 

[kevin9999]

If the Data in sheet 2 is "all over the place" then even if you could use hyperlinks - you'd probably need to use different ones to point to the different data locations? Don't see the saving there myself? This thread has moved a long way from referencing a named range to where we are now. Before we go any further, why don't you share your actual sheets using the XL2BB add-in, or alternatively share your file via Google Drive, Dropbox or similar file sharing platform, as well as a clear explanation of what your ultimate aim is?