Den tis 5 maj 2020 kl 08:38 skrev Luuk <luuk34@gmail.com>:
On 4-5-2020 09:43, Brian Barker wrote:
At 09:35 03/05/2020 +0200, Johnny Rosenberg wrote:
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.