If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
checking that cells have a value before the workbook will close | kcdonaldson | Worksheet Functions | 8 | December 5th, 2005 04:57 PM |
Help PLEASE! Not sure what answer is: Match? Index? Other? | baz | Worksheet Functions | 7 | September 3rd, 2005 03:47 PM |
Use of variable to identify range of sheets in a workbook | rhs414 | General Discussion | 1 | June 20th, 2005 01:42 PM |
Lookup from a range on separate sheets | Paul Reeve | General Discussion | 1 | April 27th, 2005 11:20 AM |
usual methods don't reset sheet's "used range" | fern | General Discussion | 4 | August 8th, 2004 11:00 PM |