Range names

users
#1

Hi!

I'm just wondering if I'm missing something.
Let's say I have a spreadsheet (I actually have several…) with a lot of
cells and ranges already filled with data and formulas. One day I discover
the range naming feature, so I name a few cell ranges. Shouldn't there be
some easy way to replace every occurrence of those cell ranges in all my
formulas? And when I say easy, I mean easier than doing search and replace
on each one of them one by one. I can't find such a feature. Is there an
extension for it?

I'm going to write a macro for it, but I find it meaningless to write a
macro for something that's already there, so that's why I ask this question
in the first place.

Kind regards

Johnny Rosenberg

#2

Hi Johnny.
I came up against this issue last year after needing to open an excel
sheet in Calc.
Excel allows range names that Calc doesn't so the sheet wouldn't work. I
wanted to rename the offending excel ranges.
I couldn't find a solution like you seek.
steve

#3

Let's say I have a spreadsheet (I actually have several…) with a lot of cells and ranges already filled with data and formulas. One day I discover the range naming feature, so I name a few cell ranges. Shouldn't there be some easy way to replace every occurrence of those cell ranges in all my formulas?

I don't think any automatic system could do exactly what you probably want.

Say your range is A1:B5 on Sheet1 and suppose you name this as Name. You may think that "Name" is now synonymous with "A1:B5", but no: instead it is shorthand for "$Sheet1.$A$1:$B$5". There are thirty-two variations on "Sheet1.A1:B5" you may have in your spreadsheet, each including a different combination of those dollar signs. As you will know, each version behaves differently if you fill ranges from a cell with a formula containing it, or if you copy and paste from such a cell. So the differences are important. By including "Name" in a formula, you are choosing to imply the anchored or absolute version of the range.

In order to preserve the precise meaning and behaviour of your existing formulae, any automatic system should replace a spelled-out reference only when it includes all five dollar signs. But in practice one may rarely add all those dollar signs in formulae, instead using only as many are necessary for the filling or copying that one is expecting to need. I suspect you wouldn't be impressed if an automatic system failed to replace "A1:B5" or "A$1:B$5" with your newly defined "Name". But if it did, it would corrupt some spreadsheets that you or others might compose.

And when I say easy, I mean easier than doing search and replace on each one of them one by one. I can't find such a feature. Is there an extension for it?

When you do this, you would need to determine in each case whether the replacement by the range name would be appropriate. And an automatic system could not do that for you.

I'm going to write a macro for it, ...

Which of the thirty-two variations will it replace?

I trust this helps.

Brian Barker

#4

Let's say I have a spreadsheet (I actually have several…) with a lot of cells and ranges already filled with data and formulas. One day I discover the range naming feature, so I name a few cell ranges. Shouldn't there be some easy way to replace every occurrence of those cell ranges in all my formulas?

I don't think any automatic system could do exactly what you probably want.

Say your range is A1:B5 on Sheet1 and suppose you name this as Name. You may think that "Name" is now synonymous with "A1:B5", but no: instead it is shorthand for "$Sheet1.$A$1:$B$5". There are thirty-two variations on "Sheet1.A1:B5" you may have in your spreadsheet, each including a different combination of those dollar signs. As you will know, each version behaves differently if you fill ranges from a cell with a formula containing it, or if you copy and paste from such a cell. So the differences are important. By including "Name" in a formula, you are choosing to imply the anchored or absolute version of the range.

In order to preserve the precise meaning and behaviour of your existing formulae, any automatic system should replace a spelled-out reference only when it includes all five dollar signs. But in practice one may rarely add all those dollar signs in formulae, instead using only as many are necessary for the filling or copying that one is expecting to need. I suspect you wouldn't be impressed if an automatic system failed to replace "A1:B5" or "A$1:B$5" with your newly defined "Name". But if it did, it would corrupt some spreadsheets that you or others might compose.

And when I say easy, I mean easier than doing search and replace on each one of them one by one. I can't find such a feature. Is there an extension for it?

When you do this, you would need to determine in each case whether the replacement by the range name would be appropriate. And an automatic system could not do that for you.

I'm going to write a macro for it, ...

Which of the thirty-two variations will it replace?

If every combination has a unique (range-)name,  who cares?

A1:B5 ==> range1

A1:B$5 ==> range2

A1$:B5 => someOtherRange

....

#5

>> Let's say I have a spreadsheet (I actually have several…) with a lot
>> of cells and ranges already filled with data and formulas. One day I
>> discover the range naming feature, so I name a few cell ranges.
>> Shouldn't there be some easy way to replace every occurrence of those
>> cell ranges in all my formulas?
>
> I don't think any automatic system could do exactly what you probably
> want.

What I probably want doesn't have anything to do with me. That's rather a
matter of other peoples guesses about me and not relevant for anything.

