On 4 September 2014 04:32, Nicola Perotto <nicola@nicolaperotto.it> wrote:

> On 03/09/2014 13:14, RussellMc wrote:
> > 2. What I want is
> >
> > In Sheet 1 I want the name eg "MyIncome" to refer to cell B27 in Sheet1
> > In Sheet 2 I want the name eg "MyIncome" to refer to cell B27 in Sheet2
> > In Sheet 3 I want the name eg "MyIncome" to refer to cell B27 in Sheet3
> > ....
> > In Sheet N I want the name eg "MyIncome" to refer to cell B27 in SheetN
>


> You want the scope of name local to sheets:
>  <http://exceluser.com/excel_help/questions/localnames.htm>


Yes. That is exactly what I want.
Equivalent to a local variable in a function or subroutine or macro

AND

http://exceluser.com/excel_help/questions/localnames.htm


DOES in fact tell me how to achieve this the hard way - by editing each of
the cells where names are defined in each individual sheet where they are
used, even if the sheets are identical.


________________  *** BUT *** ___________________________

Eureka.
Lightbulb moment.
Lucky fluke
.....

Create local names for eg the sheet named "SALES2014"
using insert name define
with names of form

            SALES2014!name1
            SALES2014!name2
             .....
            SALES2014!nameN

These can be accessed within that sheet as eg

=3Dname1
=3Dname2 etc

THEN

Copy the sheet to a new sheet, say sheet SALES2015
AND
New local variables SALES2015!name1 etc are created in the new sheet

As before, using =3Dname3 accesses the cell within the current sheet.

This worked when I copied the whole sheet contents to a new sheet (which is
just what I want to do.)

It did NOT work when I copied an area with the relevant cells and
calculations using them to a new sheet, either when pasted in the same
position within a hseet or else where.

More investigation MAY allow a more universal way of creating a set of
common local variables within a sheet, but this does appear to meet my need=
..

____________________________

Method

Create a sheet as required.
Name the sheet with a simple name for now, such as AAA.
When creating names that will be local to the sheet using 'Insert Name
Define', instead of naming them eg Income,name them AAA!Income, where AAA
is the sheet name.
When finished and ready to clone the sheet.
    Highlight all (Ctrl A)
     Copy (Ctrl C)
    Swap to target sheet
    Highlight all OR move to cell A1
    Paste (Ctrl V)

Done
Local variables specific to THIS sheet now exist

Using Insert Name Define to display the name define menu shows global names
as before PLUS local names with SheetName!  in front of the variable name

WARNING:  If you define a global name eg Yoda, and then create  SheetN!Yoda
in a sheet, and then copy the whole sheet contents to a new blank sheet,
you will wish that you hadn't :-).
The global and local variable names exist and you (well, I did) will get
into a 2 stage dialogue loop that will not let you go until you do
something that you probably don't want to.
This is not a vast problem as you can delete the new now damaged sheet and
try again.
Removing the global / local clash before doing so is a good idea :-).

It works !!!!!!!!!!!!!!! :-)

Probably.

Many thanks to all. I'll know if there are any other fish-hooks shortly.



                     Russell
--=20
http://www.piclist.com/techref/piclist PIC/SX FAQ & list archive
View/change your membership options at
http://mailman.mit.edu/mailman/listinfo/piclist
.