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