Copy a set of data based on a value in one column

[Pltwin2]

I have been trying to set up a formula that will copy a line of data from one sheet to another based on one column. I've tried an If function, but this leaves alot of empty lines, and I've tried a VLookup but it creates alot of duplicates, and deletes the line below when new data is added from the master list. Any help would be appreciated.

A little more info about my spreadsheet format.
I have 2 sheets (MSDS and 313) I need to copy cells A:H of the rows with Yes in cell G from sheet MSDS to sheet 313. I also will need to insert extra lines into sheet 313 so that I can add more information(the specific chemicals that are SARA 313 regulated) into cell I:L. Typically there is more than one chemical per product and I need to be able calculate total of each chemical used.
[TABLE="width: 500"]
<TBODY>[TR]
[TD]PAGE MSDS
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product
[/TD]
[TD]Department
[/TD]
[TD]Manufacturer
[/TD]
[TD]Phone Number
[/TD]
[TD]Revision Date
[/TD]
[TD]Date Modified
[/TD]
[TD]313
[/TD]
[TD]Comments
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Aluminum Alloy,3105
[/TD]
[TD]108 Dept. Name
[/TD]
[TD]Golden Aluminum
[/TD]
[TD]555-555-5555
[/TD]
[TD]20140623
[/TD]
[TD]20140623
[/TD]
[TD]Yes
[/TD]
[TD]updated revision
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAGE 313
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product
[/TD]
[TD]Department
[/TD]
[TD]Manufacturer
[/TD]
[TD]Phone Number
[/TD]
[TD]Revision Date
[/TD]
[TD]Date Modified
[/TD]
[TD]313
[/TD]
[TD]Comments
[/TD]
[TD]Chemical
[/TD]
[TD]Composition
[/TD]
[TD]Usage
[/TD]
[TD]Total Amount
[/TD]
[/TR]
[TR]
[TD]Aluminum Alloy, 3105
[/TD]
[TD]105 Dept. Name
[/TD]
[TD]Golden Aluminum
[/TD]
[TD]555-555-5555
[/TD]
[TD]20140623
[/TD]
[TD]20140623
[/TD]
[TD]Yes
[/TD]
[TD]update revision
[/TD]
[TD]Aluminum
[/TD]
[TD]95%
[/TD]
[TD]5lbs
[/TD]
[TD]4.75lbs
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Zinc
[/TD]
[TD]0.1%
[/TD]
[TD]5lbs
[/TD]
[TD]0.5lbs
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[Andrew Poulsom]

Welcome to MrExcel.

Why don't you AutoFilter the data on sheet MSDS for 313 equals Yes and copy the visible rows to sheet 313? A formula solution will be pretty complex, particularly if you are going to be inserting rows on the sheet that contains the formula.