Hi,

I have a chart which relies on the data in a table with 29 rows.

Occaisionally, I want to only use 28 rows as my data set for the chart.

I can manually enter a named range into the dialog, and upon entry, it
automatically ,morphs it into the string that describes the data range
(worksheetname, data set). When I manually type in the other named
range, it flips to the shorter named range in the table.

How can I inject that into the chart's data dialog on the fly?

I have tried to reference cell locations that have the named range name
in them. No worky.

I know it is possible to pass this value to the chart layout, but I do
not know how.

Anyone?

This post has some VBA techniques near the end:

http://peltiertech.com/WordPress/dyn...t-source-data/

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"FatBytestard" wrote in message
...
Hi,

I have a chart which relies on the data in a table with 29 rows.

Occaisionally, I want to only use 28 rows as my data set for the chart.

I can manually enter a named range into the dialog, and upon entry, it
automatically ,morphs it into the string that describes the data range
(worksheetname, data set). When I manually type in the other named
range, it flips to the shorter named range in the table.

How can I inject that into the chart's data dialog on the fly?

I have tried to reference cell locations that have the named range name
in them. No worky.

I know it is possible to pass this value to the chart layout, but I do
not know how.

Anyone?

On Thu, 21 May 2009 23:40:15 -0400, "Jon Peltier"
wrote:

This post has some VBA techniques near the end:

http://peltiertech.com/WordPress/dyn...t-source-data/

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______

So you are saying that this is the best way to handle say leap year?

I was wanting to get along without any VB code. Oh well.

The data table for the 29th day would only receive data if there were a
day 29, a leap year. So I leave the table at 29 rows, and only select 28
rows for the chart. On leap year, I want to select 29 rows as the data
set referred to by the chart. All my other months' days keep track with
whatever year is being done, and February does too. Just not the chart.

I may have to explicitly call the sheet name, not merely the range name
I declared. I will try that, and get back to you. Your stuff is nice,
but I don't think I actually need a dynamic table to accommodate one
single additional row every fourth year. :-) The table is fine. The
chart needs to be able to be dynamically defined by accepting named
ranges as data range input. That I can switch in once every fourth year.

I mean I know I can make a separate chart and refer to that chart on
leap year and the other during the interim, but is that the most elegant
solution? Can I merely make another chart below the first February chart,
and make it a 29 day reference, and I guessI don't have to worry about
the year, since one 365th tick not filled would be barely noticeable.
Seems like a kludge for me to simply add a leap year chart on my February
Plot sheet, such that any monkey can tell which set of dual data is valid
for the year they are in.


Hmmm... divisible by 4, by 100, by 400... What's all this leap year
crud?!

If you can use a defined name for the X and Y values for each series in the
chart, you can build dynamic charts that don't use VBA. Somehow when I read
your description, I assumed you didn't want to do that. The defined names
only work for the individual X and Y values, not for the total source data
range.

For info on creating dynamic charts:
http://peltiertech.com/Excel/Charts/...umnChart1.html
http://peltiertech.com/WordPress/dynamic-charts/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"FatBytestard" wrote in message
...
On Thu, 21 May 2009 23:40:15 -0400, "Jon Peltier"
wrote:

This post has some VBA techniques near the end:

http://peltiertech.com/WordPress/dyn...t-source-data/

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______

So you are saying that this is the best way to handle say leap year?

I was wanting to get along without any VB code. Oh well.

The data table for the 29th day would only receive data if there were a
day 29, a leap year. So I leave the table at 29 rows, and only select 28
rows for the chart. On leap year, I want to select 29 rows as the data
set referred to by the chart. All my other months' days keep track with
whatever year is being done, and February does too. Just not the chart.

I may have to explicitly call the sheet name, not merely the range name
I declared. I will try that, and get back to you. Your stuff is nice,
but I don't think I actually need a dynamic table to accommodate one
single additional row every fourth year. :-) The table is fine. The
chart needs to be able to be dynamically defined by accepting named
ranges as data range input. That I can switch in once every fourth year.

I mean I know I can make a separate chart and refer to that chart on
leap year and the other during the interim, but is that the most elegant
solution? Can I merely make another chart below the first February chart,
and make it a 29 day reference, and I guessI don't have to worry about
the year, since one 365th tick not filled would be barely noticeable.
Seems like a kludge for me to simply add a leap year chart on my February
Plot sheet, such that any monkey can tell which set of dual data is valid
for the year they are in.


Hmmm... divisible by 4, by 100, by 400... What's all this leap year
crud?!

On Fri, 22 May 2009 15:52:14 -0400, "Jon Peltier"
wrote:

