[Solved] Are there limitations on chart locations?

Discuss the spreadsheet application
Post Reply
User avatar
IsabelDalhousie
Posts: 20
Joined: Thu Aug 10, 2017 2:12 am

[Solved] Are there limitations on chart locations?

Post 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
Last edited by IsabelDalhousie on Thu Apr 18, 2019 12:41 am, edited 1 time in total.
Apache OpenOffice 4.1.5 running on macOS High Sierra 10.13.6 Arizona time zone
User avatar
robleyd
Moderator
Posts: 5036
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Are there limitations on chart locations?

Post by robleyd »

You might find this topic helpful ?
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Are there limitations on chart locations?

Post 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...)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
MrProgrammer
Moderator
Posts: 4883
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Are there limitations on chart locations?

Post 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
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
IsabelDalhousie
Posts: 20
Joined: Thu Aug 10, 2017 2:12 am

Re: Are there limitations on chart locations?

Post 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 ...
Apache OpenOffice 4.1.5 running on macOS High Sierra 10.13.6 Arizona time zone
Post Reply