Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Range Limitations
Hello,
I hope someone can help me solve this problem. What is the limit on how many non adjacent cells/ranges can be saved in a Named Range? Sometimes MS Excel 2003 will allow me to have 16 non-adjacent cells and other times it will let me do just 11. Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Range Limitations
The limit in the Define Name dialog's Refers To box is 255 characters. So
the number of areas a name created using it can have depends on their addresses and the length of the sheet's name. -- Jim "Bagia" wrote in message ... | Hello, | | I hope someone can help me solve this problem. What is the limit on how | many non adjacent cells/ranges can be saved in a Named Range? Sometimes MS | Excel 2003 will allow me to have 16 non-adjacent cells and other times it | will let me do just 11. | | Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Range Limitations
Maybe you can try this:
Select your range (click and ctrl-click as much as necessary) Type the name in the name box (to the left of the formula bar) (remember to hit enter when you're done) If that doesn't work, one more test. Select your range again hit alt-f11 to get to the VBE (where macros live) hit ctrl-g to see the immediate window type this: Selection.name = "TheNameYouWantToUseHere" Then back to excel to test it out. Bagia wrote: Hello, I hope someone can help me solve this problem. What is the limit on how many non adjacent cells/ranges can be saved in a Named Range? Sometimes MS Excel 2003 will allow me to have 16 non-adjacent cells and other times it will let me do just 11. Thank you. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Range Limitations
As a workaround, note that you can use the union operator (,) to break
your ranges into smaller units: Name in workbook: myrange1 Refers to: =Sheet1!$A$1:$F$1 Name in workbook: myrange2 Refers to: =Sheet1!$J$10:$K$15 Name in workbook: myrange3 Refers to: =(myrange1,myrange2) In article , "Jim Rech" wrote: The limit in the Define Name dialog's Refers To box is 255 characters. So the number of areas a name created using it can have depends on their addresses and the length of the sheet's name. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Range Limitations
Hello,
Thanks for the responses. I understand and know how to create Named Ranges. My question is...is there a limitation to the number of cells you can have, because I used click and ctrl-click for about 19 non-adjacent cells and was able to create a Named Range. Then a few minutes later I wanted to create another named range, but this time it will only allow me to use 11 non-adjacent cells. My co-worker also run into this problem as well. We are using MS Excel 2003. I will try Dave's 2nd suggestion. Does anyone know if there's a glitch with version 2003 when creating Named Range? Thanks again "Dave Peterson" wrote: Maybe you can try this: Select your range (click and ctrl-click as much as necessary) Type the name in the name box (to the left of the formula bar) (remember to hit enter when you're done) If that doesn't work, one more test. Select your range again hit alt-f11 to get to the VBE (where macros live) hit ctrl-g to see the immediate window type this: Selection.name = "TheNameYouWantToUseHere" Then back to excel to test it out. Bagia wrote: Hello, I hope someone can help me solve this problem. What is the limit on how many non adjacent cells/ranges can be saved in a Named Range? Sometimes MS Excel 2003 will allow me to have 16 non-adjacent cells and other times it will let me do just 11. Thank you. -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Range Limitations
You haven't addressed the suggestion in a previous response as to
whether you're exceeding the 255 character limit for a defined name. Since for each non-adjacent cell, the defined name will include the sheet name, a ! character and the address, a small change in sheet name length can significantly change the maximum number of cells in a named range. In article , Bagia wrote: Thanks for the responses. I understand and know how to create Named Ranges. My question is...is there a limitation to the number of cells you can have, because I used click and ctrl-click for about 19 non-adjacent cells and was able to create a Named Range. Then a few minutes later I wanted to create another named range, but this time it will only allow me to use 11 non-adjacent cells. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Range Limitations
I definitely did not exceed the 255 character limit. Sorry for not addressing
the suggestion. "JE McGimpsey" wrote: You haven't addressed the suggestion in a previous response as to whether you're exceeding the 255 character limit for a defined name. Since for each non-adjacent cell, the defined name will include the sheet name, a ! character and the address, a small change in sheet name length can significantly change the maximum number of cells in a named range. In article , Bagia wrote: Thanks for the responses. I understand and know how to create Named Ranges. My question is...is there a limitation to the number of cells you can have, because I used click and ctrl-click for about 19 non-adjacent cells and was able to create a Named Range. Then a few minutes later I wanted to create another named range, but this time it will only allow me to use 11 non-adjacent cells. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Range Limitations
Just to make it clear...
I think that the only limitation that Jim Rech, JE McGimpsey and I have seen is the length of the string that makes up those addresses. It sure looks like it could be easy to exceed 255 characters by pointing and clicking. Bagia wrote: Hello, Thanks for the responses. I understand and know how to create Named Ranges. My question is...is there a limitation to the number of cells you can have, because I used click and ctrl-click for about 19 non-adjacent cells and was able to create a Named Range. Then a few minutes later I wanted to create another named range, but this time it will only allow me to use 11 non-adjacent cells. My co-worker also run into this problem as well. We are using MS Excel 2003. I will try Dave's 2nd suggestion. Does anyone know if there's a glitch with version 2003 when creating Named Range? Thanks again "Dave Peterson" wrote: Maybe you can try this: Select your range (click and ctrl-click as much as necessary) Type the name in the name box (to the left of the formula bar) (remember to hit enter when you're done) If that doesn't work, one more test. Select your range again hit alt-f11 to get to the VBE (where macros live) hit ctrl-g to see the immediate window type this: Selection.name = "TheNameYouWantToUseHere" Then back to excel to test it out. Bagia wrote: Hello, I hope someone can help me solve this problem. What is the limit on how many non adjacent cells/ranges can be saved in a Named Range? Sometimes MS Excel 2003 will allow me to have 16 non-adjacent cells and other times it will let me do just 11. Thank you. -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Named Range Limitations
As you point and click, Excel helpfully adds the sheetname to each cell thus
adding a lot of characters and you soon reach the limit of 255. You can type the addresses into the refer to dialog box....comma separated.......then hit F2, select the cells in the dialog box and use F4 to turn them to Absolute references. You can get about 45 cells in a named range. Gord Dibben MS Excel MVP On Wed, 02 May 2007 12:33:36 -0500, Dave Peterson wrote: Just to make it clear... I think that the only limitation that Jim Rech, JE McGimpsey and I have seen is the length of the string that makes up those addresses. It sure looks like it could be easy to exceed 255 characters by pointing and clicking. Bagia wrote: Hello, Thanks for the responses. I understand and know how to create Named Ranges. My question is...is there a limitation to the number of cells you can have, because I used click and ctrl-click for about 19 non-adjacent cells and was able to create a Named Range. Then a few minutes later I wanted to create another named range, but this time it will only allow me to use 11 non-adjacent cells. My co-worker also run into this problem as well. We are using MS Excel 2003. I will try Dave's 2nd suggestion. Does anyone know if there's a glitch with version 2003 when creating Named Range? Thanks again "Dave Peterson" wrote: Maybe you can try this: Select your range (click and ctrl-click as much as necessary) Type the name in the name box (to the left of the formula bar) (remember to hit enter when you're done) If that doesn't work, one more test. Select your range again hit alt-f11 to get to the VBE (where macros live) hit ctrl-g to see the immediate window type this: Selection.name = "TheNameYouWantToUseHere" Then back to excel to test it out. Bagia wrote: Hello, I hope someone can help me solve this problem. What is the limit on how many non adjacent cells/ranges can be saved in a Named Range? Sometimes MS Excel 2003 will allow me to have 16 non-adjacent cells and other times it will let me do just 11. Thank you. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Can I use named range in data range box when creating pie chart? | Charts and Charting in Excel | |||
function cell range limitations | Excel Worksheet Functions |