#1

I have an Old Tab in Calc with about 20 charts linked to data in that tab. I want to copy the tab, replace the data in New Tab, and have the charts in New Tab refer to data in the New Tab automatically.

But by default the charts in New Tab refer to the data in Old Tab. How do I change this?

#8

Was this spreadsheet ever saved as .doc(x) or within Excel?

#2

trigonometricScenarios.ods (108.9 KB)
One visible sheet, one chart, multiple data sets on scenario sheets.
Instead of navigating through a bloated document, you simply click the drop-down.

#3

Thanks @Villeroy. I’m not sure I completely understand it with a quick look. Would it work with this doc?

#4

t77342_scenarios.ods (50.1 KB)

To make this complete:

  1. Windows>New, select the second sheet in the second window and place the windows side by side.
  2. On the scenario sheet, turn on the navigator in the sidebar and turn on scenario view in the navigator.
  3. Select the “NEW BLANK” scenario (dbl-click in navigator)
  4. Paste the next column and copy the name from the header cell
  5. Tools>Scenario… Paste the name.

Repeat 3. to 5. for subsequent columns on the second sheet.

All scenarios get new rows when you insert new day rows.

#5

EDIT: Sorry that I don’t see the sample file before writing my answer. See @erAck comment below.

That happens if do you copy only the chart, without the data range.

You can select the cell range that includes the data range and the chart, then copy and paste.
imagen

Also, if do you copy all the sheet (Menu Sheet - Move or Copy Sheet), or select all (Ctrl+A) then copy and paste in a new sheet, there is no problem.

#6

@LeroyG - thanks, but that’s not true, at least not in my version (7.3.2.2). Both copying the sheet and copy-pasting the data and charts to a new sheet leave the new charts linked to the old data.

#7

Create the new sheet, then copy and paste the data range with the chart.

When pasting, if the new sheet is to the left of the original sheet, the chart data will remain linked to the original one. So, you can paste then move the sheet to the left.

I am almost sure that there is a bug report about that.

I can’t reproduce the problem if copying the whole sheet as described in my answer.

Tested with 7.1.8.1 and 7.3.2.2 on Windows 7.

1 Like
Chart on a separate sheet.
#9

In the linked sample document on both sheets, even on OLD SHEET, the charts contain internal data tables instead of references to the Calc sheet. Of course there’s nothing to be adapted then when copying the sheet.

1 Like
#10

image

@zizajaun
If do you choose Data Ranges..., you can set the new data range for the upper leftmost chart to B10:C67; B10:D67 to the chart next to the right, and so, and choose the appropiate data series.

1 Like
#11

“Create the new sheet, then copy and paste the data range with the chart.”

This worked, thanks! It also works if I save a new copy of the document then copy the relevant sheet in the new doc back to the original document.

#12

Update: it works so long as you don’t change the data. When I paste in new data, it suddenly either links back to the original sheet or changes to “internal data tables”. So now I am saving a copy of the document, changing the data, then copying the tab to the original document (by right-clicking on the tab > Move/Copy Sheet). Sometimes it fails for unclear reasons and I need to save another copy and start again, but I’ll get there eventually.