[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [libreoffice-users] Range names


On Tue, 5 May 2020 21:20:24 +0200
Johnny Rosenberg <gurus.knugum@gmail.com> wrote:

> Den tis 5 maj 2020 kl 08:38 skrev Luuk <luuk34@gmail.com>:
>
> >
> > 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: 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

References:
[libreoffice-users] Range namesJohnny Rosenberg <gurus.knugum@gmail.com>
Re: [libreoffice-users] Range namesSteve Edmonds <steve.edmonds@ptglobal.com>
Re: [libreoffice-users] Range namesLuuk <luuk34@gmail.com>
Re: [libreoffice-users] Range namesJohnny Rosenberg <gurus.knugum@gmail.com>
Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.