[Solved] Dynamically change Chart Data Ranges

Discuss the spreadsheet application

[Solved] Dynamically change Chart Data Ranges

Postby FKlusmann » Fri Jan 20, 2017 6:42 pm

Can one dynamically change Chart Data Ranges?
(YES, :bravo: see answer by MrProgrammer » posted Fri Feb 03, 2017 2:40 pm(on page 2) :D )

A table of blood tests grows over time. Depending on the frequency of tests, a graft of more than the last 10 test becomes meaningless. How can the X-axis be adjusted?

Thank you!
(Calc 5.2.3.3) on (Win - 10)
Attachments
ChartTest.ods
Graft only the last entries... if possible.
(29.65 KiB) Downloaded 177 times
Last edited by FKlusmann on Sat Feb 04, 2017 1:29 am, edited 3 times in total.
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: Dynamically change Chart Data Ranges

Postby Villeroy » Fri Jan 20, 2017 7:15 pm

menu:Tools>Options>Calc>General: "Expand references ...." = ON
Select A36:C41 on your sheet.
Right-click>Delete...
[X] SHift upwards

Now the chart refers to the actually used range A15:C35.
Insert new cells anywhere within that range or directly below in order to expand all references automatically. Insertion and deletion updates all references in charts, cell formulas, range names, conditional formatting, cell range validations, form controls etc.

Without checking the "Expand references ..." option, any insertion directly below would not expand your reference and insertion in the first row would move down the reference.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26749
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dynamically change Chart Data Ranges

Postby FKlusmann » Fri Jan 20, 2017 8:31 pm

Thank you.
I set "Expand references ...." = ON, deleted cells A36:D41, then added date and values to A36:B39.
The data ranges for the X-axises (lower chart) went to $Sheet1.$A$26:$A$35.
I tried again, deleting the "$" signs from all ranges first, and then deleted A34 and below. The "$" sign returned (locking the cells ?), and the range did not grow with additional data.
Also, as the cell rows grow, (from A34 to A40) I would like the data range to change from A24:A34 to A30:A40.
Thank you again.
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: Dynamically change Chart Data Ranges

Postby Villeroy » Fri Jan 20, 2017 8:39 pm

This works equally with all references, absolute and relative.
Insert new cells in columns A to C or insert entire rows since there are no other data beyond column C.

For easier row insertion with automatic width detection and automatic handling the "expand" option you may install my Python module https://forum.openoffice.org/en/forum/v ... =21&t=2350 and add 2 custom keyboard shortcuts.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26749
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dynamically change Chart Data Ranges

Postby FKlusmann » Fri Jan 20, 2017 9:00 pm

Thank you, Villeroy.

You said "Insert new cells ......or insert entire rows......"
Fine, yes, that works, but..
Is there any way to do this by adding data into existing cells?
For the view of the charts, this does not help restrain the data points to 10 as more row or cells are added.

I will look at your Python modules

I appreciate your help.
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: Dynamically change Chart Data Ranges

Postby Villeroy » Fri Jan 20, 2017 9:14 pm

No, there is no other way. This is how all spreadsheets work.
Oh, wait. You could save your data in a database and connect an import range to the database. Then updating the linked database range would also update all references but I think I'm the only one who works like that.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26749
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Dynamically change Chart Data Ranges

Postby FKlusmann » Fri Jan 20, 2017 10:28 pm

Thank you, Villeroy.

You said " there is no other way......"

Then I will go back to 'open-ended ranges. ( i.e. $A$15:$A100)
The graft only grows when cells hold a value, so I will adjust the starting points as the graft becomes too congested (which prompted this excursive).
"A" holds dates which come from another worksheet, and I don't want to delete any data.

I guess I edit the title to say [Solved - No way].

I appreciate your help.
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: [Solved - No way] Dynamically change Chart Data Ranges

Postby Villeroy » Sat Jan 21, 2017 1:22 am

When you add new data by means of copy&paste, then use the paste-special command [Insert key] with option "Shift cells down". This will expand all references as well.
Dragging cells with Alt+Drop (move and insert) or Ctrl+Alt+Drop (copy and insert) does the same.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26749
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved - No way] Dynamically change Chart Data Ranges

Postby FKlusmann » Sat Jan 21, 2017 2:43 am

I understand, Villeroy.
In this workbook I want one row on each sheet to represent the same date. So, on Sheet1 column A is the input for dates. The other sheets have =Sheet1.A10 (done on A10 on each sheet, and copied - by dragging and pasting wherever the dates are to appear (protected column so the user cannot change it).
For now, valued are directly entered into existing cells.

Shifting cells does not really help me here. there seems to be no way to also shift the starting cell in the chart axis range.
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: [Solved - No way] Dynamically change Chart Data Ranges

Postby Villeroy » Sat Jan 21, 2017 11:43 am

All this would be a perfect use case for a database with input forms, columns of distinct data types and clear table dimensions.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26749
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved - No way] Dynamically change Chart Data Ranges

Postby Lupp » Sat Jan 21, 2017 1:25 pm

