Consolidating Data From Multiple Tabs / Worksheets By Column Name

Hello Everyone.

I have just registered so am new here. Firstly what a great community you have here. I was trying to see if I could find my answer before posting. And I am amazed at the knowledge and time people put in here. If anyone can help me I would be very grateful.

My problem is this... (excuse the terminology, I'm new to Excel too)

I have one Excel file with 18 tabs at the bottom. I believe they are called worksheets. Each worksheet is a different date. What I am working on is a delivery file so it shows me all the items that have shipped to various locations.

Along the top in Row 1 I have what I would call the column names. A1 is called EAN, below the EAN word from A2 downwards are a lot of barcode numbers.

From B1 onwards there are location names. For example, B2 London, C2 Manchester, D2 Birmingham etc. Going down each column is the number of items of that barcode that location has had.

Across the different worksheets there are some barcodes the same and there are some new ones as time goes on.

My end goal is to have just one worksheet with for example B2 London and then a list of all the barcodes and QTY they have had of each one. Is this possible?

Example (but much bigger)

WORKSHEET 1
EAN London Manchester Birmingham
5023620 2 5 2
5023661 4 1 3
2013367 1 4 2


WORKSHEET 2
EAN London Manchester Birmingham
4123659 2 2 1
5023661 3 2 3
4033699 6 3 1

And so on....


I tried using consolidation button but kept getting errors. And when I didn't get errors the numbers didn't add up correctly.

Please help if you can it would be amazing to get this sorted.

Kind Regards,

Thank you for your help.

SOLVED - Please can someone update the status for me or tell me how to do it.