At 09:35 03/05/2020 +0200, Johnny Rosenberg wrote:
Let's say I have a spreadsheet (I actually have
) 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.
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