Should be simple, but I'm stuck... Please help!

[mmccabe]

I'm trying to create a HYPERLINK formula that references cell A1, on a different sheet, within the same workbook.

If my workbook was always named "Book1.xls," I could simply use the following formula:

=HYPERLINK("[Book1.xls]Sheet2!A1","Next Page"),"")

Unfortunately, the workbook I am working with gets renamed constantly (for different users, versions, dates, etc), so a static reference to "[Book1.xls]" won't work.

Is there any way to reference the 'Active Workbook' within the formula, so that the hyperlinks will work after the file has been renamed?

With a macro, this would be straight forward, but I'm trying to avoid VBA for this particular project. I just realized that I have no idea how to reference the active workbook within a formula. Can anyone help me out?

Thanks,

Mike

 

[Jonmo1]

If the sheet is in the same book, you don't need to specify the book...

You can just use

=HYPERLINK(Sheet2!A1,"Next Page")

 

[mmccabe]

Jonmo1,

Thanks for the reply... I tried your suggested approach initially, but for some reason, the link doesn't take me anywhere. Excel recognizes the formula as a hyperlink, but when I click on "Next Page," nothing at all happens. There is no error message and I am not directed anywhere.

When I include the [Book1.xls] reference, the hyperlink works as expected, but like I mentioned, the static reference won't work if/ when the book is renamed.
Any other ideas?

Mike

 

[mmccabe]

By the way, I just tried removing the [Book1.xls] from the working formula, while keeping the surrounding quotes, like this:

=HYPERLINK("Sheet2!A1","Next Page"),"")

Excel accepted the formula, but when I attempted to follow the link, I received the following message: "Cannot open the specified file."

Without the quotes (& without [Book1.xls]), I did not get this error message, but the link didn't take me anywhere.

Mike

 

[MikeLiberty]

Just to confirm, are you going to then try to copy the formula to other cells and have the linked reference adjust?


Otherwise I would just say to right click on the cell and set the hyperlink that way. I don't think you can use the formula without the filename reference