How do I use text in one cell to trigger row to be copied on another sheet in Excel?

Question

I provide all of the cut lists for our cabinet manufacturing in Excel. I tally all parts for the entire job on the first worksheet in an Excel file, and then filter the rows based on the "Material" column, and manually copy/paste each row in to its own material-specific worksheet (example: I filter "Materials" column for "Maple Ply", and then copy all "Maple Ply" rows to the "Maple Ply" worksheet). Then the material specific worksheets are sent to the shop floor for cutting. This is time consuming, and if I need to change any data in the first page, I have to go and manually update the copied row in its material-specific page.

Is there any way to make each material page "look" for its material, and automatically populate itself with any row that has the appropriate material in the material column (example: any time I enter "Maple Ply" in the material column of sheet one, that row is automatically copied to the "Maple Ply" worksheet)? If so, could this link be dynamic, rather than just a copy, so that if I change a cell in a particular row on sheet one, that data is also updated on the material-specific worksheet copy?

Thank you, Brian

Answer 1

I'm not 100% clear on what you are trying to do, but if I've understood the problem correctly, you can use columns full of VLOOKUP functions to suck the matching rows out of the source worksheet. For instance:

item material  color                           weight                          ...
---- --------- ------------------------------- ------------------------------- ------
1    Maple Ply VLOOKUP('mtrls.xls'A:J, $B2, 2) VLOOKUP('mtrls.xls'A:J, $B2, 3) ...
2    Hardwood  VLOOKUP('mtrls.xls'A:J, $B3, 2) VLOOKUP('mtrls.xls'A:J, $B3, 3) ...