Oo Calc - assign name ranges to a sheet

English
#1

Open office calc has a feature to change the scope of a named range from global to a particular sheet. I access this by Sheet/Named Ranges and Expressions/Manage. However I can only change the scope of one named range at a time.

For instance I would like to have named ranges “year”, “month” and “day” on more than two sheets, with “year”, “month” and “day” having different values on each sheet. In actuality, I will have many more than three named ranges per sheet.

This seems to be a job for a macro, but my knowledge of complicated macros is rudimentary. Can somebody here point me in the right direction.

#2

First, note that this site is about LibreOffice, not “Open office”. Then, what is your idea? You (seem to) have a global range; do you need a way to convert it into multiple sheet-local ranges (as opposed to the “convert to a range local to a single chosen sheet” currently)? Do you also expect the procedure to also manage the actual range definitions in the process? How should a macro know how to define the ranges on each sheet? Is that information known somewhere in your file?

#4

How should a macro know how to define the ranges on each sheet?

Because macros can do anything!

And the first sheet would have ranges declared using Sheet/Named Ranges and Expressions/Create.
Which creates global range names. The macro would then limit the scope of the range names to that sheet.

Next, create another sheet and do the same thing to create global named ranges. Then I would run the macro on that sheet, and so on.

I have recorded a macro to change one range name, which I thought I would use as a basis for developing it further, but it needs more work, of course.

And yeah, I meant libre office, not oo.

#5

Why create a global range, just to run a macro to convert it to local, instead of creating local range from start?

And maybe you don’t need named ranges at all - just use automatically find column and row labels feature?

#6
Why create a global range, just to run a macro to convert it to local, instead of creating local range from start??

Because I knew of no option to do that. Well, I will have to now find out how … don’t go away.

#3

Google led me to the following example:
https://wiki.documentfoundation.org/Macros/Calc/ba019

And there is a thread here on ask for the topic too:

PS: keywords for google:
libreoffice macro create named range

#7

Thanks Wanderer, I will look at the wiki thing too.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.