Can charts automatically change by entering new range of dates

I am creating a Dashboard report for my Accounting department at work and I
have a weekly one I set up with links for the series of a chart to another
worksheet. Easy. But what I am trying to do is instead of creating a new
one each week by changing the link location for the data is there a way I can
just enter in a range of dates and the charts automatically change and filer
in the new data without me having to change the link in the series where the
data entry is coming from??

You can define a dynamic range that adjusts to dates you enter into cells. I
wrote a somewhat involved tutorial which you may be able to adapt:

http://pubs.logicalexpressions.com/P...cle.asp?ID=246

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Sean Moor" wrote in message
...
I am creating a Dashboard report for my Accounting department at work and I
have a weekly one I set up with links for the series of a chart to another
worksheet. Easy. But what I am trying to do is instead of creating a new
one each week by changing the link location for the data is there a way I
can
just enter in a range of dates and the charts automatically change and
filer
in the new data without me having to change the link in the series where
the
data entry is coming from??

Thanks for the reply Jon. Unfortunately I was not able to understand that
tutorial and I don't know much easier you can explain it to me. Really just
wanna have my charts created basically by entering in a range of dates
anywhere in the spreadsheet and have the chart automatically read that and
know where the range of data is and be able to be produced automatically
instead of having to change the series inside the chart data everytime I want
to see a new chart.

Sean

"Jon Peltier" wrote:

You can define a dynamic range that adjusts to dates you enter into cells. I
wrote a somewhat involved tutorial which you may be able to adapt:

http://pubs.logicalexpressions.com/P...cle.asp?ID=246

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Sean Moor" wrote in message
...
I am creating a Dashboard report for my Accounting department at work and I
have a weekly one I set up with links for the series of a chart to another
worksheet. Easy. But what I am trying to do is instead of creating a new
one each week by changing the link location for the data is there a way I
can
just enter in a range of dates and the charts automatically change and
filer
in the new data without me having to change the link in the series where
the
data entry is coming from??

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?

"Sean Moor" wrote:

I am creating a Dashboard report for my Accounting department at work and I
have a weekly one I set up with links for the series of a chart to another
worksheet. Easy. But what I am trying to do is instead of creating a new
one each week by changing the link location for the data is there a way I can
just enter in a range of dates and the charts automatically change and filer
in the new data without me having to change the link in the series where the
data entry is coming from??

Excel isn't magic, it needs a mechanism to know that the two dates you enter
refer somehow to the total data range, and to apply the subset of the data
range to the chart. The tutorial I cited provides that mechanism.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Sean Moor" wrote in message
news
Thanks for the reply Jon. Unfortunately I was not able to understand that
tutorial and I don't know much easier you can explain it to me. Really
just
wanna have my charts created basically by entering in a range of dates
anywhere in the spreadsheet and have the chart automatically read that and
know where the range of data is and be able to be produced automatically
instead of having to change the series inside the chart data everytime I
want
to see a new chart.

Sean

"Jon Peltier" wrote:

You can define a dynamic range that adjusts to dates you enter into
cells. I
wrote a somewhat involved tutorial which you may be able to adapt:

http://pubs.logicalexpressions.com/P...cle.asp?ID=246

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Sean Moor" wrote in message
...
I am creating a Dashboard report for my Accounting department at work
and I
have a weekly one I set up with links for the series of a chart to
another
worksheet. Easy. But what I am trying to do is instead of creating a
new
one each week by changing the link location for the data is there a way
I
can
just enter in a range of dates and the charts automatically change and
filer
in the new data without me having to change the link in the series
where
the
data entry is coming from??

I don't think a macro would be any easier than setting up a worksheet- or
formula-based solution, especially if you're not familiar with VBA. There
are no "easy macro setups" if you don't know how to set up the problem. VBA
isn't magic, it only does what Excel can do, but allows you to do it
automatically.

I suggest you refer to the tutorial I cited, perhaps after reading about
dynamic charts and defined names.

Dynamic Charts:
http://peltiertech.com/Excel/Charts/Dynamics.html
http://peltiertech.com/Excel/Charts/...umnChart1.html
http://peltiertech.com/Excel/Charts/DynamicLast12.html
http://peltiertech.com/Excel/Charts/...hartLinks.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"Sean Moor" wrote in message
...
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?

"Sean Moor" wrote:

I am creating a Dashboard report for my Accounting department at work and
I
have a weekly one I set up with links for the series of a chart to
another
worksheet. Easy. But what I am trying to do is instead of creating a
new
one each week by changing the link location for the data is there a way I
can
just enter in a range of dates and the charts automatically change and
filer
in the new data without me having to change the link in the series where
the
data entry is coming from??

Hey Jon,

Wow thanks for those links. The one I found that will work the best is the
Interactive Chart Creation as anyone can pick where they want the chart to go
and what data boom done.

