software

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

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:
contextures.com/xlNames01.html#Dynamic

David wrote:
gt;
gt; Excel2000
gt;
gt; I've been scouring the archives for what I want and all posts seem to
gt; advise against it, but I want it!!!
gt;
gt; I have a workbook where each sheet has a named range 'Attendance' that is
gt; unique to that sheet and depending on which sheet is selected, clicking
gt; Attendance from the NameBox dropdown will highlight that sheets Attendance
gt; range. I don't know how I accomplished that, since it's been years, but now
gt; I want a similar common name for 2 of the sheets in that workbook. I want
gt; SortRange for those 2 sheets, so when I select either sheet and click
gt; SortRange from the NameBox dropdown, it will highlight SortRange on the
gt; selected sheet.
gt;
gt; How can I do this?
gt;
gt; --
gt; David

--

Dave Peterson

Dave Peterson wrote

gt; Include the sheet name in the name definition:
gt;
gt; Insert|Name|Define
gt; Names in workbook:
gt; 'yoursheetnamehere'!SortRange
gt;
gt; refers to:
gt; ='yoursheetnamehere'!$a$1:$x$99
gt; (or whatever)
gt;
gt; If the range can grow, you may even want to make that name dynamic.
gt;
gt; See Debra Dalgeish's site for some nice tips:
gt; contextures.com/xlNames01.html#Dynamic
gt;
gt; David wrote:
gt;gt;
gt;gt; Excel2000
gt;gt;
gt;gt; I've been scouring the archives for what I want and all posts seem to
gt;gt; advise against it, but I want it!!!
gt;gt;
gt;gt; I have a workbook where each sheet has a named range 'Attendance'
gt;gt; that is unique to that sheet and depending on which sheet is
gt;gt; selected, clicking Attendance from the NameBox dropdown will
gt;gt; highlight that sheets Attendance range. I don't know how I
gt;gt; accomplished that, since it's been years, but now I want a similar
gt;gt; common name for 2 of the sheets in that workbook. I want SortRange
gt;gt; for those 2 sheets, so when I select either sheet and click SortRange
gt;gt; from the NameBox dropdown, it will highlight SortRange on the
gt;gt; selected sheet.
gt;gt;
gt;gt; How can I do this?
gt;gt;
gt;gt; --
gt;gt; David
gt;

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

gt; Include the sheet name in the name definition:
gt;
gt; Insert|Name|Define
gt; Names in workbook:
gt; 'yoursheetnamehere'!SortRange
gt;
gt; refers to:
gt; ='yoursheetnamehere'!$a$1:$x$99
gt; (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

gt; Include the sheet name in the name definition:
gt;
gt; Insert|Name|Define
gt; Names in workbook:
gt; 'yoursheetnamehere'!SortRange
gt;
gt; refers to:
gt; ='yoursheetnamehere'!$a$1:$x$99
gt; (or whatever)
gt;
gt; If the range can grow, you may even want to make that name dynamic.
gt;
gt; See Debra Dalgeish's site for some nice tips:
gt; contextures.com/xlNames01.html#Dynamic
gt;
gt; David wrote:
gt;gt;
gt;gt; Excel2000
gt;gt;
gt;gt; I've been scouring the archives for what I want and all posts seem to
gt;gt; advise against it, but I want it!!!
gt;gt;
gt;gt; I have a workbook where each sheet has a named range 'Attendance'
gt;gt; that is unique to that sheet and depending on which sheet is
gt;gt; selected, clicking Attendance from the NameBox dropdown will
gt;gt; highlight that sheets Attendance range. I don't know how I
gt;gt; accomplished that, since it's been years, but now I want a similar
gt;gt; common name for 2 of the sheets in that workbook. I want SortRange
gt;gt; for those 2 sheets, so when I select either sheet and click SortRange
gt;gt; from the NameBox dropdown, it will highlight SortRange on the
gt;gt; selected sheet.
gt;gt;
gt;gt; How can I do this?
gt;gt;
gt;gt; --
gt;gt; David
gt;

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

gt; Include the sheet name in the name definition:
gt;
gt; Insert|Name|Define
gt; Names in workbook:
gt; '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

Whew!

Glad you got it working.

David wrote:
gt;
gt; Dave Peterson wrote
gt;
gt; gt; Include the sheet name in the name definition:
gt; gt;
gt; gt; Insert|Name|Define
gt; gt; Names in workbook:
gt; gt; 'yoursheetnamehere'!SortRange
gt;
gt; Aha! Stupid me was leaving out this important part!! After reading a reply
gt; from Bob Phillips in a 2004 thread, the lightbulb went off. Rechecking here
gt; comfirmed my oversight. Could have save lots of time if I'd read your reply
gt; more carefully. Many thanks.
gt;
gt; --
gt; David

--

Dave Peterson