Common range name for 2 sheets

[David]

Excel2000

I've been scouring the archives for what I want and all posts seem to
advise against it, but I want it!!!

I have a workbook where each sheet has a named range 'Attendance' that is
unique to that sheet and depending on which sheet is selected, clicking
Attendance from the NameBox dropdown will highlight that sheets Attendance
range. I don't know how I accomplished that, since it's been years, but now
I want a similar common name for 2 of the sheets in that workbook. I want
SortRange for those 2 sheets, so when I select either sheet and click
SortRange from the NameBox dropdown, it will highlight SortRange on the
selected sheet.

How can I do this?

 

[Dave Peterson]

Include the sheet name in the name definition:

Insert|Name|Define
Names in workbook:
'yoursheetnamehere'!SortRange

refers to:
='yoursheetnamehere'!$a$1:$x$99
(or whatever)

If the range can grow, you may even want to make that name dynamic.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

 

[David]

Dave Peterson wrote

Include the sheet name in the name definition:

Insert|Name|Define
Names in workbook:
'yoursheetnamehere'!SortRange

refers to:
='yoursheetnamehere'!$a$1:$x$99
(or whatever)

If the range can grow, you may even want to make that name dynamic.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Well, I can't get it to work
First range I want is ='Breakfast(2)'!$A$3:$AB$32
Second range I want is ='Lunch(2)'!$A$3:$AB$32

I select Breakfast(2) and Insert|Name|Define and type
SortRange in the top box and
='Breakfast(2)'!$A$3:$AB$32 in the Refers to: box and click Add
Then I select Lunch(2) and Insert|Name|Define and type
SortRange in the top box and
='Lunch(2)'!$A$3:$AB$32 in the Refers to: box and click Add

I select SortRange from the Name box while in Breakfast(2) and the range
in Lunch(2) gets highlighted.

What am I missing??

 

[David]

Dave Peterson wrote

Include the sheet name in the name definition:

Insert|Name|Define
Names in workbook:
'yoursheetnamehere'!SortRange

refers to:
='yoursheetnamehere'!$a$1:$x$99
(or whatever)

Rereading my original post, I may have misrepresented how the Attendance
named range works. Each sheet has a named range, 'Attendance' unique to
that sheet, so depending on which sheet is selected, the selected sheets
'Attendance' range is selected.

 

[David]

Dave Peterson wrote

Include the sheet name in the name definition:

Insert|Name|Define
Names in workbook:
'yoursheetnamehere'!SortRange

refers to:
='yoursheetnamehere'!$a$1:$x$99
(or whatever)

If the range can grow, you may even want to make that name dynamic.

See Debra Dalgeish's site for some nice tips:
http://contextures.com/xlNames01.html#Dynamic

Well, I did it the hard way. I named the range in Breafast(2),copied the
sheet, renamed it Lunch(2)(after deleting the original) and updated any
data, formulas and other named ranges on other sheets to match what was
in or referred to original Lunch(2)--(I hope I got them all)

Still puzzled over the difference between Global and Local named ranges
and how to enter them so they'll work. This is a relatively small
workbook with a limited amount of rows and columns, so corrections to
what was messed up by the sheet copy weren't THAT involved.

 

[David]

Dave Peterson wrote

Include the sheet name in the name definition:

Insert|Name|Define
Names in workbook:
'yoursheetnamehere'!SortRange

Aha! Stupid me was leaving out this important part!! After reading a reply
from Bob Phillips in a 2004 thread, the lightbulb went off. Rechecking here
comfirmed my oversight. Could have save lots of time if I'd read your reply
more carefully. Many thanks.

 

[Dave Peterson]

Whew!

Glad you got it working.

Dave Peterson wrote


Aha! Stupid me was leaving out this important part!! After reading a reply
from Bob Phillips in a 2004 thread, the lightbulb went off. Rechecking here
comfirmed my oversight. Could have save lots of time if I'd read your reply
more carefully. Many thanks.