I'm trying to create a worksheet that can be copied with an embedded
dynamic chart.

I created the named formulas at the worksheet level and then entered
the names in the worksheet formula dialog box and the chart works
fine.

The part that doesn't work is that when I copy the worksheet the named
formulas update to the new name of the copied sheet but the chart
formula doesn't update properly.

I've tried inserting indirect references into the chart formula and
that didn't work.

Is there a way to get the chart formula to update on the copied
worksheet?

Hi,

this is probably going to be a problem. First though you should show us the
defined names you are using and what the Series function is displaying and
what you want it to display after you copy the sheet.

Also, what version of Excel are you using and what is the function of the
dynamic range names - why are you using them?

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

I'm trying to create a worksheet that can be copied with an embedded
dynamic chart.

I created the named formulas at the worksheet level and then entered
the names in the worksheet formula dialog box and the chart works
fine.

The part that doesn't work is that when I copy the worksheet the named
formulas update to the new name of the copied sheet but the chart
formula doesn't update properly.

I've tried inserting indirect references into the chart formula and
that didn't work.

Is there a way to get the chart formula to update on the copied
worksheet?

There are 2 defined names:

x9a with the formula =OFFSET('9 (4)'!$A$5,0,0,COUNT('9 (4)'!$A:$A),1)

y9a with the formula =OFFSET('9 (4)'!x9a,0,1)

This is in Excel 2007.

These is a 2 column data set that displays a column of Month-Year
dates, and the second column contains a monthly count like 4,555.
These two columns vary in length from 1 to 12 months depending on
values in a date selector drop down box.

The chart is a column chart with the Month-Year dates along the x axis
and the values above them.

These are presentation pages with a table of data at the top and a
chart or two charts at the bottom under the table of data.

Thanks and yes, your response is helpful but I don't see the yes
button you are referring to in your message.

Hi,

Don't worry about the Yes button, it only appears if you are using the Web
interface.

I think I am understanding your problem a little better - when you copy the
sheet you want the associated chart to use a new dynamic range name, one that
refers to the new sheet rather than the old one? The data range would still
be detected based on the offset function but within the new sheet?

Here is the problem, you want one range name to refer to multiple ranges on
different sheets which is not possible.

Try this solution instead assuming the data starts in A1 with titles on the
first row:

put the cursor in the data and choose Home, Format as Table. Pick any table
style and click OK to the next dialog box. This chart is dynamic without the
use of a range name. If you add more data to the table the chart will
increase. If you delete a row from the table the chart will adjust.

If you try to copy the entire sheet Excel will crash, so select the data and
the chart an copy it to a new blank sheet. The chart will be refering to the
original sheet. On the second sheet select the chart and choose Chart Tools,
Design, Select Data which will select Sheet1, switch back to the new sheet
and highlight the range.

All charts based on table ranges are dynamic. And if you don't like the
table formatting you can just change it to none - the first choice on the
Table Tools, Design, Table Styles gallery.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


" wrote:

There are 2 defined names:

x9a with the formula =OFFSET('9 (4)'!$A$5,0,0,COUNT('9 (4)'!$A:$A),1)

y9a with the formula =OFFSET('9 (4)'!x9a,0,1)

This is in Excel 2007.

These is a 2 column data set that displays a column of Month-Year
dates, and the second column contains a monthly count like 4,555.
These two columns vary in length from 1 to 12 months depending on
values in a date selector drop down box.

The chart is a column chart with the Month-Year dates along the x axis
and the values above them.

These are presentation pages with a table of data at the top and a
chart or two charts at the bottom under the table of data.

Thanks and yes, your response is helpful but I don't see the yes
button you are referring to in your message.

Tables will not work since my base data expands and contracts with a
date range selector used.

It was mentioned - "you want one range name to refer to multiple
ranges on
different sheets which is not possible." - which is true, but really
what is needed is a range that would update itself when the sheet is
copied.

One idea was to have a cell read the sheet name and concatenate that
result into what would be needed for the formula name manager. I
haven't been able to come up with the combination to make this idea
work.




On Feb 22, 12:20*pm, Shane Devenshire
wrote:
Hi,

Don't worry about the Yes button, it only appears if you are using the Web
interface.

I think I am understanding your problem a little better - when you copy the
sheet you want the associated chart to use a new dynamic range name, one that
refers to the new sheet rather than the old one? *The data range would still
be detected based on the offset function but within the new sheet?

Here is the problem, you want one range name to refer to multiple ranges on
different sheets which is not possible. *

Try this solution instead assuming the data starts in A1 with titles on the
first row:

put the cursor in the data and choose Home, Format as Table. *Pick any table
style and click OK to the next dialog box. *This chart is dynamic without the
use of a range name. *If you add more data to the table the chart will
increase. *If you delete a row from the table the chart will adjust.

If you try to copy the entire sheet Excel will crash, so select the data and
the chart an copy it to a new blank sheet. *The chart will be refering to the
original sheet. *On the second sheet select the chart and choose Chart Tools,
Design, Select Data which will select Sheet1, switch back to the new sheet
and highlight the range.

All charts based on table ranges are dynamic. *And if you don't like the
table formatting you can just change it to none - the first choice on the
Table Tools, Design, Table Styles gallery.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

" wrote:
There are 2 defined names:

x9a with the formula =OFFSET('9 (4)'!$A$5,0,0,COUNT('9 (4)'!$A:$A),1)

y9a with the formula =OFFSET('9 (4)'!x9a,0,1)

This is in Excel 2007.

These is a 2 column data set that displays a column of Month-Year
dates, and the second column contains a monthly count like 4,555.
These two columns vary in length from 1 to 12 months depending on
values in a date selector drop down box.

The chart is a column chart with the Month-Year dates along the x axis
and the values above them.

These are presentation pages with a table of data at the top and a
chart or two charts at the bottom under the table of data.

Thanks and yes, your response is helpful but I don't see the yes
button you are referring to in your message.