How do I apply arrayformula on a hyperlink so that I can reference another cell in a different sheet?

Question

I have a column on sheet2 where each cell has a hyperlink redirecting to cells of a column of sheet1.

I have manually gotten the "Link to this cell" and put in under hyperlink to be able to achieve the desired functionality. I understand that to access different cells I will just have to change the range.

I have created each of these links manually using the following format:

In Sheet2 A2: =HYPERLINK("https://docs.google.com/spreadsheets/d/********************************************/edit#gid=**********&range=A2", "Sheet1-A2")

Sheet2 A3: =HYPERLINK("https://docs.google.com/spreadsheets/d/********************************************/edit#gid=**********&range=A3", "Sheet1-A3")

I have a thousand rows in this sheet and so it becomes cumbersome to do this. Is it possible to do this using arrayformulas?

Answer 1

You need to wrap your formula with an Arrayformula and generate as many rows as column A in Sheet1 has a value in it:

=ARRAYFORMULA(if(len(Sheet1!A2:A),
HYPERLINK("https://docs.google.com/spreadsheets/d/xxxx/edit#gid=xxxx&range=A"&row(A2:A)
, "Sheet1-A" & row(A2:A)),""))

Sheet2:

Sheet1: