Hyperlink to another sheet based on specific text in cell

Question

I'm working on a project involving pricing information for various retailing stores. On the main page, the user can enter in a store in cell G6. There will be a sheet with the exact same name as the user's cell entry. For example:

User enters "Wal-Mart" -- Sheet named "Wal-Mart" User enters "Target" -- Sheet named "Target"

My question is, how can I have the cell automatically hyperlink to the sheet of the same text value? I would prefer a formula over VBA, but any help is appreciated.

Thank you! - DJ

Answer 1

Without using VBA to detect the change, and update the hyperlink, you could not affect the same cell

You can, however, use another cell that would have the appropriate hyperlink. You do have to know the name of the workbook, as the hyperlink requires the workbook name (i.e. just "Target!A1" would not work)

=HYPERLINK("[MyWorkbook.xlsx]" & G6 & "!A1","Goto Sheet")

This will create a link in the cell you put this formula in, which would then go to A1 of the sheet name typed in.

Example View:
Clicking the Goto Sheet will go to the cell referenced in the formula (in this case, [MyWorkbook.xlsx]Sheet2!A1 )