Hi everyone,

I have a worksheet which contains a pivot table in columns 1 through 6, then I have static data which I added manually in columns 8 and 9 (column 7 is also not part of the pivot table but only contains formulas, hence it will not be affected by the issue explained below).

When not using any filters, the rows from the pivot table (i.e. column 1 through 6) are properly aligned to the rows from the static data in columns 8 and 9. For example at row 10, cell R10C2 refers the row for the CISCO1721 chassis and manually added information are located in cells R10C8 and R10C9. See screenshot Inventory - Pivot Table All.jpg.

If I apply a filter by selecting a specific chassis such as CISCO1721 (by selecting CISCO1721 in cell R1C2), the number of rows in my pivot table obviously decreases which implies that the static data from columns 8 and 9 are not aligned anymore to their original rows. For instance, CISCO1721 from column 2 is now located at row 5 but the static data from columns 8 and 9 remain at row 10. See screenshot Inventory - Pivot Table CISCO1721.jpg.

-> I would have liked the data from R10C8 and R10C9 to also move up to location R5C8 and R5C9.

Does anyone know if there is a way to make sure that the data from columns 8 and 9 are dynamically updated in order to constantly remain associated to their chassis from column 2?

I would appreciate if anyone could help.

PS: spreadsheet is available from Inventory.xlsx.

Thanks a lot,
Ant