Actually, I've found an easier workaround:
1. Copy the worksheet to a new workbook.
2. Rename the copy of the sheet (in the new workbook).
3. Move this sheet back into the original workbook.
- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______
"Lothos" wrote in message
...
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.
"Jon Peltier" wrote:
Andy -
SP1 does not fix the bug you describe. I've reported it as a bug to
Microsoft.
- Jon
-------
Jon Peltier, Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
Advanced Excel Conference - Training in Charting and Programming
http://peltiertech.com/Training/2009...00906ACNJ.html
_______
"Andy Pope" wrote in message
...
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
--
Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"badpazzword" wrote in message
...
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}".