Thanks I was trying that yesterday and it worked for creating separate
woorkshhet for each individual compound... but then I thought hey why not
just create the chart while the filter is in effect for compund A... so I did
but now when I change the filter to show compound B... the compund A chart
values change... so I guess that I will have to create separate worksheets
for each compound.

any other work around suggested???

"MartinW" wrote:

Hi Will,

You can insert a helper column next to your data and put 1,2,3 and 4
in the first four cells, then highlight all four cells, grab the fill handle
and drag it down to the end of your data while holding down the ctrl key.
This will leave you with repeating series of 1 to 4 running down the column.

You can now use autofilter on your helper column. Filtering on 1 should
give you all the compound A's, filtering on 2 should give you all the B's
etc.

Then just copy and paste each filter to a new worksheet.

HTH
Martin