Automatic Table Names?

[wagwan_jc]

Hi,

I have a sheet which contains a list of many tables. Each Table is for a set,
Table1 > Set 1
Table2 > Set 2
Table3 > Set 3 .......
Table99 > Set 99


Each set contains its own list of items and the qty of each item (column[Model#] and column[Qty]) . And at the end, each set has a qty for the set.

Example of Table1 and Table2:

Set #1
MODEL# QTY
x 4
xy 1
xyz 3
xx 2
xxy 1
xxz 1
QTY OF SET1 50

Set #2
MODEL# QTY
x 4
zzz 1
xyz 3
xx 2
xyy 1
xxz 1
QTY OF SET2 65

Set3 will have some repeating model # and some different. And so on for each set.

In a different tab we have created a TableBOQ. This table will list each individual item found in all the tables. The objective is; for each item to search for the matching model# in each table and if found return the (item qty) X (set quantity).

Example of TableBOQ:
A B C D E F MODEL# QTY set1 set2 set3 set4
1 x
2 xy
3 xyz
4 xx
5 xxy
6 xxz
7 xyy
8 xzz

In cell C1, I have used following formula:
C1=IFERROR(VLOOKUP(TableBOQ[@Model#],Table1[[#All],[Model]:[Qty]],2,FALSE)*B$10,0)
This will look for Model#[x] in Table1 and return the Qty multiplied by the Qty of the set.

Therefore in Cell D1, we will use the same formula but change the Table name and the cell containing the Qty of set;
D1=IFERROR(VLOOKUP(TableBOQ[@Model#],Table2[[#All],[Model]:[Qty]],2,FALSE)*B$24,0)
This will look for the Model#[x] in Table2 and if found return the qty multiplied by the qty of set2.

And so on, until the last set.

Then in the next row we will use the same method to search for Model[xy] in each set and return the quantities.

And so on.

PROBLEMS WITH MY FORMULA:

- When creating the tables, The table names must be changed manually to follow the serial. Then when creating the formula, both the table name and the cell containing the qty of set must be entered manually. If for example after completing the sheet, I need to enter a new table between tables 1 and 2, this will ruin the sequence, and will have to adjust manually in the BOQ page.
My question is; Is there a way to make table names automatically follow a sequence so that if I were to insert a table and/or delete a table a table names would automatically adjust?

Hope its clear,
Thanks;

 

[Jeffrey Mahoney]

I want to help. Darn, wish I could see the data. Have you looked at using the INDIRECT function. You could put the TABLE names above the formulas and use the Indirect function. Then you could just change the TABLE names and the formulas automatically look at those tables.

 

[Oeldere]

Make sure all data (for all sets) looks like below. After that it is easy to analyze the data with a pivot table.


Set #1 Set #1
Set #1 MODEL# QTY
Set #1 x 4
Set #1 xy 1
Set #1 xyz 3
Set #1 xx 2
Set #1 xxy 1
Set #1 xxz 1
Set #1 QTY OF SET1 50

 

[wagwan_jc]

Hi, Thank you for the help;

I dont think INDIRECT Function will work, as well as a Pivot table.
I have created a basic sample of the Data and taken a screen shot.
Kindly if you could check the links below, should make more clear;
Thanks,
1)SETS
2)BOQ

 

[Oeldere]

I dont think INDIRECT Function will work, as well as a Pivot table.

I know this will work in pivot table.

 

[wagwan_jc]

I have added the column to the SET table at the beginning, to setup the data in the way you have mentioned, but can you advise as to how to set up the pivot table to get the matching model # qty?

 

[wagwan_jc]

Is there a way to create 1 pivot table from all the data in the tables from SETS?

 

[Oeldere]

for the pivot table.

rows => model

value => quantity

Make sure all data is in the table (before you make a pivot table).