Date: prev next · Thread: first prev next last
2020 Archives by date, by thread · List index

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.

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


I trust this helps.

Brian Barker

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: 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.