Apologies if the title seems confusing, I didn't know how to put my issue in 1 sentence.
I work as an inventory coordinator and part of my job is checking inventory accuracy for 4 warehouses that handle our products but we don't own those warehouses and they work on different ERP systems.
I get emailed a daily stock report from each of them and I then generate a report comparing our stock numbers in our own ERP system against the numbers they send me in the daily reports. After that, I resolve any stock variances and write my comments (e.g. 500 cases of product X receipted in ERP system).
I currently make 4 of these book-to-book reports every day, each of them having the same lay-out in Excel but they are separate workbooks. They each contains sheets where I input the raw data, which gets sorted nicely with a pivot table and the first sheet pulls the data from both pivot tables in and shows me the variances with some simple formulas and conditional formatting.
https://ibb.co/CpLMQZ7
What I would like to do, is combine those 4 reports into 1 workbook where the first sheet has some sort of filter/slicer that switches between the 4 warehouses.
How do I go about setting up this filter so that if I choose a different warehouse, I get a "clean" sheet to work on without losing the work I did on the other warehouses? I would prefer to keep working with pivot tables for each warehouse since the same item can be repeated in different locations within that warehouse (e.g. QA for blocked stock) which is important since not all products are part of the available stock.
https://ibb.co/pz12tF9
I can set up a pivot table with the data from all 4 warehouses and a calculated field to show the variance in both stock numbers and then cycle through them with a slicer or something but I would need to type in some data in a normal cell to explain that variance. Can those cells somehow be linked to the pivot table maybe? So that if I switch from warehouse A to warehouse B, can I somehow "re-use" cell L10 for example to write a different comment/data for multiple warehouses and that comment will change depending on what warehouse I look at?
