Page 1 of 1

[Solved] Are there limitations on chart locations?

PostPosted: Wed Apr 17, 2019 1:14 am
by IsabelDalhousie
I have a complex spreadsheet with multiple sheets with financial trends.

To simplify visually, I want to create a new sheet "charts" with all the charts which point to all the other data sheets for updates.

Here's the way I'm going about it. I create each chart in the original source sheet, then copy it to the new location "charts"

Problem is I can't seem to get it to pick up the updated data sheet once i copy and move it.

Is this just a fundamental limitation or am I doing something wrong?

Any assistance and guidance would be greatly appreciated ... thanks in advance

Re: Are there limitations on chart locations?

PostPosted: Wed Apr 17, 2019 3:40 am
by robleyd
You might find this topic helpful ?

Re: Are there limitations on chart locations?

PostPosted: Wed Apr 17, 2019 1:25 pm
by Lupp
In recent versions of LibreOffice Calc-charts are Copy/Paste_d the way you expect, as long as you paste into the same spreadsheet document as you copied from.

If you paste the chart into a different document, the connection to the source gets broken and the data are moved to a DataTable internal to the chart-shape. That's unavoidable. (Concerning the creation of charts in Writer documents with a link to external data you may study https://wiki.documentfoundation.org/Faq/Writer/140. I personally never did this.)

However, again with recent versions of LibreOffice, you can even paste a chart containing its DataTable from elsewhere into a spreadsheet, and you will find the DataRanges property there. Trying to edit it you will then get offered the choice to replace the DataTable using the data from newly assigned CellRange(s). That's a real enhancement, and I doubt if AOO will ever implement a similar feature.

Technical background: The visible "chart" object is a complicated shape object. Only spreadsheet documents also support an additional kind of objects (now internally actually named "Chart"), one collection per sheet, which are in charge of connecting the chart-shapes to DataRanges (one by one).

(I never was in British Arizona...)

Re: Are there limitations on chart locations?

PostPosted: Wed Apr 17, 2019 7:15 pm
by MrProgrammer
IsabelDalhousie wrote:Problem is I can't seem to get it to pick up the updated data sheet once i copy and move it.
You've created a Data Table chart instead of a Data Ranges chart. The former holds in the chart a copy of the data (View → Chart Data Table) which is disconnected from the source data. The latter has references to the source data (Format → Data Ranges).

IsabelDalhousie wrote:To simplify visually, I want to create a new sheet "charts" with all the charts which point to all the other data sheets for updates.Here's the way I'm going about it. I create each chart in the original source sheet, then copy it to the new location "charts"
Instead of copying the chart you want to move it. Create the chart on the original source sheet. If the chart is open for editing (Insert menu will show Titles Legent Axes …) press ESC to terminate edit mode (Insert menu will show ManualBreak Cells Rows Columns …). Single-click the chart and hold the click until a small black square appears at the cursor. As you're holding the mouse button, press and hold the Mac's ⌃ key (probably labelled "control"). Drag the square to the sheet tabs area. Drag the square onto "Charts" in the sheet tabs to select that sheet. Drag the square onto the Charts sheet. Release the ⌃ key. Only now release mouse button to drop the chart there. Position the chart as desired. (Holding the ⌃ key as you move the square to the sheet tabs area prevents Calc from scrolling the sheet as you approach the last visible row.)

Double-click the chart and the Format menu should show Data Ranges. Data Ranges should show the names of the cells on your data sheet. If Data Ranges is disabled, undo the move (press ⌘Z twice). Then move the chart again but hold ⌘ when you release the drag, a procedure which I believe some versions of OpenOffice required.

For people using Linux or Windoze, I do not know what the equivalent is to holding the ⌃ key on a Mac.

Lupp wrote:That's a real enhancement, and I doubt if AOO will ever implement a similar feature.
In OpenOffice one can drag source cells onto a Data Table chart to re-establish the connection to the Data Ranges. Moving the chart with Cut/Paste, instead of the click/drag procedure above, will create a Data Table chart but one can then drag the source data cells onto it. I would think that moving the chart with Cut/Paste should create a Data Ranges chart, however I cannot find an issue which reports the present behavior as a bug.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Mac FAQ

Re: Are there limitations on chart locations?

PostPosted: Thu Apr 18, 2019 12:40 am
by IsabelDalhousie
Mr Programmer YOU ROCK!! Thanks so much ... really appreciate the precise instructions this worked beautifully. Thanks also to Lupp and RobLeyd for chiming in ...