0

In Google Sheets, I'm trying to write a QUERY which outputs a column displaying a hyperlink which links you to the cell pulled in by the QUERY, in another tab on the same sheet.

Example (all within the same Sheet):

Tab 1:

**Name**
Chandler
Ross
Joey

Utilizing the hyperlink() function as I read from this question, I've been able to link to another tab within the same sheet by hardcoding the hyperlink formula. But how can you incorporate this within a QUERY?

Tab 2: (my attempt but errors out)

=QUERY(Sheet1!A:A,"SELECT hyperlink("#gid=123456789range=A2", A2)")

Desired Output:

**Name**
Chandler [hyperlinked to cell A2]
Ross [hyperlinked to cell A3]
Joey [hyperlinked to cell A4]
Ken
  • 1,001
  • 3
  • 14
  • 27
  • Figured out how to do it with a helper column, but not ideal: =IF(NOT(ISBLANK(B21)),HYPERLINK("#gid=0range="&"D"&MIN(ArrayFormula(IF('Project Management'!D:D=B21,ROW('Project Management'!D:D),""))),"Project Link"),"") – Ken Oct 16 '18 at 00:44

1 Answers1

2

I am going to give the answer in more general terms than you asked for, so that it is (hopefully) more useful to others.

You can do this by turning your attempt inside out. That is, use QUERY to pull the data, input it into HYPERLINK, and then use ARRAYFORMULA to do this for the whole list. Since you need the matched pairs of the names and the corresponding URLs, QUERY will be used twice with the same filter condition.

For example, if you had the table:

**Table A1:C4**  
Name, URL, Rating  
SE,https://stackexchange.com, 2  
Physics SE,https://physics.stackexchange.com, 4  
Maths SE,https://math.stackexchange.com, 5

and you wanted to list all the URLs where the rating was greater than 3, labelled by the corresponding name in column A, then you could use:

=arrayformula(hyperlink(query(A2:C4,"select B where C>3,0),query(A2:C4,"select A where C>3",0)))

This will output a column containing two links:

SE
Maths SE

Paul
  • 887
  • 6
  • 22