How to create a table that gets data from multiple tables in separate worksheets and consolidate them

I have an excel workbook with three worksheets, and have 9 tables in each worksheet, the column headings and the first column all have the same value across all tables,

I am looking for a way to create a table in a new worksheet, that would get the value from those tables and add them together.

To elaborate, the worksheets name are Chest, Arm and waist

Each sheet has 9 tables based on their level, for example: level1, level2, etc.

Each table has the stats. S, D, R, V, C, L. Example below:

Armor	S	D	R	V	C	L
Armor1	0	2	1	5	7	0
Armor2	0	3	5	0	0	0

Item	Armor	S	D	R	V	C
Chest	Dropdown with all armor(col1) from 1st sheet	stats value based on dropdown selection	..	..	..	..
Arm	Dropdown with all armor(col1) from 2nd sheet	stats value based on dropdown selection	..	..	..	..
Waist	Dropdown with all armor(col1) from 3rd sheet	stats value based on dropdown selection	..	..	..	..

So in the new sheet, I want to create a table as something like below

I'm using excel 2016, and windows. Apologies if this is not sufficient information, feel free to ask me any questions.

If anyone can give me direction on how I could achieve it that would be appreciated

I tried to google and have created tables using the insert -> table, select the range and gave it a name.

Solution

I'm not sure if excel 2016 already supports named ranges, but this makes it simpeler, let me know if it doesn't work and I'll fix it.

So first up, make a few named ranges:

Select all your armor, name it 'Armor' (select the address cell in the top left and change it);
Select all your chests, name it 'Chest';
Select all your waists, name it 'Waist';

Now select the cell where you want your dropdown list, say for armor, go (on the top menu) to Data -> Data Validation -> pick list from the dropdown; as source write =Armor. Repeat for chest and waist.

You should now have a dropdownlist(s).

In the cells with the data, apply follow formula and drag down and to the right (start with S):

=INDEX(Armor!$B$2:$B$3 , MATCH($B2 , Armor), COLUMN(A1))

Or try this, but I'm again not sure if Excel 2016 already supports spilling:

=INDEX(Armor!$B$2:$B$3 , MATCH($B2 , Armor), 0)

The latter should spill out the entire selected range Armor!$B$2:$B$3.

The ranges here are based on your example data, so they need some adjustment.

Here is what it should look like (everything is on one sheet for me though):