Custom error bars bug -- copy pasting chart does not update refere

B

badpazzword

What happens with Excel 2007:

1. Get two columns/rows of data
2. Create a chart out of the first column/row
3. Create custom error bars out of the second column/row (I go to Graph
Tools > Layout > Error bars > More options > Custom; I select the second
column both for positive and negative error for the sake of simplicity)
4. Duplicate the current sheet (I hold ctrl, drag the sheet label on a new
position and release both)
5. Switch to the new sheet
6. Change the graphed data. Notice the new chart updates correctly
7. Change the error bar data. Notice nothing happens.
8. Change the error bar data on the old worksheet. Notice both charts update
with the new value.
9. Reach the custom error bars dialog for the new graph.
10. Notice both fields incorrectly display ={1}

What should happen:
* Custom error data references are correctly updated on duplication
* The Custom error dialog correctly shows the worksheet reference

What happens instead:
* Custom error data references stick to the old reference
* The Custom error dialog incorrectly shows "={1}".
 
T

Tushar Mehta

You don't have to copy the sheet to discover the problem. In the original
worksheet, create a chart and add the custom error bars specifying a
worksheet range. Close all dialog boxes and go back to the error bar dialog
box. You will find the range is missing from the custom error bar dialog box.

I imagine there may be a way around this but I don't know of any. I played
a bit with VBA to see if the information was available through the object
model but there apparently is no property that returns the custom error bar
specifications.

FWIW, I find the Excel 2007 error bar handling to be very poor.
Unfortunately, there doesn't appear to be anything one can do about it.
Maybe, if enough customers with clout complain Microsoft may do something in
a future release of Excel...

--
Tushar Mehta
http://www.tushar-mehta.com
Custom business solutions leveraging a multi-disciplinary approach
In Excel 2007 double-click to format may not work; right click and select
from the menu
 
A

Andy Pope

Hi,

I could not replicate this problem exactly. For me range references were
retained when copying the sheet but there where not updated to reference the
recently copied sheet.
That is the reference for custom error bars on Sheet1 (2) was still
=Sheet1!$C$2:$C$6

Do you have SP1 installed?

Cheers
Andy
 
L

Lothos

I have been having the same problem. When you make a copy of a worksheet
within a single workbook the custom error bars do not update and still point
to the original worksheet. I figured out a work around. It's rather tedious,
but if you have a lot of charts in the worksheet with custom error bars, it
is faster than individually selecting the error bar values for each chart.
Here's what to do:

1. Make a copy of the entire workbook.
2. Delete the worksheet that has the custom error bars from the copied
workbook.
3. From the original workbook, copy the worksheet with the custom error bars
to the new workbook (right click on the worksheet tab, click "Move or copy
....", choose the new workbook, check "Make a copy"). All of the cells in the
copied worksheet should reference the original workbook, but the error bars
only specify the worksheet, and not the workbook.
4. Rename the worksheet in the new work book. The error bars will update
with the new name (they only fail to update when you copy the worksheet).
5. Move (or copy) the renamed worksheet from the new workbook to the old
workbook. The cells in the worksheet will still reference the original
worksheet. Again, since the error bars did not update when the worksheet was
moved or copied, they will retain the name of the renamed worksheet without
specifying workbook.

You should now have a second worksheet in the original workbook that is
identical to the original worksheet, except that the name is changed,
including references in all cells, charts, and error bars.

Hope this one works for you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Addition of error bars creates black filled in areas on chart in 2 2
Dynamic Error Bars 1
error bars on bar chart in Excel 2007 1
Custom error bars excel 2007 2
error bars display 1
Custom Error Bars in Excel 2007 2
Cannot Add Error Bars for individual data points! 1
Excel 2007 - Custom error bars ?? 5

Top