I have a program working how i want it to work except for it is very sloppy coding. I was trying to make each category in my chart a dynamic range since it is always growing. I am using a drop down menu (in S4) to the right of the chart on the left (where all the data is displayed) with a list based on what is selected in the drop down. I am currently using an offset and indirect formula but when i created the dynamic ranges they would no longer work properly.

to make my dynamic ranges i was using the code
Formula: copy to clipboard 
=OFFSET(Sheet1!$C$4,0,0,COUNTA(Sheet1!$C$4:$C$10000),1)


Can anyone see another solution to this? thanks

heres the file

Nano Filtration for liz.xls