Multiple charts from one dataset with dynamic length

[Tbone]

Hello chandoovians!


I am working with a single data set which contains aquarium chemistry for several aquariums over time. The dataset is periodically updated so it grows dynamically.

My columns are

DATE AQUARIUM_NAME TEMP(C) pH NITRATE NITRITE


I want to chart each aquarium/attribute series separately. And I would like to have a single chart which I fill with data driven by a listbox selection for each of aquarium and attribute. E.G. pick "TANK A" in one list and "TEMP C" in another to chart that combination alone.


I have done similar things before using OFFSET to create a subset of the master data, which then becomes the chart source. This is easy with financial figures where you typically have regularly defined periods, and few of them (annual, monthly etc).


However, this dataset is updated irregularly. I would like to avoid building a pre-defined list of 365 entry points for the data set, because that multiplies by the nubmer of aquariums to become huge. Also, some days may get zero data updates and should be skipped.


SO! What can I do? I can see the results in my head but I can't put all of it in Excel. I need to be more excellent.

 

[Jordan]

Start here, my friend:

http://www.ozgrid.com/Excel/DynamicRanges.htm

 

[Tbone]

I understand OFFSET but Named Ranges are new to me. Thanks for the tip!

OFFSET will get me the necessary column.


BUT, how can I filter my dataset rows by aquarium? There are multiple tanks represented, so I must somehow use a selection criteria to return only rows for "Tank 1" etc?


I would like to do this with a menu selection, rather than forcing the user to apply filters. Do I need to use VBA to do this? That would be OK but seems like cheating. Is there a non VBA solution?