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.
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.
"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
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.