Few questions though. It does come up as a built-in chart style which I
would prefer either a column or pie chart instead is there a way in the
macro/vbe to change so instead of the built-in one thats in there to make a
different kind of chart instead so I don't have to change the chart type
after its created.

As well for my weekly report I do end up having around 4 different charts
plotting 2 stats on each. Is there a way with this interactive setup to
generate 4 different charts at one time like that instead of doing one and
then just following the same set up and doing it again to 3 more?

Thanks again

"Jon Peltier" wrote:

I don't think a macro would be any easier than setting up a worksheet- or
formula-based solution, especially if you're not familiar with VBA. There
are no "easy macro setups" if you don't know how to set up the problem. VBA
isn't magic, it only does what Excel can do, but allows you to do it
automatically.

I suggest you refer to the tutorial I cited, perhaps after reading about
dynamic charts and defined names.

Dynamic Charts:
http://peltiertech.com/Excel/Charts/Dynamics.html
http://peltiertech.com/Excel/Charts/...umnChart1.html
http://peltiertech.com/Excel/Charts/DynamicLast12.html
http://peltiertech.com/Excel/Charts/...hartLinks.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"Sean Moor" wrote in message
...
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?

"Sean Moor" wrote:

I am creating a Dashboard report for my Accounting department at work and
I
have a weekly one I set up with links for the series of a chart to
another
worksheet. Easy. But what I am trying to do is instead of creating a
new
one each week by changing the link location for the data is there a way I
can
just enter in a range of dates and the charts automatically change and
filer
in the new data without me having to change the link in the series where
the
data entry is coming from??

The procedure as posted is pretty simple. It can certainly be made as
complicated as you'd like, changing the chart type (I think it uses the
default, so you could change your default to make different types), even
making multiple charts from a single selected range. Without knowing
details, it's not easy to make specific recommendations, and of course, this
starts to become a development project once it starts to grow custom
features.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Sean Moor" wrote in message
...
Hey Jon,

Wow thanks for those links. The one I found that will work the best is
the
Interactive Chart Creation as anyone can pick where they want the chart to
go
and what data boom done.

Few questions though. It does come up as a built-in chart style which I
would prefer either a column or pie chart instead is there a way in the
macro/vbe to change so instead of the built-in one thats in there to make
a
different kind of chart instead so I don't have to change the chart type
after its created.

As well for my weekly report I do end up having around 4 different charts
plotting 2 stats on each. Is there a way with this interactive setup to
generate 4 different charts at one time like that instead of doing one and
then just following the same set up and doing it again to 3 more?

Thanks again

"Jon Peltier" wrote:

I don't think a macro would be any easier than setting up a worksheet- or
formula-based solution, especially if you're not familiar with VBA. There
are no "easy macro setups" if you don't know how to set up the problem.
VBA
isn't magic, it only does what Excel can do, but allows you to do it
automatically.

I suggest you refer to the tutorial I cited, perhaps after reading about
dynamic charts and defined names.

Dynamic Charts:
http://peltiertech.com/Excel/Charts/Dynamics.html
http://peltiertech.com/Excel/Charts/...umnChart1.html
http://peltiertech.com/Excel/Charts/DynamicLast12.html
http://peltiertech.com/Excel/Charts/...hartLinks.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"Sean Moor" wrote in message
...
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?

"Sean Moor" wrote:

I am creating a Dashboard report for my Accounting department at work
and
I
have a weekly one I set up with links for the series of a chart to
another
worksheet. Easy. But what I am trying to do is instead of creating a
new
one each week by changing the link location for the data is there a
way I
can
just enter in a range of dates and the charts automatically change and
filer
in the new data without me having to change the link in the series
where
the
data entry is coming from??

Sounds great ya I was trying out a few things at work and I can easily change
the chart type by changing the default and the basic outlook of the chart
just in the VBE, going to be nice to learn all of it.

Only thing is the options I like with the popup asking where the chart to be
put but when it asks for where the data is from I want to be able to type say
May 12 and have 4 charts come up with the data I have in my system from May
12. Like for example one chart would be my cash/charge sales, one would be
my accounts opened/closed, one would be $/contract compared to last year and
finally my Month to Date sales compared to last years. Those are the 4
charts I want to create based on just entering in a date. I am thinking once
I get it going I'll probably have to in the code write alot of IF='s right?
like basically if=May 12 then it knows where to find the data and then create
4 charts out of it?

Thanks

"Jon Peltier" wrote:

The procedure as posted is pretty simple. It can certainly be made as
complicated as you'd like, changing the chart type (I think it uses the
default, so you could change your default to make different types), even
making multiple charts from a single selected range. Without knowing
details, it's not easy to make specific recommendations, and of course, this
starts to become a development project once it starts to grow custom
features.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Sean Moor" wrote in message
...
Hey Jon,

Wow thanks for those links. The one I found that will work the best is
the
Interactive Chart Creation as anyone can pick where they want the chart to
go
and what data boom done.

