I use the indirect function quite a lot with Gnumeric, to extract a subset of the data from a given sheet. For example, I find the row numbers for the beginning and end of a date range into D2 and E2, then use concatenate to create what I want as my data range, eg
=concat(“Upcoming!BZ”,$D2,":BZ",$E2), to generate
Upcoming!BZ11246:BZ23663 in, for example G2, so that I can have indirect(G2) as my data range.
Can anyone else see a way to do this which doesn’t use indirect() ?
could have expected to set it up from Named Ranges and Expressions
unfortunately seems not so simple : 151800 – Chart won't display correctly if data is a named range
Youi can’t use named expressions (which aren’t factually named ranges) at all for charts.
Named ranges can be used in a limited way in the entries for Data Series. DataRanges stays empty then, but the chart may show correctly.
From my bit of experience:
At latest after a Save and loading again, all the entries for which named ranges were accepted, are replaced by the absolute addresses including the sheet names.
Possibly odf specifications don’t even have a way to store charts containing named ranges in their defining entries.
Please use LibO notations and prefer the semicolon as the argument delimiter. Always store spreadsheet documents to .ods files.
Not sure what you’re saying here: comma is the delimiter suggested by the function wizard, and, anyway, that part is working. The part that isn’t working is the use of the indirect function. I’ve tried duplicating precisely the format it generates itself when you select a range using the tool, which leaves the text as $‘Upcoming’.BZ11246:$‘Upcoming’.BZ23717, but the moment I type the word “indirect” into the data range box, it goes red, and disables the OK button.
Spreadsheet functions can’t be used in Chart ranges.
What Lupp was trying to say with “prefer the semicolon as the argument delimiter” is that the semicolon is accepted in all localizations, while the comma is locale dependent (i.e. only if the decimal separator is not comma), so here we prefer semicolon in examples that can be copy-pasted by everyone.
The ! sheet separator instead of . for INDIRECT() is only necessary if you export documents that are to be read by Excel.
1 Like
I believe you use Gnumeric and that means you can use the exclamation mark, but it does not mean you can work in the way you wish. Maybe the next idea can work.
Insert a new Sheet
Copy and past the data you want use for your chart. When you do it the first time then make later the Chart you want have. When you have made the chart then the next time you copy and past the data to the table.
After understanding the copy and past methode
When the copy and paste process is working then it is maybe possible to use:
*) scenarios’s
*) filter your datatable and place the result in the table for the chart
This methode can also work in LibreOffice. In LibreOffice can also work an other methode which does maybe not work in Gnumeric: hide the data in the table you want not show in the chart.