[Solved] Dynamically change Chart Data Ranges
[Solved] Dynamically change Chart Data Ranges
Can one dynamically change Chart Data Ranges?
(YES, see answer by MrProgrammer » posted Fri Feb 03, 2017 2:40 pm(on page 2) )
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)
(YES, see answer by MrProgrammer » posted Fri Feb 03, 2017 2:40 pm(on page 2) )
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 437 times
Last edited by FKlusmann on Sat Feb 04, 2017 1:29 am, edited 3 times in total.
Re: Dynamically change Chart Data Ranges
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Dynamically change Chart Data Ranges
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 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.
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: Dynamically change Chart Data Ranges
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 viewtopic.php?f=21&t=2350 and add 2 custom keyboard shortcuts.
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 viewtopic.php?f=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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Dynamically change Chart Data Ranges
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.
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.
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: Dynamically change Chart Data Ranges
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Dynamically change Chart Data Ranges
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.
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.
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved - No way] Dynamically change Chart Data Ranges
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved - No way] Dynamically change Chart Data Ranges
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.
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.
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved - No way] Dynamically change Chart Data Ranges
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved - No way] Dynamically change Chart Data Ranges
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.)
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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Solved - No way] Dynamically change Chart Data Ranges
Do you want to achieve somethig similar as in the modified example file?
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.
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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: [Solved - No way] Dynamically change Chart Data Ranges
=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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved - No way] Dynamically change Chart Data Ranges
(Just kidding?)
It's funny what you know about everybody's mind and soul. I feel naked now as if Grillo caught me.
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 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Solved - No way] Dynamically change Chart Data Ranges
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 !
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 !
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved] Dynamically change Chart Data Ranges
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Dynamically change Chart Data Ranges
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.
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.
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved] Dynamically change Chart Data Ranges
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.
F1 and search references;expanding (Calc) will give you more information on expanding references.
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
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
Re: [Solved] Dynamically change Chart Data Ranges
Thank you!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 ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved - No way] Dynamically change Chart Data Ranges
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?Zizi64 wrote:Do you want to achieve somethig similar as in the modified example file?
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!
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved] Dynamically change Chart Data Ranges
You can use a "Space" character (or some another textual value) in the first table instead of the empty cells.
(Or - maybe - you can use the IF(ISBLANK(...)...) function at the calculating the points of the graph.)
(Or - maybe - you can use the IF(ISBLANK(...)...) function at the calculating the points of the graph.)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: [Solved] Dynamically change Chart Data Ranges
Thank you, Zizi64Zizi64 wrote:You can use a "Space" character ...................
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 196 times
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved] Dynamically change Chart Data Ranges
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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: [Solved] Dynamically change Chart Data Ranges
It works, thanks to Zizi64 !!!! No macro needed.Zizi64 wrote: Tips:
(notice that Calc plots values higher than they are?)
- Attachments
-
- ChartTest-4.ods
- (35.14 KiB) Downloaded 265 times
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved] Dynamically change Chart Data Ranges
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.
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Dynamically change Chart Data Ranges
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:VLOOKUP does not handle duplicates. Create some dupes in column C. The VLOOKUPs return the same date which is the first matching date..
which I cannot do......Villeroy wrote:This would be very easy to do with a database....
Thank you again!Villeroy wrote:anyway, ...
.....
Now you have a clean list to start with.
Now I need to figure out a way to automate this......
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved] Dynamically change Chart Data Ranges
The secondary Y axis is shown in your file, but the graph lines are assigned to the primary axis. You must fix it:(notice that Calc plots values higher than they are?)
- 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; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
Re: [Solved] Dynamically change Chart Data Ranges
Thank you! I see that now.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.
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.
Thank you ALL! (Win 10 - LibreOffice Version: 7.0.3.1 (x64))
Saving the Planet! Only recycled electrons were used in this post!
Saving the Planet! Only recycled electrons were used in this post!
Re: [Solved] Dynamically change Chart Data Ranges
But the answer remains the same: A spreadsheet is not a database, no matter how hard you try.FKlusmann wrote:Every question answered seems to bring in two more to be asked........
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- MrProgrammer
- Moderator
- Posts: 4883
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: [Solved] Dynamically change Chart Data Ranges
Hi, and welcome to the forum.
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.
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.FKlusmann wrote:… a [chart] of more than the last 10 test becomes meaningless. How can the X-axis be adjusted?
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 640 times
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).
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).