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
"badpazzword" wrote:
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}".