Defining same name for cells in different sheets

[Parisicaine]

Does anyone know the answer to this one?
I want to give the same range name to the same cell
reference in a series of worksheets.
I find I can do this by pre-defining all my range names on
a "master" worksheet and making several copies of the
sheet (try it, it works!)
But let's say I have done this, and entered all my data on
the sheets I have created, and suddenly realize I need
another range name. I haven't found any way to define a
new range name and apply it to the same range on several
sheets.
This is not the same as a 3-D reference, which I tried. (3-
d referencing groups together in one range name cells over
several worksheets.)
What's the trick? I haven't found the answer to this
anywhere?
Thanks in advance!
Parisicaine

 

[Ken Wright]

If you want to apply the name abc to the range A1 on sheets 1, 2, and 3, then go to sheet 1,
select A1 and do Insert / Name / Define and then put in sheet1!abc instead of just abc. You can
then repeat this for each sheet, and then regardless of which sheet you are on you can just select
abc and it will take you to A1 on that sheet

 

[Gord Dibben]

Andy

I'm not Ken but....yes, you can.

Gord Dibben Excel MVP XL2002

 

[Parisicaine]

Thanks for your quick answers!
I'll try both methods. Why couldn't I find that in the
Excel help files?
Regards,
Parisicaine

 

[David Hager]

Be aware that a recalculation is necessary for this to work.

David Hager
Excel MVP