Isn't it a strange and dissatisfying behaviour of Calc features, however, that the (doubtable) 'Validation' and the 'Named Ranges' tool accept calculated ranges/arrays while Charts don't?
It may be
A) for compatibility reasons.
B) to avoid problems concerning variable numbers of columns.
C) ?
(There are additional issues, in specific the conversion to 'Data Table...' under certain conditions - and the lack of a tool to revert it.)
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2489
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved - No way] Dynamically change Chart Data Ranges

Postby Zizi64 » Sat Jan 21, 2017 2:04 pm

Do you want to achieve somethig similar as in the modified example file?

ChartTest.ods
(26.22 KiB) Downloaded 144 times

The Chart will show the last 10 data (the data related to the 10 largest date-time value of the table).
Just add some data to the existing data table. The functions work up to 60-th row of the table, but you can modify the parameters of the functions.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved - No way] Dynamically change Chart Data Ranges

Postby Villeroy » Sat Jan 21, 2017 3:15 pm

=OFFSET($A$1;0;0;COUNTA(A$1:$A$1048576);3) returns an array starting at A1 with 3 columns and as many rows as there are data in the first column.
This is a popular trick for those spreadsheet addicted who hate row insertion. Charts and form controls (list and combo boxes) can't handle calculated range dimensions. Formulas can, conditional formatting can, validation can.
But whoever hates to expand lists by row insertion, hates the mere existence of a database document even more although this combination of a simple, say dBase file, with a spreadsheet does exactly what many users always wanted to have.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26749
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved - No way] Dynamically change Chart Data Ranges

Postby Lupp » Sat Jan 21, 2017 4:20 pm

(Just kidding?)
It's funny what you know about everybody's mind and soul. I feel naked now as if Grillo caught me.
On Windows 10: LibreOffice 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2489
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved - No way] Dynamically change Chart Data Ranges

Postby FKlusmann » Sat Jan 21, 2017 5:42 pm

Zizi64 , Thank you! That is exactly what I need. Perfect!

Villeroy, There is no hate here., only lack of knowledge. That is why I look towards teachers like you.

Database may be the better solution. It was not considered because the audience machines for this experiment do not have any dB program installed.

Row Insertion would have to work at the WorkBOOK level here (for my purpose). It appears to work only on WorkSHEETs.

Thank you !
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: [Solved] Dynamically change Chart Data Ranges

Postby Villeroy » Sat Jan 21, 2017 8:55 pm

Row insertion works on workbook level. In Excel, in Gnumeric and in Calc a reference OtherSheet.A1:C99 becomes OtherSheet.A1:A100 when you insert one row.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26749
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Dynamically change Chart Data Ranges

Postby FKlusmann » Sun Jan 22, 2017 3:48 am

Villeroy wrote "Row insertion works on workbook level. .... OtherSheet.A1:C99 becomes OtherSheet.A1:A100"

Yes, existing references are maintained between sheets.

I assume that I did not explain well. Inserting a row on sheet 1 did NOT insert a corresponding row on sheet 2.
Also, inserting a row below on sheet 2 did not copy the formulas or references into the new row.

Am I missing something else?

Thank you for teaching me.
Last edited by FKlusmann on Sun Jan 22, 2017 6:23 pm, edited 1 time in total.
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: [Solved] Dynamically change Chart Data Ranges

Postby robleyd » Sun Jan 22, 2017 4:07 am

See the first answer, second option in How do I append a row to the end of a spreadsheet?

F1 and search references;expanding (Calc) will give you more information on expanding references.
Cheers
David
Apache OpenOffice Developer Build 4.2.0 9820 - Slackware 14.2 - 64 bit
LibreOffice 6.0.7.3 - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 2758
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] Dynamically change Chart Data Ranges

Postby FKlusmann » Sun Jan 22, 2017 6:30 pm

robleyd wrote:See the first answer, second option in How do I append a row to the end of a spreadsheet? and F1 and search references;expanding (Calc) will give you more information on expanding references.


Thank you!
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: [Solved - No way] Dynamically change Chart Data Ranges

Postby FKlusmann » Fri Jan 27, 2017 5:19 am

ChartTest-3.ods
(29.44 KiB) Downloaded 55 times
Zizi64 wrote:Do you want to achieve somethig similar as in the modified example file?

This is a follow-on question: Given that on one date many tests may (or not) be done. It is valid for a test NOT to be done. How may a row be ignored from a chart if a given column holds no data?
Based on "Zizi64"'s work, here is my best attempt:
Column "A" holds Dates, "B" holds values where none is provided for a given date.
The "Helper table" and graft is based on LARGE and VLOOKUP. Note that the data point for 11/24/16 is null and plots as a negative spike going to zero.
"My test table" Column "P" checks for null so did not return a date or value for 11/24/16, and data point 6 was not plotted. An improvement! However, consider that a given test value is only once every 10 tests, it would not be plotted.
I appreciate any ideas. Thank you!
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: [Solved] Dynamically change Chart Data Ranges

Postby Zizi64 » Fri Jan 27, 2017 1:08 pm

You can use a "Space" character (or some another textual value) in the first table instead of the empty cells.

