Re: [libreoffice-users] Range names

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+unsubscribe@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