If you can use a defined name for the X and Y values for each series in the
chart, you can build dynamic charts that don't use VBA. Somehow when I read
your description, I assumed you didn't want to do that. The defined names
only work for the individual X and Y values, not for the total source data
range.

For info on creating dynamic charts:
http://peltiertech.com/Excel/Charts/...umnChart1.html
http://peltiertech.com/WordPress/dynamic-charts/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"FatBytestard" wrote in message
.. .
On Thu, 21 May 2009 23:40:15 -0400, "Jon Peltier"
wrote:

This post has some VBA techniques near the end:

http://peltiertech.com/WordPress/dyn...t-source-data/

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______

So you are saying that this is the best way to handle say leap year?

I was wanting to get along without any VB code. Oh well.

The data table for the 29th day would only receive data if there were a
day 29, a leap year. So I leave the table at 29 rows, and only select 28
rows for the chart. On leap year, I want to select 29 rows as the data
set referred to by the chart. All my other months' days keep track with
whatever year is being done, and February does too. Just not the chart.

I may have to explicitly call the sheet name, not merely the range name
I declared. I will try that, and get back to you. Your stuff is nice,
but I don't think I actually need a dynamic table to accommodate one
single additional row every fourth year. :-) The table is fine. The
chart needs to be able to be dynamically defined by accepting named
ranges as data range input. That I can switch in once every fourth year.

I mean I know I can make a separate chart and refer to that chart on
leap year and the other during the interim, but is that the most elegant
solution? Can I merely make another chart below the first February chart,
and make it a 29 day reference, and I guessI don't have to worry about
the year, since one 365th tick not filled would be barely noticeable.
Seems like a kludge for me to simply add a leap year chart on my February
Plot sheet, such that any monkey can tell which set of dual data is valid
for the year they are in.


Hmmm... divisible by 4, by 100, by 400... What's all this leap year
crud?!

Perhaps the best way to do this is to define two February tables and do
a simple VB leap year test and set a value based on the test results that
hides one or the other table, and that chart always looks at only the
active table. Sound good?

Or two tables AND two charts, and the VB script merely hides the two
that are not pertinent in the year selected.

I guess I don't understand what you're trying to do, and how you're going
about it.

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______


"FatBytestard" wrote in message
...
On Fri, 22 May 2009 15:52:14 -0400, "Jon Peltier"
wrote:

If you can use a defined name for the X and Y values for each series in
the
chart, you can build dynamic charts that don't use VBA. Somehow when I
read
your description, I assumed you didn't want to do that. The defined names
only work for the individual X and Y values, not for the total source data
range.

For info on creating dynamic charts:
http://peltiertech.com/Excel/Charts/...umnChart1.html
http://peltiertech.com/WordPress/dynamic-charts/

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______



"FatBytestard" wrote in message
. ..
On Thu, 21 May 2009 23:40:15 -0400, "Jon Peltier"
wrote:

This post has some VBA techniques near the end:

http://peltiertech.com/WordPress/dyn...t-source-data/

- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/

Advanced Excel Conference - June 17-18 2009 - Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______

So you are saying that this is the best way to handle say leap year?

I was wanting to get along without any VB code. Oh well.

The data table for the 29th day would only receive data if there were a
day 29, a leap year. So I leave the table at 29 rows, and only select 28
rows for the chart. On leap year, I want to select 29 rows as the data
set referred to by the chart. All my other months' days keep track with
whatever year is being done, and February does too. Just not the chart.

I may have to explicitly call the sheet name, not merely the range name
I declared. I will try that, and get back to you. Your stuff is nice,
but I don't think I actually need a dynamic table to accommodate one
single additional row every fourth year. :-) The table is fine. The
chart needs to be able to be dynamically defined by accepting named
ranges as data range input. That I can switch in once every fourth
year.

I mean I know I can make a separate chart and refer to that chart on
leap year and the other during the interim, but is that the most elegant
solution? Can I merely make another chart below the first February
chart,
and make it a 29 day reference, and I guessI don't have to worry about
the year, since one 365th tick not filled would be barely noticeable.
Seems like a kludge for me to simply add a leap year chart on my
February
Plot sheet, such that any monkey can tell which set of dual data is
valid
for the year they are in.


Hmmm... divisible by 4, by 100, by 400... What's all this leap year
crud?!

Perhaps the best way to do this is to define two February tables and do
a simple VB leap year test and set a value based on the test results that
hides one or the other table, and that chart always looks at only the
active table. Sound good?

Or two tables AND two charts, and the VB script merely hides the two
that are not pertinent in the year selected.