| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HOW TO: Name Range?
Hi TWIMC,
I'd like to add one name range that works on each sheet. E.g. I set a named range called My Data and when I click on it, it selects the A2 to B10 depending on which sheet is active, how do I do it? TIA KM |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO: Name Range?
Hi Kevin,
Try something like: '============= Public Sub Tester001() Dim SH As Worksheet Const sAdd As String = "A1:A10" For Each SH In ActiveWorkbook.Worksheets Names.Add Name:=SH.Name & "!Data", _ RefersTo:=SH.Range(sAdd) Next SH End Sub '<<============= --- Regards, Norman "Kevin McCartney" wrote in message ... Hi TWIMC, I'd like to add one name range that works on each sheet. E.g. I set a named range called My Data and when I click on it, it selects the A2 to B10 depending on which sheet is active, how do I do it? TIA KM |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO: Name Range?
Define the name and have it referring to the range A2:B10 WITH A LEADING
EXCLAMATION MARK: MyData Refers to !A2:B10 Now you have what I call a global/local name. See page 49 of my book "This isn't Excel it's Magic!" http://www.iil.com/iil/excelmagic "Kevin McCartney" wrote in message ... Hi TWIMC, I'd like to add one name range that works on each sheet. E.g. I set a named range called My Data and when I click on it, it selects the A2 to B10 depending on which sheet is active, how do I do it? TIA KM |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO: Name Range?
OK,
I click on Insert | Name | Define, I type in the Names in workbook, MYDATA, in the Refers to box I put =!A2:B10 click on Add then OK, but nothing shows up in the Name Range List, what am I doing wrong, please help. TIA KM "Bob Umlas" wrote: Define the name and have it referring to the range A2:B10 WITH A LEADING EXCLAMATION MARK: MyData Refers to !A2:B10 Now you have what I call a global/local name. See page 49 of my book "This isn't Excel it's Magic!" http://www.iil.com/iil/excelmagic "Kevin McCartney" wrote in message ... Hi TWIMC, I'd like to add one name range that works on each sheet. E.g. I set a named range called My Data and when I click on it, it selects the A2 to B10 depending on which sheet is active, how do I do it? TIA KM |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO: Name Range?
All named ranges don't show up in the name dropdown, but will appear in
Insert=Names=Define. This includes names that are variable in nature such as this one. In the name box, type in MyData and hit return. does it select the proper range? -- Regards Tom Ogilvy "Kevin McCartney" wrote in message ... OK, I click on Insert | Name | Define, I type in the Names in workbook, MYDATA, in the Refers to box I put =!A2:B10 click on Add then OK, but nothing shows up in the Name Range List, what am I doing wrong, please help. TIA KM "Bob Umlas" wrote: Define the name and have it referring to the range A2:B10 WITH A LEADING EXCLAMATION MARK: MyData Refers to !A2:B10 Now you have what I call a global/local name. See page 49 of my book "This isn't Excel it's Magic!" http://www.iil.com/iil/excelmagic "Kevin McCartney" wrote in message ... Hi TWIMC, I'd like to add one name range that works on each sheet. E.g. I set a named range called My Data and when I click on it, it selects the A2 to B10 depending on which sheet is active, how do I do it? TIA KM |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO: Name Range?
Unfortunately not, the Refers to for some reason changes, and it seems to
select the 10 cells below the active cell when I type in the Name dropdown, not sure what I'm doing wrong. TIA KM "Tom Ogilvy" wrote: All named ranges don't show up in the name dropdown, but will appear in Insert=Names=Define. This includes names that are variable in nature such as this one. In the name box, type in MyData and hit return. does it select the proper range? -- Regards Tom Ogilvy "Kevin McCartney" wrote in message ... OK, I click on Insert | Name | Define, I type in the Names in workbook, MYDATA, in the Refers to box I put =!A2:B10 click on Add then OK, but nothing shows up in the Name Range List, what am I doing wrong, please help. TIA KM "Bob Umlas" wrote: Define the name and have it referring to the range A2:B10 WITH A LEADING EXCLAMATION MARK: MyData Refers to !A2:B10 Now you have what I call a global/local name. See page 49 of my book "This isn't Excel it's Magic!" http://www.iil.com/iil/excelmagic "Kevin McCartney" wrote in message ... Hi TWIMC, I'd like to add one name range that works on each sheet. E.g. I set a named range called My Data and when I click on it, it selects the A2 to B10 depending on which sheet is active, how do I do it? TIA KM |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO: Name Range?
But that's because I need to put !A$2$:B$10$, thanks all
:-) regards KM "Kevin McCartney" wrote: Unfortunately not, the Refers to for some reason changes, and it seems to select the 10 cells below the active cell when I type in the Name dropdown, not sure what I'm doing wrong. TIA KM "Tom Ogilvy" wrote: All named ranges don't show up in the name dropdown, but will appear in Insert=Names=Define. This includes names that are variable in nature such as this one. In the name box, type in MyData and hit return. does it select the proper range? -- Regards Tom Ogilvy "Kevin McCartney" wrote in message ... OK, I click on Insert | Name | Define, I type in the Names in workbook, MYDATA, in the Refers to box I put =!A2:B10 click on Add then OK, but nothing shows up in the Name Range List, what am I doing wrong, please help. TIA KM "Bob Umlas" wrote: Define the name and have it referring to the range A2:B10 WITH A LEADING EXCLAMATION MARK: MyData Refers to !A2:B10 Now you have what I call a global/local name. See page 49 of my book "This isn't Excel it's Magic!" http://www.iil.com/iil/excelmagic "Kevin McCartney" wrote in message ... Hi TWIMC, I'd like to add one name range that works on each sheet. E.g. I set a named range called My Data and when I click on it, it selects the A2 to B10 depending on which sheet is active, how do I do it? TIA KM |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO: Name Range?
Yes, for a fixed range, you should use absolute addressing.
-- Regards, Tom Ogilvy "Kevin McCartney" wrote in message ... But that's because I need to put !A$2$:B$10$, thanks all :-) regards KM "Kevin McCartney" wrote: Unfortunately not, the Refers to for some reason changes, and it seems to select the 10 cells below the active cell when I type in the Name dropdown, not sure what I'm doing wrong. TIA KM "Tom Ogilvy" wrote: All named ranges don't show up in the name dropdown, but will appear in Insert=Names=Define. This includes names that are variable in nature such as this one. In the name box, type in MyData and hit return. does it select the proper range? -- Regards Tom Ogilvy "Kevin McCartney" wrote in message ... OK, I click on Insert | Name | Define, I type in the Names in workbook, MYDATA, in the Refers to box I put =!A2:B10 click on Add then OK, but nothing shows up in the Name Range List, what am I doing wrong, please help. TIA KM "Bob Umlas" wrote: Define the name and have it referring to the range A2:B10 WITH A LEADING EXCLAMATION MARK: MyData Refers to !A2:B10 Now you have what I call a global/local name. See page 49 of my book "This isn't Excel it's Magic!" http://www.iil.com/iil/excelmagic "Kevin McCartney" wrote in message ... Hi TWIMC, I'd like to add one name range that works on each sheet. E.g. I set a named range called My Data and when I click on it, it selects the A2 to B10 depending on which sheet is active, how do I do it? TIA KM |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
HOW TO: Name Range?
Personally I recommend not using Names with refers-to starting with !
because there is an Excel bug that gives the wrong answer (always refers to the active sheet) whenever calculation is called from VBA. Its safer to use =INDIRECT("$A$2:$B$10") instead. Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Bob Umlas" wrote in message ... Define the name and have it referring to the range A2:B10 WITH A LEADING EXCLAMATION MARK: MyData Refers to !A2:B10 Now you have what I call a global/local name. See page 49 of my book "This isn't Excel it's Magic!" http://www.iil.com/iil/excelmagic "Kevin McCartney" wrote in message ... Hi TWIMC, I'd like to add one name range that works on each sheet. E.g. I set a named range called My Data and when I click on it, it selects the A2 to B10 depending on which sheet is active, how do I do it? TIA KM |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
| Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
| Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
| Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
| how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming | |||
Linear Mode
