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}".

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}".

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}".

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}".

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}".

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}".