Google-sheets – How to apply arrayformula on a hyperlink so that I can reference another cell in a different sheet

google sheetsgoogle-sheets-arrayformulalinks

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?

Best Answer

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:

enter image description here

Sheet1:

enter image description here

Related Topic