[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [libreoffice-users] Range names
- Subject: Re: [libreoffice-users] Range names
- From: Brian Barker <firstname.lastname@example.org>
- Date: Mon, 04 May 2020 08:43:57 +0100
- To: email@example.com
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.
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/
|[libreoffice-users] Range names||Johnny Rosenberg <email@example.com>|
|Re: [libreoffice-users] Range names||Steve Edmonds <firstname.lastname@example.org>|
- Prev by Date: [libreoffice-users] Base Guide 6.2 Published!
- Next by Date: Re: [libreoffice-users] Insert page before TOC
- Previous by thread: Re: [libreoffice-users] Range names
- Next by thread: Re: [libreoffice-users] Range names