How to consolidate my data to make functional pivot and chart for my reports

Hello there!

I need someone to help, please!

As of now, I have to consolidate or put together data from more than 20 worksheets on the same workbook, in order to make a pivot table with chart that goes on a weekly report I have to hand out to my boss. I need to find a way of making the consolidation process automatic because data will update every week, and I simply don’t visualize myself manually copying and pasting data from all these worksheets into a master tab (the consolidation tab).

I have enclosed a file to show you how I have data organized on my worksheets (See tabs ITEM1, ITEM2, and ITEM3), and what I’d love to obtain with your help (See CONSOLIDATED ITEMS tab to see how I’d like to have data automatically put together to be able to make the pivot and chart found there). The ITEM tabs have 4 columns: the first one is for checkpoints or dates; the second one stands for countries; the third one is for items (say, for instance, # of clients, # of agents or salespersons, units of product sold, etc.), and lastly, a column for values.

The reason why I have put items all together on a same column rather than putting all of them on separate ones as someone would normally do, is because I need to be able to chart my pivot so that all items be shown one at a time and not all together at once (see chart on CONSOLIDATED ITEMS tab).

Making a consolidated pivot hasn’t worked for me since I lose columns content when doing the consolidation (See consolidated pivot attached on CONSOLIDATED ITEMS tab).

I don’t think Excel’s consolidate feature could work either since data will be different every week (not every country and every item will show up on the ITEMS tabs every week); so data’s format is not consistent.

So, is there a way to do this? What about using array formulas so that data can be automatically updated on the master tab? I don’t have any clue of how to do this.