Can x axis auto update when adding data to a column?

[FaithfulScuff]

I have a workbook that I keep records of data collected from our pool. On sheet one I have the actual data collected from the tests I run on the water quality. On two I have three charts like this.
chart

When adding to the date column on sheet one the charts do not populate with the added dates. I have to edit like this by changing the last cell in the column.

When February comes around and I add another month to the column I will have to manually change the last cell of the date column to the chart.

Here is my question, can the x axis in sheet two with dates be linked to the column in sheet one with dates to populate automatically when adding cells and dates?

Version: 7.4.2.3 / LibreOffice Community

Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf

CPU threads: 4; OS: Mac OS X 11.7.2; UI render: default; VCL: osx

Locale: en-US (en_US.UTF-8); UI: en-US

Calc: threaded

[FaithfulScuff]

So i could not see when highlighting a chart that the columns highlight also. I could when I created a chart on the same page with the data.

I can now see and I knew but maybe asking the question incorrectly that x axis is associated with a column it has to be.

Looking at the screenshot there are 4 highlighted columns associated with the chart. They do not reach the bottom the the grid.

Apple numbers those highlighted columns can be drug down to include the rest of the grid and the chart would update new added data. Can something like this be done in calc? And if so how?

[Zizi64]

Please upload a real (ODF type) sample file here.

[LeroyG]

@FaithfulScuff

something like this

• Secondary click on the chart, choose Edit
• Secondary click on the chart, choose Data Ranges
• Select Series tab (also possible in Data Range tab, but a bit more complicated)
• Change values there

[FaithfulScuff]

test.ods (201.1 KB)

[FaithfulScuff]

@LeroyG

• Secondary click on the chart, choose Edit
• Secondary click on the chart, choose Data Ranges
• Select Series tab (also possible in Data Range tab, but a bit more complicated)
• Change values there

Is this the only way to change or add new data to the chart?

[LeroyG]

@FaithfulScuff

Is this the only way

No.
You can leave empty (without data) the last row in the Data Range (say 246), so when you need to add new rows, add them over this last row.

[FaithfulScuff]

Thank you for your reply, I have it setup with the last row in the data range being empty.

• I have tried dragging each row separately from 248, did not add cells to the data range.
• I have tried clicking row 248 and adding rows below, did not add cells to the data range.
• I have tried highlighting B-H in row 248 and dragging them down, did not add cells to data range.

Can you tell what I might be doing wrong or not understanding?
Thank you,

test.ods (174.7 KB)

[Villeroy]

Insert new cells in order to expand all references in formulas, named ranges, charts and wherever range addresses are used.
There is an option under Tools>Options>Calc>General> "Insert rows and columns when new cells are inserted.
.
WIth that option being checked, any insertion between the first row or column and the row/column directly below/behind will expand the reference. A formula like SUM(A1:C99) becomes SUM(A1:C100) after row insertion and SUM(A1::D99) after column insertion.

WITHOUT that option being checked, insertion at the first row or column will move the referenced range and insertion directly below/behind the referenced range does nothing.

[LeroyG]

@FaithfulScuff

adding rows below

Do not add rows below 248; add rows above 248.

[LeroyG]

@Villeroy

There is an option

Expand references when new columns/rows are inserted.

This option is disabled by default, but it works here.

[FaithfulScuff]

@LeroyG

Expand references when new columns/rows are inserted.

This option is disabled by default, but it works here.

There are actually two solutions here. I selected what I believe is the better of the two.
I can add rows above and it works but one row at a time.
I ticked the Expand references when new columns/rows are inserted AND then use and hold the keyboard shortcut Ctrl + (keypad + ) to add multiple rows.

Maybe I’ll look into making a macro or something that when used it will ask how many rows above or below, then insert them.

Thank you for the suggestions and solutions.
EDIT: I just noticed it includes formatting but no formulas.

[FaithfulScuff]

@Villeroy

WIth that option being checked, any insertion between the first row or column and the row/column directly below/behind will expand the reference. A formula like SUM(A1:C99) becomes SUM(A1:C100) after row insertion and SUM(A1::D99) after column insertion.

Will more complex formulas be left behind i.e. =IF(ISBLANK(I245),"",IF(I245<>"",(0.4943*LN(I245))-0.7661,0))
I have Expand references when new columns/rows are inserted and also Expand Formatting ticked

[LeroyG]

@FaithfulScuff

one row at a time

Six in the next screenshot.