strange problems with dynamic charts

[Rebekah]

I have a relatively involved workbook that consists of
three worksheets with data on them and 14 additional
sheets of charts. Using the method described by Microsoft
in their knowledge base article 183446 and also described
by several MVP's, I am attempting to make the charts
dynamic. I create Names for each series on the chart and
add the series with an offset and COUNTA function then go
into the chart and set the series to the name. Here is an
example of the Name formula that I'm using:
='SVE - Mass Removal'!$E$28:$F$28,OFFSET('SVE - Mass
Removal'!$I$28,0,0,1,COUNTA('SVE - Mass Removal'!28:28)-5)

This seems to work fine at first.

The first problem I'm having is that after I have a few
names in the sheet it seems to keep changing the range in
the COUNTA function on the existing names. I can't figure
out why this would be happening but it seems to occur if
you edit or view an existing name or create a new one.

The other problem is that the charts keep redrawing
themselves, sometimes correctly, sometimes incorrectly
even though I havent changed any of the data. I have
several macros in this workbook but they shouldnt be
affecting either of these problems. They are used to
enter new data in the workbook. I am not opposed to
writing some additional macros to keep the charts drawn
correctly if this is possible.

Has anyone had any problems like this? What have you done
to fix them?

Thank you in advance,
Rebekah

 

[Rebekah]

I figured out the answer to the first problem. Here it is
in case anyone else makes this same mistake. The range in
the COUNTA part of the statement didn't have $ in front of
the row numbers. This caused Excel to view it as
a "relative" range rather than an "absolute" range.

If anyone knows how to keep the charts updated to fix my
second problem, please let me know.

 

[Tushar Mehta]

Since you solved the first problem yourself, this is only about the
2nd.

First of all, sorry, but your name
='SVE - Mass Removal'!$E$28:$F$28,OFFSET('SVE - Mass
Removal'!$I$28,0,0,1,COUNTA('SVE - Mass Removal'!28:28)-5)
makes no sense.

Second, for one of the charts, copy the source data and paste the
values (use Edit | Paste Special... | Values option) into an unused
range in the worksheet. Now, run the macros that you have associated
with the workbook. Finally, compare the saved values with the current
values.

If the problem is still unresolved, for one of the charts that changes
by itself can you post the exact names used and the series formulas?
For the former, select an empty area of the worksheet and then use
Insert | Name > Paste... | click the Paste List button. Now, copy the
relevant names+formulas and paste them into your newsgroup message.
For the latter, click on the plotted series in the chart, then copy the
formula in the formula bar.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions