0

Important: I asked this question before and it was merged into another question where the solution is using a script. Such a solution, with a script, is not helpful to me, so please only offer a solution with formulas, thanks.

I have a main sheet with a list of the names of the other sheets in the workbook. I want to create a glossary in the main sheet linking to cell A1 in each of the other sheets (a simple Hyperlink). In Col A - list of sheet names (e.g. Jan, Feb) In Col B - I want the hyperlinks (e.g. a link to Jan!A1, a link to Feb!A1)

What I can do, is use INDIRECT and CONCATENATE to bring the respective value of the cells (I've added it in Col C in the example below), but I can't find how to create the links.

Here's an example: https://docs.google.com/spreadsheets/d/1C1LghRPcutYBwgZZaitJQ1PVrgggr5XdvG6V7L32eE0/edit#gid=0

Tom
  • 183
  • 1
  • 3
  • 12

1 Answers1

2

and here we go again... this is your previous question: stackoverflow.com/questions/70054228 (for reference)

what you don't need is:

  • INDIRECT formula
  • CONCATENATE formula

because these are not capable of solving your issue

to solve your question:

Hyperlinking to sheets without using a script

you need two things:

  • HYPERLINK formula
  • #gid= number

GID number can be acquired from the URL of every sheet tab. for creating a list of all GID numbers you need a script. since the script is not an option for you (as for your request), your only option is to create a list of GIDs manually - eg. going into each tab of your spreadsheet and extracting GID numbers from the URL

note: each spreadsheet has unique GID numbers for each of the sheets. only first sheet on every spreadsheet has GID value set to 0 - #gid=0

after you will have all the necessary GIDs (let's say in a column) you can go for the next step and use HYPERLINK formula:

enter image description here

instead of the URL as the first argument, we use GID to create jump links:

=HYPERLINK("#gid=1220119768"; "Sheet 2")

of course, this is the same as using full URL:

=HYPERLINK("https://docs.google.com/spreadsheets/d/1yaRwbLGmDeynYktvxmLJcCBZrAvqgJA-_nYTlVf92Tc/edit#gid=1062970060"; "Sheet 2")

the only difference is in formula length

for multiple hyperlinks we can use arrayformula:

=ARRAYFORMULA(HYPERLINK("#gid="&A2:A5; B2:B5))

where:

         A                  B
1
2    1062970060      Sheet 2
3    2118975038      Sheet 3
4    273293449       Feb
5    1564587416      some other label
6

if you want your hyperlinks to lead for example to F8 you can add &range=F8 so:

=ARRAYFORMULA(HYPERLINK("#gid=" & A2:A5 & "&range=F8"; B2:B5))

also, if it wasn't clear... labels are not mandatory. you can skip them and have just:

=HYPERLINK("#gid=1220119768")

and also keep in mind that the 2nd argument of HYPERLINK can take even formula:

=HYPERLINK("#gid=1220119768"; INDIRECT(A2&"!A1"))

and to save your time... INDIRECT is not supported under ARRAYFORMULA. the only workaround for that would be to place multiple INDIRECTs into array {}:

=ARRAYFORMULA(HYPERLINK("#gid="&
 {"1220119768"; "2118975038"; "273293449"; "1564587416"}; 
 {INDIRECT(A2&"!A1"); INDIRECT(A3&"!A1"); INDIRECT(A4&"!A1"); INDIRECT(A5&"!A1")}))
player0
  • 124,011
  • 12
  • 67
  • 124