ChartTest-3.ods
(37.36 KiB) Downloaded 64 times



(Or - maybe - you can use the IF(ISBLANK(...)...) function at the calculating the points of the graph.)
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Dynamically change Chart Data Ranges

Postby FKlusmann » Fri Jan 27, 2017 5:11 pm

Zizi64 wrote:You can use a "Space" character ...................

Thank you, Zizi64
The problem is that every row is taken into the new table.
I wish for a table being made with rows with Date and Values only from rows from the original table containing values.
I manually entered a table here to show what I hope for.
Thank you again!
Attachments
ChartTest-3a.ods
(32.61 KiB) Downloaded 62 times
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: [Solved] Dynamically change Chart Data Ranges

Postby Zizi64 » Fri Jan 27, 2017 10:21 pm

I wish for a table being made with rows with Date and Values only from rows from the original table containing values.



Tips:

1.: Use a Filter on the original tasble with copied results (to the primary helper table). Then you reference the desired (last 10) data into secondary helper table with the functions LARGE(), VLOOKUP()...

2.: Use a macro to delete the rows containing empty data cells...
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Dynamically change Chart Data Ranges

Postby FKlusmann » Tue Jan 31, 2017 5:53 pm

Zizi64 wrote:Tips:


It works, thanks to Zizi64 !!!! No macro needed.
(notice that Calc plots values higher than they are?)

Dynamically Change Data Range.jpg
Attachments
ChartTest-4.ods
(35.14 KiB) Downloaded 86 times
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: [Solved] Dynamically change Chart Data Ranges

Postby Villeroy » Tue Jan 31, 2017 11:48 pm

VLOOKUP does not handle duplicates. Create some dupes in column C. The VLOOKUPs return the same date which is the first matching date.
This would be very easy to do with a database where you could simply query all blue records having some value. Spreadsheets work with single values, databases handle row sets.

anyway, ...
Create a new sheet
Select your blue list in A18:C52
menu:Data>Define...
This is a database range without column headers. Just add a name and click OK.
Now you can click any single cell in this list and do this:
Data>FIlter>Standard Filter...
Column C <not empty>
[More Options...]
Copy output to ... <other sheet.A1>
Now you have a clean list to start with.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26749
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Dynamically change Chart Data Ranges

Postby FKlusmann » Wed Feb 01, 2017 3:17 am

Villeroy wrote:VLOOKUP does not handle duplicates. Create some dupes in column C. The VLOOKUPs return the same date which is the first matching date..
A fine catch, Villeroy! In fact rows 21 and 23 dupes, and the only the first date was carried to the net step. I guess that I am not very observant.
Villeroy wrote:This would be very easy to do with a database....
which I cannot do......
Villeroy wrote:anyway, ...
.....
Now you have a clean list to start with.

Thank you again!
Now I need to figure out a way to automate this......
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: [Solved] Dynamically change Chart Data Ranges

Postby Zizi64 » Wed Feb 01, 2017 9:02 am

(notice that Calc plots values higher than they are?)


The secondary Y axis is shown in your file, but the graph lines are assigned to the primary axis. You must fix it:
- use (switch ON and format) the primary axis or
- assign the graph lines to the secondary axis.

Probably this problem is related to the fact, that the X axis has revert-ordered values, and the position of the secondary axis is adjusted to the END of the X axis. The "END" probably means the largest value on the axis.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7984
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Dynamically change Chart Data Ranges

Postby FKlusmann » Wed Feb 01, 2017 4:06 pm

Zizi64 wrote:Probably this problem is related to the fact, that the X axis has revert-ordered values, and the position of the secondary axis is adjusted to the END of the X axis. The "END" probably means the largest value on the axis.

Thank you! I see that now.
Villeroy pointed out that VLOOKUP does not handle duplicates and showed a nice solution. Now I need to find a way to handle this without the dreaded macro warnings.

Every question answered seems to bring in two more to be asked........
Thanks.
I Thank you ALL! (Win 10 - LibreOffice Version: 5.3.7.2 (x64))
Saving the Planet! Only recycled electrons were used in this post!
FKlusmann
 
Posts: 41
Joined: Fri Jan 20, 2017 3:22 am

Re: [Solved] Dynamically change Chart Data Ranges

Postby Villeroy » Thu Feb 02, 2017 10:57 pm

FKlusmann wrote:Every question answered seems to bring in two more to be asked........

But the answer remains the same: A spreadsheet is not a database, no matter how hard you try.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26749
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Dynamically change Chart Data Ranges

Postby MrProgrammer » Fri Feb 03, 2017 2:41 am

Hi, and welcome to the forum.

FKlusmann wrote:… a [chart] of more than the last 10 test becomes meaningless. How can the X-axis be adjusted?
Though the topic is marked Solved, there seem to be additional questions. Perhaps the attached method will be of interest. It does not use evil macros.

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.
Attachments
201702021521.ods
(26.64 KiB) Downloaded 268 times
Mr. Programmer
AOO 4.1.5 Build 9789 on MacOS 10.11.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3715
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Next

Return to Calc

Who is online

Users browsing this forum: No registered users and 29 guests