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
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
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
I trust this helps.
To unsubscribe e-mail to: firstname.lastname@example.org
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy