I currently have an excel file that has values in the Cols that represent
percentages of compunds found in the rows. The problem is that the first 4
rows contain 4 chemicals for the ist sample date while rows 5,6,7, and 8
contain the same 4 chemical sampled on a later date. The repetition is
completed over and over.

This makes charting difficult because I cant just select a data range of
continuous cells for compund A. I thought that to make a work around I would
just create an additional worksheet that referenced the original worksheet
and did not skip rows. So the new worksheet would only contain Compund A and
its concentrations at points 1 ,2, 3... on each date.

Then of course the second new worksheet would contain refrences to compund
B...

the problem is that when I form the new worksheet I am placing each date on
sucessive rows... but when I try to copy the refrenced values I am copying
them from the original woorksheet where the values are actually found on
every 4 th row... how can I tell excell that for every row I copy down in
the new worksheet i want to reference a row down 4 rows in the original
wooksheet.

I am sure this is possible but now I have confused myself. If I need to
clear this up then please let me know. I love this community, you allow
incompetent people like myself to impress our bosses!! you rock!

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

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

Maybe this might help.

Say your data is in column B starting at B4. In a blank column off to the
right, say M, put this formula and copy down as far as needed.

=OFFSET(B$4,(ROWS($1:1)-1)*4,,)
that should extract all your Compound A's

use
=OFFSET(B$5,(ROWS($1:1)-1)*4,,)
in column N for compound B's
etc.
etc.

HTH
Martin