> Say your range is A1:B5 on Sheet1 and suppose you name this as Name.
> You may think that "Name" is now synonymous with "A1:B5", but no:
> instead it is shorthand for "$Sheet1.$A$1:$B$5".

Great, that's exactly what I want.

There are thirty-two

> variations on "Sheet1.A1:B5" you may have in your spreadsheet, each
> including a different combination of those dollar signs. As you will
> know, each version behaves differently if you fill ranges from a cell
> with a formula containing it, or if you copy and paste from such a
> cell. So the differences are important. By including "Name" in a
> formula, you are choosing to imply the anchored or absolute version of
> the range.

I can't see any problems with that. Maybe I don't follow.

In order to preserve the precise meaning and behaviour of your
> existing formulae, any automatic system should replace a spelled-out
> reference only when it includes all five dollar signs.

Yes, why would it do anything else than that?

But in practice
> one may rarely add all those dollar signs in formulae, instead using
> only as many are necessary for the filling or copying that one is
> expecting to need.

Oh, I don't know anything about that. I only know I wouldn't, and if I did,
it would be my fault only. I'm not expecting Calc to do the thinking for me.

I suspect you wouldn't be impressed if an automatic
> system failed to replace "A1:B5" or "A$1:B$5" with your newly defined
> "Name".

No, I'm rarely impressed when things just work as expected. They just
should.

But if it did, it would corrupt some spreadsheets that you or
> others might compose.
>
>> And when I say easy, I mean easier than doing search and replace on
>> each one of them one by one. I can't find such a feature. Is there an
>> extension for it?
>
> When you do this, you would need to determine in each case whether the
> replacement by the range name would be appropriate. And an automatic
> system could not do that for you.

No, that's just wrong. Only absolute references (those with $-signs
everywhere) will be searched for, nothing else. Don't over complicate this
now.

>> I'm going to write a macro for it, ...
>
> Which of the thirty-two variations will it replace?

The one that Calc itself associates with the name, that is those with
$-signs everywhere. I actually finished my macro instead of making up a lot
of problems.
It proved to be quite easy, actually:
You can easily get any used name and its corresponding reference (or
”address”) by index with:
lNameCount=ThisComponent.NamedRanges.getCount()
For lNameIndex=0 To lNameCount-1
sName=ThisComponent.NamedRanges.getByIndex(lNameIndex).getName()
sAddress=ThisComponent.NamedRanges.getByIndex(lNameIndex).getContent()
⁝
⁝
Next lNameIndex

sAddress in this case always looks something like $SomeSheet.$A1:$B75.

Inside that loop I have another loop, that loops through all sheets. When
searching the same sheet as the Name is located, I also search for $A1:$B75
kind of reference.

It works great as far as I can tell, so far. Maybe I will run into
something in the future, who knows?
The only problem I saw so far is that at one point there was a reference
like this: $SomeSheet$A$1:$SomeSheet$B$75, that is with the sheet name on
both sides of the colon. I'm not sure how that could happen. I'm pretty
sure I didn't type that and so far I found no way to make Calc doing that,
so I'm not sure what went wrong there. Maybe it was me after all. The macro
seems to be very fast anyway, so I guess I could make it take care of that
kind of reference as well with reasonable speed.

Kind regards

Johnny Rosenberg

If every combination has a unique (range-)name, who cares?

A1:B5 ==> range1

I tried to assign a name to such a reference, but Calc didn't like that
very much… I get Error 522 (circular reference) every time I try to use the
name. Maybe it's a bug.

A1:B$5 ==> range2

A1$:B5 => someOtherRange

