Naming ranges

[daniel bonallack]

I have 30 sheets in a workbook, and I want cell V43 in
each worksheet to have the named range Page_Num.

If I had one sheet with this range name, then copied it
29 times, then I would have the result I wanted - 30
sheets each with a worksheet-level range name.

But now that the sheets are created, I can't see how to
make the names - I only seem to be able to make one
instance of it.

Thanks in advance
Daniel

 

[Jan Karel Pieterse]

Hi Daniel,

I have 30 sheets in a workbook, and I want cell V43 in
each worksheet to have the named range Page_Num.

If I had one sheet with this range name, then copied it
29 times, then I would have the result I wanted - 30
sheets each with a worksheet-level range name.

But now that the sheets are created, I can't see how to
make the names - I only seem to be able to make one
instance of it.

If you just want to use the name locally on each sheet, you can do with
just one global name.

Define Page_Num as:

=INDIRECT(ADDRESS(ROW($V$43),COLUMN($V$43)))

Which will update when you insert/delete any rows or columns on the
sheet that was active when you defined the name.

This name Page_Num will always refer to cell V43 on the sheet you use
the name on.

If you use defined names a lot, consider downloading the
Name manager (by Charles Williams, Matthew Henson and
myself) from:

www.jkp-ads.com

or

www.bmsltd.ie/mvp

or from:

www.decisionmodels.com/downloads.htm

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Daniel Bonallack]

Thanks Jan

This is perhaps a little cleverer than what I need. I
want to be able to go to a V43 on any sheet, and in the
name box see the "Page_Num" (as would happen if I had one
sheet with that range name, then copied it).

Is this possible?

Daniel

 

[Jan Karel Pieterse]

Hi Daniel,

This is perhaps a little cleverer than what I need. I
want to be able to go to a V43 on any sheet, and in the
name box see the "Page_Num" (as would happen if I had one
sheet with that range name, then copied it).

Is this possible?

Sure, but you'll have to define the names one by one. Precede the name
with the name of the sheet you are defining it on. Enclose sheetnames
with spaces with single quotes:

Sheet1!Page_Num
'Sheet 1'!Page_Num

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com