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.