Re: [libreoffice-users] Range names

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: 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