{Can I use INDIRECT to create different named ranges on diffent Calc sheets?[Solved]

[Thailandian]

I would like to use a single LibreOffice macro to sort different ranges of data on different sheets.

For example, on Sheet 1, I want to sort, A1:B10, but on Sheet 2, I want to sort A1 to B12; in both cases, using Column A as the key.

I have tried making named ranges such as $A$1:INDIRECT(S1) where S1 contains the text $B$10, but the name doesn’t appear in the named ranges list.

As an experiment, I also tried a named range of a single cell INDIRECT($S$1), which I know works in a cell formula, but the name still doesn’t appear in the list.

Am I doing something wrong, or is this just not possible?

[Thailandian]

I’ve been experimenting some more, and am now fairly sure this is impossible.

I suspect the problem is that the dialog box is expecting a text string with the address, so any formula is invalid. I tried the simplest I could think of: A1:B9+2 and that failed.

[erAck]

It looks as if you are confusing cell range references and named ranges, which makes it hard to follow what you actually want. A1:INDIRECT(S1) is not a named range, it is a formula expression and would not magically appear in the named expression list. But you can name such an expression and use the name in your formula, just in the named range dialog enter the expression instead of a range.

You could also use a cell range reference where one corner cell is a named cell, such as A1:NamedCell where NamedCell previously was defined to, for example, A100.

[mariosv]

I think you can’t create a range name with a cell address as name. The ‘$’ isn’t allowed in range names.

Screenshot about how define range names per sheet.

[Thailandian]

Thanks for the response, but I can make a named range that include a ‘$’; e.g. $A$1:$B$12.

It’s only when I try adding the INDIRECT function that it doesn’t work.

[mariosv]

I think you are not talking about range name (Menu/Insert/Names/Define names) but about cell addresses.
Maybe will be better to show here the macro line with the issue, so someone with knowledge in macros can help you.

[Thailandian]

Thanks for the response. Actually, I was talking about Menu/Insert/Names/Define. If I could define a range with the same name for each sheet, then I could reference those rages with a single macro.

However, since this isn’t working, I could change the range using the macro itself. Here is the relevant code:

args1(0).Value = "$A$1:$B$10"

So what I’d like to do is change the $B$10 part to read in an address from the given spreadsheet.

[mariosv]

Please what is your LibreOffice version?. I have just verified it is possible in 4.0.6.2, please see the added screenshot.

[Thailandian]

Thanks for the response mariosv.
Just to be clear, I know I can include the $ symbol in names. What I want to do is use the INDIRECT function; e.g.

$A$1:INDIRECT(S1).

[mariosv]

I think you can use it in the range not in the name, putting a $ in the name, [Add] button is disable.
In any case I can’t help with macros, seems to me better redefine the title of this question.
You can find macro documentation in: https://wiki.documentfoundation.org/Macros

[Thailandian]

So after some discussion and a lot of experimenting, it’s clear that the answer to my question is “no”.

It should be possible to get the required result from within the macro itself, but I should probably start a different thread for that.