[Solved] Dynamically change Chart Data Ranges

[FKlusmann]

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)

[Villeroy]

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.

[FKlusmann]

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.

[Villeroy]

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.

[FKlusmann]

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.

[Villeroy]

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.

[FKlusmann]

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.

[Villeroy]

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.

[FKlusmann]

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.

[Villeroy]

All this would be a perfect use case for a database with input forms, columns of distinct data types and clear table dimensions.

[Lupp]

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

[Zizi64]

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

ChartTest.ods

(26.22 KiB) Downloaded 475 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.

[Villeroy]

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

[Lupp]

(Just kidding?)
It's funny what you know about everybody's mind and soul. I feel naked now as if Grillo caught me.

[FKlusmann]

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]

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.

[FKlusmann]

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.

[robleyd]

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.

[FKlusmann]

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!

[FKlusmann]

ChartTest-3.ods

(29.44 KiB) Downloaded 249 times

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!

[Zizi64]

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 302 times

(Or - maybe - you can use the IF(ISBLANK(...)...) function at the calculating the points of the graph.)

[FKlusmann]

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!

[Zizi64]

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

[FKlusmann]

Tips:

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

[Villeroy]

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.

[FKlusmann]

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.

This would be very easy to do with a database....

which I cannot do......

anyway, ...
.....
Now you have a clean list to start with.

Thank you again!
Now I need to figure out a way to automate this......

[Zizi64]

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

[FKlusmann]

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.

[Villeroy]

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.

[MrProgrammer]

Hi, and welcome to the forum.

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