I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence.

Crude representation:

enter image description here

I'd like to be able to create a pivot table in a different sheet with a unique column for each assignee, containing rows of task descriptions assigned to that person.

What I'd also like to do is having each cell in this table to actually be a =HYPERLINK(...) to the corresponding task description cell in the original sheet. This is something, as I understand, that is impossible with built-in pivot tables. I've tried constructing a pivot table with those specifications and succeeded, but the links were not clickable.

enter image description here

"HYPERLINK" column formula is

=HYPERLINK("#gid=0&range=A" & MATCH(Task, Tasks!A1:A, 0), Task)

Is it possible to accomplish this?

I've fiddled around with =QUERY(...) with mixed success as I've been unable to automatically generate columns for the table. What I have so far is query that generates headers for the table, and a query that generates rows for that header, but I have to manually copy and paste the second query - it's not automatically applied to ALL columns. For example, I've added a new task for "Purple" and corresponding tasks are not listed automatically.

This is an example of what I'd like to do. This is the closest I've gotten to the solution but still not quite right.


I've managed to avoid using =QUERY completely, and switched to =ARRAYFORMULA with =FILTER and {;} syntax.

enter image description here

All that is left with this approach, is to skip empty cells in columns. This is something I have no idea how to handle.

If anyone could chime in, I'd be really grateful.

  • Hey, can you share a dummy sheet ? or a screenshot with some data + expected result. Maybe a =query + pivot or a combination of =unique, =transpose and =filter – pjmg Mar 18 at 16:21
  • 1
    @pjmg I've edited my question with a link to the example sheet, thanks! – Puzomor Croatia Mar 19 at 8:34
  • It looks that you already managed how to get result that you was looking for on the Pivot(Query) sheet. Right? – Rubén Mar 20 at 20:27
  • Well no, because I'd like to avoid having to manually expand the formula across the columns. In Pivot(Query) when new assignees appear, I have to copy and paste the formula into a new column. I've added a new assignee - "Purple" and tasks assigned to purple are not shown in Pivot(Query). In Pivot(af+f), tasks are shown but with a lot of empty space between. – Puzomor Croatia Mar 21 at 21:42
  • @PuzomorCroatia Please update the question. – Rubén Mar 22 at 0:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.