Ask Your Question

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

asked 2014-01-24 21:07:31 +0200

Thailandian gravatar image

updated 2014-01-28 08:50:56 +0200

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 04:04:37.924798


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.

Thailandian gravatar imageThailandian ( 2014-01-25 06:40:48 +0200 )edit

3 Answers

Sort by » oldest newest most voted

answered 2014-01-28 12:29:00 +0200

erAck gravatar image

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.

edit flag offensive delete link more

answered 2014-01-25 02:31:33 +0200

m.a.riosv gravatar image

updated 2014-01-26 16:25:52 +0200

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.

image description

edit flag offensive delete link more


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.

Thailandian gravatar imageThailandian ( 2014-01-25 04:01:03 +0200 )edit

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.

m.a.riosv gravatar imagem.a.riosv ( 2014-01-25 17:38:04 +0200 )edit

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.

Thailandian gravatar imageThailandian ( 2014-01-26 12:51:56 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2014-01-26 16:24:12 +0200 )edit

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.

Thailandian gravatar imageThailandian ( 2014-01-26 18:34:45 +0200 )edit

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:

m.a.riosv gravatar imagem.a.riosv ( 2014-01-26 20:01:10 +0200 )edit

answered 2014-01-27 17:57:30 +0200

Thailandian gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-01-24 21:07:31 +0200

Seen: 1,178 times

Last updated: Jan 28 '14