Excel Formula in Same Workbook using Named Range 1

[Randy11]

Have named cell B5 on Sheet AAA1 as A1.
Would like to use A1 in hyperlink formula so that if the sheet name changes, it will still go to the same sheet & cell B5. Current Hyperlink works but does not if sheet name changes.
Assistance appreciated

=HYPERLINK("#"&"AAA1!$b$5",'AAA1'!$B$2)

 

[SkipVought]

Have named cell B5 on Sheet AAA1 as A1

I do not believe that THAT is at all possible!

You cannot Name ANY cell with a RANGE REFERENCE.

A1 is a RANGE REFERENCE.

Please explain the PURPOSE of naming B1 as A1?

How would you EVER be able to reference A1 if you COULD do that? It makes no sense!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Randy11]

My apologies. You are very right. Name the range AAA for the sake of the discussion.
Want to used a named range (AAA) as the location (Sheet AAA1 Cell B5) to Hyperlink to so that is the worksheet name changes, the hyperlink to that location will still work.

 

[Mouldheels]

I'm not sure if you are being too sophistated in using the =HYPERLINKS formula

Name the range on the appropriate worksheet
Go to where you want your hyperlink index to be
Right Click
Add Hyperlink
Select PLACE IN THIS WORKBOOK
Select the named range

If the sheet name changes this will not effect the link.

is that too simple
(remember I'm a self-effacing brit)

 

[Randy11]

Thank you, this is perfect & you are right, that is too simple. Was not aware this method was available.