Once again I accidentally sent something in private rather than to the list. When will I learn? Anyway, apologies to Dave and here it is for the list:
---------- Forwarded message --------- Från: Johnny Rosenberg <[email protected]> Date: sön 10 maj 2020 kl 09:07 Subject: Re: [libreoffice-users] Range names To: Dave Howorth <[email protected]> Den ons 6 maj 2020 kl 00:11 skrev Dave Howorth <[email protected]>: > On Tue, 5 May 2020 21:20:24 +0200 > Johnny Rosenberg <[email protected]> wrote: > > > Den tis 5 maj 2020 kl 08:38 skrev Luuk <[email protected]>: > > > > > > > > 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. > > > > > > > What I probably want doesn't have anything to do with me. That's > > rather a matter of other peoples guesses about me and not relevant > > for anything. > > > > > > > > > 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". > > > > > > Great, that's exactly what I want. > > > > 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. > > > > > > > I can't see any problems with that. Maybe I don't follow. > > > > > > > > > > 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. > > > > > > Yes, why would it do anything else than that? > > > > > > > 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. > > > > > > Oh, I don't know anything about that. I only know I wouldn't, and if > > I did, it would be my fault only. I'm not expecting Calc to do the > > thinking for me. > > > > > > > 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". > > > > > > No, I'm rarely impressed when things just work as expected. They just > > should. > > > > > > > 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. > > > > > > > No, that's just wrong. Only absolute references (those with $-signs > > everywhere) will be searched for, nothing else. Don't over complicate > > this now. > > > > > > > > >> I'm going to write a macro for it, ... > > > > > > > > Which of the thirty-two variations will it replace? > > > > > > > The one that Calc itself associates with the name, that is those with > > $-signs everywhere. I actually finished my macro instead of making up > > a lot of problems. > > It proved to be quite easy, actually: > > You can easily get any used name and its corresponding reference (or > > ”address”) by index with: > > lNameCount=ThisComponent.NamedRanges.getCount() > > For lNameIndex=0 To lNameCount-1 > > sName=ThisComponent.NamedRanges.getByIndex(lNameIndex).getName() > > sAddress=ThisComponent.NamedRanges.getByIndex(lNameIndex).getContent() > > ⁝ > > ⁝ > > Next lNameIndex > > > > sAddress in this case always looks something like $SomeSheet.$A1:$B75. > > Why isn't it: $SomeSheet.$A$1:$B$75 ? > It is indeed, I just made a typo. Sorry, and thanks for pointing it out. ☺ Kind regards Johnny Rosenberg > > > Inside that loop I have another loop, that loops through all sheets. > > When searching the same sheet as the Name is located, I also search > > for $A1:$B75 kind of reference. > > > > It works great as far as I can tell, so far. Maybe I will run into > > something in the future, who knows? > > The only problem I saw so far is that at one point there was a > > reference like this: $SomeSheet$A$1:$SomeSheet$B$75, that is with the > > sheet name on both sides of the colon. I'm not sure how that could > > happen. I'm pretty sure I didn't type that and so far I found no way > > to make Calc doing that, so I'm not sure what went wrong there. Maybe > > it was me after all. The macro seems to be very fast anyway, so I > > guess I could make it take care of that kind of reference as well > > with reasonable speed. > > > > > > Kind regards > > > > Johnny Rosenberg > > > > > > > > > If every combination has a unique (range-)name, who cares? > > > > > > A1:B5 ==> range1 > > > > > > > I tried to assign a name to such a reference, but Calc didn't like > > that very much… I get Error 522 (circular reference) every time I try > > to use the name. Maybe it's a bug. > > > > > > > > A1:B$5 ==> range2 > > > > > > A1$:B5 => someOtherRange > > > > > The last one is not valid (#NAME?), so not a good example. > > > > > > Kind regards > > > > Johnny Rosenberg > > > > > > > .... > > > > > > > > > > > I trust this helps. > > > > > > > > Brian Barker > > -- > To unsubscribe e-mail to: [email protected] > 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 > -- To unsubscribe e-mail to: [email protected] 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