The last one is not valid (#NAME?), so not a good example.

Kind regards

Johnny Rosenberg

#6

>
> >> Let's say I have a spreadsheet (I actually have several…) with a
> >> lot of cells and ranges already filled with data and formulas.
> >> One day I discover the range naming feature, so I name a few
> >> cell ranges. Shouldn't there be some easy way to replace every
> >> occurrence of those cell ranges in all my formulas?
> >
> > I don't think any automatic system could do exactly what you
> > probably want.
>

What I probably want doesn't have anything to do with me. That's
rather a matter of other peoples guesses about me and not relevant
for anything.

>
> > Say your range is A1:B5 on Sheet1 and suppose you name this as
> > Name. You may think that "Name" is now synonymous with "A1:B5",
> > but no: instead it is shorthand for "$Sheet1.$A$1:$B$5".

Great, that's exactly what I want.

There are thirty-two
> > variations on "Sheet1.A1:B5" you may have in your spreadsheet,
> > each including a different combination of those dollar signs. As
> > you will know, each version behaves differently if you fill
> > ranges from a cell with a formula containing it, or if you copy
> > and paste from such a cell. So the differences are important. By
> > including "Name" in a formula, you are choosing to imply the
> > anchored or absolute version of the range.
>

I can't see any problems with that. Maybe I don't follow.

>

> In order to preserve the precise meaning and behaviour of your
> > existing formulae, any automatic system should replace a
> > spelled-out reference only when it includes all five dollar
> > signs.

Yes, why would it do anything else than that?

> But in practice
> > one may rarely add all those dollar signs in formulae, instead
> > using only as many are necessary for the filling or copying that
> > one is expecting to need.

Oh, I don't know anything about that. I only know I wouldn't, and if
I did, it would be my fault only. I'm not expecting Calc to do the
thinking for me.

> I suspect you wouldn't be impressed if an automatic
> > system failed to replace "A1:B5" or "A$1:B$5" with your newly
> > defined "Name".

No, I'm rarely impressed when things just work as expected. They just
should.

> But if it did, it would corrupt some spreadsheets that you or
> > others might compose.
> >
> >> And when I say easy, I mean easier than doing search and replace
> >> on each one of them one by one. I can't find such a feature. Is
> >> there an extension for it?
> >
> > When you do this, you would need to determine in each case
> > whether the replacement by the range name would be appropriate.
> > And an automatic system could not do that for you.
>

No, that's just wrong. Only absolute references (those with $-signs
everywhere) will be searched for, nothing else. Don't over complicate
this now.

>
> >> I'm going to write a macro for it, ...
> >
> > Which of the thirty-two variations will it replace?
>

The one that Calc itself associates with the name, that is those with
$-signs everywhere. I actually finished my macro instead of making up
a lot of problems.
It proved to be quite easy, actually:
You can easily get any used name and its corresponding reference (or
”address”) by index with:
lNameCount=ThisComponent.NamedRanges.getCount()
For lNameIndex=0 To lNameCount-1
sName=ThisComponent.NamedRanges.getByIndex(lNameIndex).getName()
sAddress=ThisComponent.NamedRanges.getByIndex(lNameIndex).getContent()
⁝
⁝
Next lNameIndex

sAddress in this case always looks something like $SomeSheet.$A1:$B75.

Why isn't it: $SomeSheet.$A$1:$B$75 ?

#7

Once again I accidentally sent something in private rather than to the
list. When will I learn?
Anyway, apologies to Dave and here it is for the list:

#8

steve.edmonds@ptglobal.com>:

steve.edmonds@ptglobal.com>:

Hi Johnny.
I came up against this issue last year after needing to open an excel
sheet in Calc.
Excel allows range names that Calc doesn't so the sheet wouldn't work. I
wanted to rename the offending excel ranges.

Out of curiosity, I would like to know what kinds of range names that is.
Can you give me some examples?

I filed a bug; https://bugs.documentfoundation.org/show_bug.cgi?id=130577

So Excel allows for a leading backslash, LibreOffice does not. I'l have a
look at it tomorrow, if I remember to.

Does your name changing macro change the name through range name and
formulae using that range name to be changed?

Do you mean the code I wrote below or the macro that I mentioned before
that?
The macro I mentioned first, the one with the obvious bug, is not finished
yet, but I think it will be tomorrow (and hopefully bug free too, almost).
It loops through all defined cell range names and cell names. For every
name, it finds its corresponding reference, for instance ”$Sheet1.$A$1”.
Then it search all sheets for that reference and replaces it with the cell
range name. So if cell A1 has the name ”Length” and cell A2 is
”=$A$1*$A$1”, then the macro will change A2 to ”=Length*Length”. However,
it will also change things like ”$A$123”, but I think I solved that one
now. I'll try to finish it tomorrow.

The code I wrote below is just a starting point if you want to create your
own macro to change all the names in that Excel file. I don't know if
that's even possible, maybe you need to do that in Excel, since those names
are invalid in LibreOffice Calc. I don't know yet, I'll give it a go
tomorrow, I think.

I think I might have to do my replacement of range names in the excel file
as by the time Calc opens it the import filter has messed it up when range
names are offending.

I just had some time to check it out, the file you attached to that bug
report, and we were right, you can't fix it automatically in LibreOffice
Calc. The problem is that some of the names are broken (refers to #REF!).
Of course you could do it manually if you know what all the names are. In
this case there are 34 names, and not all of them are broken, so it
shouldn't take too long if you know the references for all the broken
names. But I guess it's easier to fix it in Excel, either manually or with
a VBA macro. You could, for instance, make a VBA macro that replace all
backslashes with something else, like yet another underscore (first make
sure there will be no doublets) or just something unique, like _Ch92_,
_Asc92_, _bsol_ or whatever, making the risk of doublets minimal.

Kind regards

Johnny Rosenberg