Extract Data from Named Ranges

[KATPIF]

I have a dynamic named range with 2 columns, Task (column A) and Resource (column B). (Sheet 1) Both column A and B are defined as a table and are each a named range [tbl]Assign, TskAssign and RscAssign

I would like to create a formula that would display all resources associated with a single task and all Task associated with a resource (sheet 2).


I would rather not use the auto filter as management will be updating these quite often as they frequently manage workloads and rearrange resources as new tasks arise.

Is it possible to accomplish this using a formula (I am not familiar with VBA)?
Excel-001.png

[ben_hensel]

I'd feed the data into two pivot tables; one for each arrangement.

[KATPIF]

Thanks Ben, I was just looking at that. Is there a way I can have the pivot table refresh when my named ranges change on sheet 1?

[ben_hensel]

Select the pivot table, then "right-click: refresh" or "ALT+F5"

That's the most straight-forward way; it will re-grab the data feeding the pivot table. If that's all you need, great!

If not.... automatic updating requires VBA and has some downsides.

[KATPIF]

It is too bad there is no way to auto-update the pivot table without VBA. Thank you for your assistance.

[KATPIF]

The Pivot Table is not going to work for this spreadsheet, I need the information to be constant so weekly hours can be added to additional columns. Does anyone have any ideas on how I can extract the data without using a pivot table?