Automatically format charts with range of dates

[Sean Moor]

What I am trying to do is I have 4 charts that are linked with data from
another spreadsheet. They are basically week totals for my business and make
a chart compared to say last year.

What I want to do though is have an option on my report that I can enter in
a specific date or a range of dates and have the charts automatically filter
the new data in and change. Instead of having to go into the chart data and
changing what series of data I want to have a chart for I want to be able to
just enter eg. "April 12-April 18" and have the charts bring up that data to
reflect the new option.

Any suggestinos would be appreciated. I heard macros is a good idea but I
have no idea how to use them

 

[Andy Pope]

Hi,

If you filter the actual data the chart will reflect only that data. You
just need to check the setting Plot Visible Cells Only is checked.
Select chart and use Tools > Options > Chart > Plot visible cells only.

Cheers
Andy

 

[Sean Moor]

Thanks for the reply Andy but how would that allow me to enter in a set of
dates and have the chart automatically change the look of it and it will now
display the new data without having to change the links inside the chart?..

And one other thing where would I be able to enter almost like a use entry
cell that I can type in those dates and have it interact with the chart to
change via the dates I set?

Thanks

 

[Sean Moor]

and by the way if you do understand kind of what i am trying to do maybe if
you could give me step by step instructions on how to do so or a video if you
have one

thanks

 

[Andy Pope]

Details of the filtering process can be found on Debra's site
http://www.contextures.com/xlautofilter01.html

As long as your chart is based on the filtered data then the act of hiding
rows will remove that data from the chart.

Cheers
Andy

 

[Sean Moor]

Thanks for the help. Unfortunately since I dont have alot of Macro/VB
experience I am still not able to get through what I am trying to do. I just
can't figure out how to be able to have a chart existing and just be able to
enter in a set of dates and have the chart filter those dates and find the
data and boom its there instead of alays having to go in the chart and change
where the series is from the old dates to the new ones. I know quite a bti
about excel but this is far beyond my knowledge. If there is an easier or
more thorough way you know how to accomplish this I would appreciate it. I
have all my data one one spreadsheet and then my charts on another and have 4
charts I make and each chart has 3 different pieces of data. Eg One chart has
my company's cash sales/charge sales/total sales and another is our contracts
open/closed etc..

Thanks

 

[Sean Moor]

And BTW would it be easier to be creating a macro for this setup or no?.. If
so whats the best place I can go to to find out an easy macro setup?

 

[Andy Pope]

Hi Sean,

The filtering would be a manual event.
If you want to email direct an example file I will have a look.

andy @ andypope DOT info

Let me know which version of XL you are using.

Cheers
Andy

 

[Sean Moor]

Sure thing Andy I can do that tonight. What is your email??

And I am using Excel 2003 V11.8120.8200 SP2

Thanks

 

[Sean Moor]

Hi Andy,

Just wondering fi you happen to get my email and was able to possibly give
me a little more information on what I am trying to do. I have pretty much
tried everything on links you have gave me but I must be missing something.

 

[Andy Pope]

Hi,

I have sent you your revised example file, explaining how to use filtering,
3 times now.
Does this not do what you want?

Cheers
Andy

 

[Sean Moor]

Thanks for the reply. I have sent you another emailing explaining everything
I am trying to do. I may have not explained throughouly want I wanted. I
was able to do it using Dynamic Name Ranges as per another MVP on here Jon
showed me. But it is only allowing me to chart one month as my months are
separated by tabs but all on one spreadsheet so by doing it by Dynamic Name
Range it won't work.

Filtering is a great idea yes btu according to the big bosses here they
would rather have a spot where you can enter in a set of dates or have say a
dropdown menu with dates you select (start/end range) instead of a greater
than or equal to etc filter process. But filter process I might have to go
with until I figure something more out but with filtering if I set autofilter
on all my months how could I have it carry over like on my say April tab I do
greater than or equal to 28 but then want it to graph until May 4. Bcause
they are on different tabs that won't work then right?

Thanks Andy

 

[Andy Pope]

Check you spam folder as I sent you a file on the 12th. Sent it again today.

Cheers
Andy

 

[Sean Moor]

I will when I get back to work Andy today.. just incase though can you send
it to my hotmail address too.. (e-mail address removed)

thanks again for all your amazing effort