data filter criteria based on cell references?

[Guest]

I have a large series of data where the criteria for the data filter updates
a linked chart. The data looks as follows:

Time Stamp Data1 Data2 Data3 Data4
6/21/07 10:05AM 123 124 125 126
6/21/07 10:10AM 223 224 225 226
6/21/07 10:15AM 323 324 325 326
etc.

I use the data filter to select the criteria that is greater than or equal
to first specified date & time and is less than or equal to second specified
date & time. Because the chart is on a different sheet and there are other
input fields that are also required for the chart, I’m trying to automate as
much of the charting as possible.

I have two cells where I would input the start and end dates. How do you
create a macro that will take the start date as the first criteria in the
data filter and the end date as the second criteria? The start and end dates
will vary. When I’ve tried to create the macro, it will take the input start
date as the actual date, meaning, if I change the start date, I will still
get the original start date. Is there any way to use whatever I type in for
the start and end dates for the data filter criteria?

On a similar topic, since the chart updates to the parameters of the data
filter, the y-axis scale becomes skewed. I’ve created a formula to calculate
the min and max for the scale. Again the macro I’ve tried to create only
recognizes the actual value in the cell rather than the formula output. How
would I create a macro to recognize the result of the formula?

Thank you in advance.

 

[Guest]

Hi,

For the first part (getting the autofilter to update whenever you change the
date) you can do one of several things. 1 add a button that you press to
update the filters each time you change the date. (the VB for changing the
macro can be found from the macro recorder, then just modify the selection
criteria a bit) use a custom filter -> between x + y. Or you could also have
it run on a worksheet change event (ie when you change the date, the macro
will run and do the rest). There's probably other ways as well, but those
are two starts.

As for the graph problem, either have a button and change event like above
so each time the thing is changed it will be updated. I don't think you can
put a formula in the min/max values of a chart axis (but correct me if I'm
wrong)