Copy tab in Calc with charts and have the charts link to data in the new tab

zizajaun · May 9, 2022, 7:10pm

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?

LeroyG · May 10, 2022, 2:28pm

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

Villeroy · May 9, 2022, 7:56pm

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.

zizajaun · May 9, 2022, 9:03pm

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

Villeroy · May 10, 2022, 11:08am

t77342_scenarios.ods (50.1 KB)

To make this complete:

Windows>New, select the second sheet in the second window and place the windows side by side.
On the scenario sheet, turn on the navigator in the sidebar and turn on scenario view in the navigator.
Select the “NEW BLANK” scenario (dbl-click in navigator)
Paste the next column and copy the name from the header cell
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.

LeroyG · May 10, 2022, 12:18pm

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.

zizajaun · May 10, 2022, 12:34pm

@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.

LeroyG · May 10, 2022, 2:21pm

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.

erAck · May 10, 2022, 2:47pm

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.

LeroyG · May 10, 2022, 7:53pm

@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.

zizajaun · May 11, 2022, 4:58pm

“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.

zizajaun · May 12, 2022, 12:35am

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.