Auto Populate Column in Table Based on Multiple Criteria in Another Table

[AgingRapidly]

I have a spreadsheet in which I have tables that contain data pulled from our inventory software.

The main table CI_Item contains information for everything we make or buy with the fist column being the item number.

All of the jars have a value of PM10 in the ProductLine column; those that have not been discontinued also have a value of R in the ProductType column.

I would like to populate the first column in a new table for jars only (JarTable) that would give me the item number of everything where ProductLine=PM10 and ProductType=R. My thought is that when a jar is added or discontinued in the inventory software and updated on the CI_Item table, that the JarTable would update also. Once I have the item numbers on the JarTable, I can pull data from other data tables using Index/Match.

After working it our for jars, I would then use same idea with different ProductLine values to create tables for caps, boxes, raw materials and finished goods.

I've been reading internet posts, asking co-workers and watching training videos. I've tried a few things, but being new to complicated formulas, I've not yet found the solution.

Any help would be gratefully appreciated.

 

[Jerry Sullivan]

Welcome to MrExcel,

You might try using the Advanced Filter function.