+ Reply to Thread
Results 1 to 7 of 7

Common range name for 2 sheets

  1. #1
    David
    Guest

    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

  2. #2
    Dave Peterson
    Guest

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

  3. #3
    David
    Guest

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

  4. #4
    David
    Guest

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

  5. #5
    David
    Guest

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

  6. #6
    David
    Guest

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

  7. #7
    Dave Peterson
    Guest

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1