Creating Dynamic Table Based Off Master Table with Multi-Selection Dropdown List

Hi,

I am creating a dynamic table based off a master table. My master table has a part number and suppliers. I want to create a dynamic table in a new tab, where each supplier has their own tab. The suppliers are listed in a drop down menu that can select multiple values (so you can have "Supplier 1" in one cell or "Supplier 1, Supplier 2, Supplier 3" in another).

Currently I have:

=IF(ROWS(A$4:A4)>$L$1,"",INDEX(AVL[SAP'#],SMALL(IF(AVL[Suppliers]=$I$1,ROW('Approved Vendor Lookup'!$J$2:$J$260)-ROW('Approved Vendor Lookup'!$J$2)+1),ROWS(A$4:A4))))

Where:
-AVL[Suppliers] is the master list supplier (aka: 'Approved Vendor Lookup'!$J$2:$J$260)
-AVL[SAP'#] is the master list part number (aka 'Approved Vendor Lookup'!$A$2:$A$260)
-$I$1 is where the supplier name put
-$L$1 is a count function that determins the number of rows where the supplier is found and limits the cell

Currently I am able to get the table to propogate some values. If I enter in Supplier 1 into I1 it will return with a count of 4 and propogate 4 rows. This corresponds to the 4 rows in the master sheet that have only Supplier 1 as a supplier. If I enter in *Supplier 1* I receive a count of 16 (this is all of the parts supplied by Supplier 1), but I am returned with a #NUM error.


I have tried:
-Changing $I$1 to "Supplier 1";"*Supplier 1*"; and other combinations
-Changing from the named array to the actual cells, e.g. AVL[Suppliers] to 'Approved Vendor Lookup'!$J$2:$J$260

I attempted to create multiple supplier columns that propogate based off the Supplier Column, but now I am faced with the issue of changing the equation to read all the columns searching for Supplier 1.


The reason I am using this instead of a pivot table or macro is that I have several of these tabs that are pulling data from all other sources based off this part number. The formula makes it so I do not have to manually enter in all the different locations the part number every time I add a new part to the master list.

I hope I have explained this well enough. I could attempt to load images (work blocks most storage sites) if required.

Also to get a better idea of what I was doing you could watch this video.

And I have attached a sample workbook Dynamic Tables with Master Table and Dropdown List Example.xlsm

Hi,

Unfortunately it appears that any testing of potential solutions in this file will be impossible as all pertinent formulas are referencing external files to which, obviously, we have no access.

Regards

Thanks.

I couldn't find a reference to cell I1 in any tab, but in A4 of the Dynamic Table Based Off Columns tab, if I understand you correctly, this array formula**:

=IF(ROWS(A$4:A4)>$E$1,"",INDEX('Master Table with columns'!A$2:A$24,SMALL(IF(ISNUMBER(SEARCH($B$1,'Master Table with columns'!$D$2:$D$24)),ROW('Master Table with columns'!$D$2:$D$24)-ROW('Master Table with columns'!$D$2)+1),ROWS(A$4:A4))))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

"You used the ISNUMBER(SEARCH($B$1,'Master Table with columns'!$D$2:$D$24)) to search my column for a word (Series1 in this case). If it found this word it returned a value corresponding to the location of the first letter (e.g. 1 if it was the first word in that cell).

Which then the IF() function would return a true and the rest of the function would carry out..."

Spot-on analysis.

"is it inappropiate to say I love you?"

Yes!

Why not just have a drop down list in B! on the Dynamic Table worksheet? The drop down list could have all the different combinations of Suppliers. Your existing formulae will already work with this.

The list would look like this as taken from your Master Table:

Series2, Series1
Series1, Series2
Series2
Series1
Series3, Series1
Series3


I have tested this with your workbook and it works perfectly.