Common range name for 2 sheets
 

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?

--
David

Common range name for 2 sheets
 

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 wrote:

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?

--
David

--

Dave Peterson

Common range name for 2 sheets
 

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

David wrote:

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?

--
David


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

Common range name for 2 sheets
 

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

Common range name for 2 sheets
 

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

David wrote:

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?

--
David


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

Common range name for 2 sheets
 

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.

--
David

Common range name for 2 sheets
 

Whew!

Glad you got it working.

David wrote:

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.

--
David

--

Dave Peterson