[Solved] Set chart Data Rages from cell value

[giavvns]

I need to do a graph over a range of cells, say, $A$1:$A$20. The cell number 20 always varies and it's been determined by another function. I usually go and manualy change the number 20 to whatever number the function outputs. How can I do that automatically? In other words how cai I have something like this:

$A$1:$A$$NUM, where NUM=20?

Thanks

[Villeroy]

Tools>Options>Calc>"Expand references when rows/columns are inserted " = ON

Now you can insert new cells anywhere within or directly below the referenced range and all references in formulas, names, charts, conditional formatting etc. will expand automatically.

With that setting turned off, only insertions between the second and the last row/column will expand references. Insertions on top will shift ther references and insertions directly below have no effect.

The same setting applies to columns likewise.

[MrProgrammer]

In other words how cai I have something like this: $A$1:$A$$NUM, where NUM=20?

You cannnot use constructs like $A$1:$A$$cell or INDIRECT(cell) in Format → Data Ranges for a chart. Data Ranges only support fixed cell ranges. However you can supply a large chart range like $A$1:$A$999. Unused cells at the end of the range are ignored by the chart. As you use more of the cells, the chart adjusts automatically.

If you need any additional assistance attach a spreadsheet demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). I will not help further unless you attach a spreadsheet.

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know