Few questions though. It does come up as a built-in chart style which I
would prefer either a column or pie chart instead is there a way in the
macro/vbe to change so instead of the built-in one thats in there to make
a
different kind of chart instead so I don't have to change the chart type
after its created.

As well for my weekly report I do end up having around 4 different charts
plotting 2 stats on each. Is there a way with this interactive setup to
generate 4 different charts at one time like that instead of doing one and
then just following the same set up and doing it again to 3 more?

Thanks again

"Jon Peltier" wrote:

I don't think a macro would be any easier than setting up a worksheet- or
formula-based solution, especially if you're not familiar with VBA. There
are no "easy macro setups" if you don't know how to set up the problem.
VBA
isn't magic, it only does what Excel can do, but allows you to do it
automatically.

I suggest you refer to the tutorial I cited, perhaps after reading about
dynamic charts and defined names.

Dynamic Charts:
http://peltiertech.com/Excel/Charts/Dynamics.html
http://peltiertech.com/Excel/Charts/...umnChart1.html
http://peltiertech.com/Excel/Charts/DynamicLast12.html
http://peltiertech.com/Excel/Charts/...hartLinks.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"Sean Moor" wrote in message
...
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?

"Sean Moor" wrote:

I am creating a Dashboard report for my Accounting department at work
and
I
have a weekly one I set up with links for the series of a chart to
another
worksheet. Easy. But what I am trying to do is instead of creating a
new
one each week by changing the link location for the data is there a
way I
can
just enter in a range of dates and the charts automatically change and
filer
in the new data without me having to change the link in the series
where
the
data entry is coming from??

Here's that development project I alluded to. You need to change the
interface, replacing the simple inputbox with a userform to list the
available dates. Then the code has to somehow examine the data and parse out
just what you need.

Or... You could set up a filter on your data, and use it to display the date
you want, and hide the rest. By default, data in hidden cells do not appear
in the chart.

That's if the dates are in a single column, with the data lined up in
sequential rows. If you have different columns for each date's data, use
something like this:

http://peltiertech.com/Excel/Charts/ChartByControl.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Sean Moor" wrote in message
...
Sounds great ya I was trying out a few things at work and I can easily
change
the chart type by changing the default and the basic outlook of the chart
just in the VBE, going to be nice to learn all of it.

Only thing is the options I like with the popup asking where the chart to
be
put but when it asks for where the data is from I want to be able to type
say
May 12 and have 4 charts come up with the data I have in my system from
May
12. Like for example one chart would be my cash/charge sales, one would
be
my accounts opened/closed, one would be $/contract compared to last year
and
finally my Month to Date sales compared to last years. Those are the 4
charts I want to create based on just entering in a date. I am thinking
once
I get it going I'll probably have to in the code write alot of IF='s
right?
like basically if=May 12 then it knows where to find the data and then
create
4 charts out of it?

Thanks

"Jon Peltier" wrote:

The procedure as posted is pretty simple. It can certainly be made as
complicated as you'd like, changing the chart type (I think it uses the
default, so you could change your default to make different types), even
making multiple charts from a single selected range. Without knowing
details, it's not easy to make specific recommendations, and of course,
this
starts to become a development project once it starts to grow custom
features.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Sean Moor" wrote in message
...
Hey Jon,

Wow thanks for those links. The one I found that will work the best is
the
Interactive Chart Creation as anyone can pick where they want the chart
to
go
and what data boom done.

Few questions though. It does come up as a built-in chart style which
I
would prefer either a column or pie chart instead is there a way in the
macro/vbe to change so instead of the built-in one thats in there to
make
a
different kind of chart instead so I don't have to change the chart
type
after its created.

As well for my weekly report I do end up having around 4 different
charts
plotting 2 stats on each. Is there a way with this interactive setup
to
generate 4 different charts at one time like that instead of doing one
and
then just following the same set up and doing it again to 3 more?

Thanks again

"Jon Peltier" wrote:

I don't think a macro would be any easier than setting up a worksheet-
or
formula-based solution, especially if you're not familiar with VBA.
There
are no "easy macro setups" if you don't know how to set up the
problem.
VBA
isn't magic, it only does what Excel can do, but allows you to do it
automatically.

I suggest you refer to the tutorial I cited, perhaps after reading
about
dynamic charts and defined names.

Dynamic Charts:
http://peltiertech.com/Excel/Charts/Dynamics.html
http://peltiertech.com/Excel/Charts/...umnChart1.html
http://peltiertech.com/Excel/Charts/DynamicLast12.html
http://peltiertech.com/Excel/Charts/...hartLinks.html

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"Sean Moor" wrote in message
...
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?

"Sean Moor" wrote:

I am creating a Dashboard report for my Accounting department at
work
and
I
have a weekly one I set up with links for the series of a chart to
another
worksheet. Easy. But what I am trying to do is instead of
creating a
new
one each week by changing the link location for the data is there a
way I
can
just enter in a range of dates and the charts automatically change
and
filer
in the new data without me having to change the link in the series
where
the
data entry is coming from??