Google Spreadsheet: Dynamic Hyperlink Formula

Question

I'm trying to create a dynamic HYPERLINK formula that will automatically create the link based on the sheet name in Column A, but I'm not sure how (or if it's possible) to get the URL of the sheet based on the name.

Here's the setup:

• Single Google Spreadsheet with multiple tabs
• Tab names: 1500, 1501, 1502, Consolidated

On the Consolidated tab, I have two columns: Column A is the Sheet Name, and Column B is a HYPERLINK formula, that when clicked should open the corresponding sheet.

Is there a way to programmatically get the URL for the sheet based on the sheet name in Column A? Perhaps I could use a script to populate Column C with the URL, then use the following formula: =HYPERLINK(C2,A2)?

Thanks for the help!

Answer 1

Surprised to see this as a top search on Google but with no answer.

Anyway, here's the method I found that works for me: combine Hyperlink with values from a different column using the &, a basic example is shown below:

Answer 2

If you are trying to automatically generate direct URL's to specific sheets based on their name, and do not want to use scripts, you are out of luck. Currently, the only way to link directly to specific sheets is by appending the correct gid number to the spreadsheet URL. A gid must be either copied manually from the active sheet's URL, or automatically extracted with a custom function, created using scripts.