Named References

[ajames]

Does anyone know how to create one named reference across multiple
worksheets in the same workbook (BUT, not for the same cell reference
in each worksheet).
For example, the reference of TOTAL that could apply to cell A10 in
worksheet1, but A13 in worksheet2.
I know there is a way because I have inadvertantly done it for one
reference, I now need to replicate this and don't know how!

Please help

 

[Guest]

If I'm understanding you right, in the Insert Names Define Dialog, where it
asks you for the name, enter the full sheet refernce and the name eg 'Sheet
1'!TOTAL
That allows you to have the same name on different worksheets. Formulas on
that sheet will only see the range on that sheet, not others. Formulas on
sheets without the name will either see the "global" name (that appears in
the names dialog when you're in any worksheet, and doesn't show a sheet name
against it), or if there isn't one, will return a #NAME? error

BrianH

 

[ajames]

Thanks for your response, but unfortunately this does not work. If I
call cell A10 in Sheet1 "TOTAL" and then go to Sheet2, click on A13 and
try and define it as "TOTAL", the named reference comes up as
Sheet1!A10, I can override it to Sheet2!A13, but then it doesn't work
for Sheet1 anymore!
The one where I have managed to do it correctly for (Somehow), when I
go into the Define Name box, the list of current names shows up in the
second box as normal, but there seems to be a second column in that box
which states the worksheet that the name applies to.

 

[Jim May]

Assign Rande names to each and

=SUM(First,Second,Third)

 

[Guest]

You need to put the sheet name in front of the RANGE NAME, as per my previous
response, not just in front of the cell reference. If there is no sheet name
in front of the range name, it is "seen" from anywhere in the workbook, so
hence the behaviour you describe if you simply change the sheet and cell
referenced by an unqualified range name.

If you create a range name on a sheet, and then copy the sheet, the original
sheet will contain the cells refernced globally - ie "seen" from anywhere in
the workbook, and the copied sheet will now contain a local version of the
range name, seen only by formulas on that sheet, or by formulas elsewhere
that use the full sheet reference plus range name. Depending where your
formulas referncing only the range name without a sheet name qualifier are,
they'll pick up one or the other - can be dangerous, care required!

BrianH

 

[Jim May]

You can also create a new name Mytotal and in the
RefersTo box enter =SUM(First,Second,Third)
then you can in a cell just enter =Mytotal

 

[ajames]

Brian,

I'm really sorry, but I don't understand what you mean.

I have gone in to Insert, Name, Define and inserted the name TOTAL. In
the Refers to box I have the text =Sheet1!$A&10

If I then go to Sheet2 and try and create the name TOTAL again, it just
brings up the original one. If I override the Refers to box with, for
example, =Sheet2!$A$12 - then the name does not work for the first
sheet anymore.

I am trying to use this in a macro whereby if they run the macro on
Sheet1 - it will go to the named range TOTAL (A10), but if they run the
macro when they are on Sheet2 then it will go to the named range TOTAL
(A12).

I would really appreciate it if you could explain to me exactly what I
need to do differently.

Thanks

 

[Guest]

Go to Insert, Name, Define and enter 'Sheet1'!TOTAL in the Names box above
the list (the apostrophes are required if you have spaces or other special
characters in your sheet name, but are optional in this particular case), and
whatever cell reference you want in the Refers to box - Sheet1!$A$10 in your
case. You will see Sheet1 appear to the right of the TOTAL name in the
dialog box when you reopen it while you're on that sheet.

Similarly for Sheet2, and so on.

Now formulas on those sheets that reference TOTAL will pick up the value
from the same sheet, and macros will refer to the TOTAL cell on the active
worksheet.

If you have a name that shows TOTAL without a sheet name - entered as per
your current process - entering a formula on a sheet that doesn't have it's
own TOTAL will pick up that value, wherever it is. If you don't have a
"global" TOTAL, a formula will generate a #NAME? error, and your macro will
generate a run time error. You can even have the "global" named cell and a
sheet specific cell with the same name on the one sheet and they can be the
same or different cells! Formuals on that sheet see the local sheet-specific
name, not the global name.

You can however refernce a sheet range name from elsewhere by putting the
full sheet refernce in the formula (=Sheet1!TOTAL), from anywhere in this (or
any other) workbook, and in a macro by Worksheets("Sheet1").Range("TOTAL")

I haven't been able to work out how you can access the "global" name by
formula or macro from a sheet with a "local" name. If for some reason you
need to do that, at present I'd have to say use a different name.

Cheers

BrianH

 

[ajames]

You are an absolute star!

 

[Guest]

My pleasure

Cheers