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.
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
--
To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy