Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I'm trying to build a drop down list using the values in a range of cells on
the same sheet. I Select about 150 cells and then using the Add method of the Validation object to create the validation on this range of cells. The code runs and all 150 cells now have a drop down list. The problem is that the drop down list for the top cell in the range has a complete list of values. As you go down the list of cells and click the drop down list, the list of values gets shorter and shorter until finally it's blank when you get about half way down the list of 150 cells. The point at which the drop down list comes up blank seems to correlate to the number of cells in the range used as the Formula1 in .Add method. anyone seen this before or know what's going on? Love some suggestions. thanks -- D.Farns |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi
Let's assume you have list values in range X1:X10 You defined your list using relative reference like =$X1:$X10 Now, on row 2 the reference will be =$X2:$X11 , on row 3 =$X3:$X12 , on row 11 =$X11:$X20 As you can see, the referred range 'moves off' the range with your list. To avoid this, use absolute reference instead =$X$1:$X$10 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "D.Farns" wrote in message ... I'm trying to build a drop down list using the values in a range of cells on the same sheet. I Select about 150 cells and then using the Add method of the Validation object to create the validation on this range of cells. The code runs and all 150 cells now have a drop down list. The problem is that the drop down list for the top cell in the range has a complete list of values. As you go down the list of cells and click the drop down list, the list of values gets shorter and shorter until finally it's blank when you get about half way down the list of 150 cells. The point at which the drop down list comes up blank seems to correlate to the number of cells in the range used as the Formula1 in .Add method. anyone seen this before or know what's going on? Love some suggestions. thanks -- D.Farns |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Arvi, thanks for the quick response. I ended up addressing the problem by
looping through the range of cells and adding the drop downs one at a time rather than adding to the selected range. Not sure which is more efficient, but I suspect looping is better programming practice than selecting a range anyway. It's nice to know what was causing the issue none the less. Being cognisant of when relative vs absolute references make a difference is essential. Thanks for the reminder! -- D.Farns "Arvi Laanemets" wrote: Hi Let's assume you have list values in range X1:X10 You defined your list using relative reference like =$X1:$X10 Now, on row 2 the reference will be =$X2:$X11 , on row 3 =$X3:$X12 , on row 11 =$X11:$X20 As you can see, the referred range 'moves off' the range with your list. To avoid this, use absolute reference instead =$X$1:$X$10 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "D.Farns" wrote in message ... I'm trying to build a drop down list using the values in a range of cells on the same sheet. I Select about 150 cells and then using the Add method of the Validation object to create the validation on this range of cells. The code runs and all 150 cells now have a drop down list. The problem is that the drop down list for the top cell in the range has a complete list of values. As you go down the list of cells and click the drop down list, the list of values gets shorter and shorter until finally it's blank when you get about half way down the list of 150 cells. The point at which the drop down list comes up blank seems to correlate to the number of cells in the range used as the Formula1 in .Add method. anyone seen this before or know what's going on? Love some suggestions. thanks -- D.Farns |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi
Btw., why don“t you like named range as validation list source? I myself use validation lists quite frequently - and mostly with named range as source (and there again mostly dynamic named ranges). In Excel, less code is almost always a best solution. Arvi Laanemets "D.Farns" wrote in message ... Arvi, thanks for the quick response. I ended up addressing the problem by looping through the range of cells and adding the drop downs one at a time rather than adding to the selected range. Not sure which is more efficient, but I suspect looping is better programming practice than selecting a range anyway. It's nice to know what was causing the issue none the less. Being cognisant of when relative vs absolute references make a difference is essential. Thanks for the reminder! -- D.Farns "Arvi Laanemets" wrote: Hi Let's assume you have list values in range X1:X10 You defined your list using relative reference like =$X1:$X10 Now, on row 2 the reference will be =$X2:$X11 , on row 3 =$X3:$X12 , on row 11 =$X11:$X20 As you can see, the referred range 'moves off' the range with your list. To avoid this, use absolute reference instead =$X$1:$X$10 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "D.Farns" wrote in message ... I'm trying to build a drop down list using the values in a range of cells on the same sheet. I Select about 150 cells and then using the Add method of the Validation object to create the validation on this range of cells. The code runs and all 150 cells now have a drop down list. The problem is that the drop down list for the top cell in the range has a complete list of values. As you go down the list of cells and click the drop down list, the list of values gets shorter and shorter until finally it's blank when you get about half way down the list of 150 cells. The point at which the drop down list comes up blank seems to correlate to the number of cells in the range used as the Formula1 in .Add method. anyone seen this before or know what's going on? Love some suggestions. thanks -- D.Farns |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Arvi, no reason really. I'm still a bit of a "newby" in the Excel object
model. I'm using named ranges elsewhere and found to be a pain redefining the name when it changes is size. I do see that using Names is preferred/recommended often. I'll start to use more often as they appear to have benefits I could use. Perhaps they'll help with another issue I'm having with AdvancedFilter. Just posted it. -- D.Farns "Arvi Laanemets" wrote: Hi Btw., why donĀ“t you like named range as validation list source? I myself use validation lists quite frequently - and mostly with named range as source (and there again mostly dynamic named ranges). In Excel, less code is almost always a best solution. Arvi Laanemets "D.Farns" wrote in message ... Arvi, thanks for the quick response. I ended up addressing the problem by looping through the range of cells and adding the drop downs one at a time rather than adding to the selected range. Not sure which is more efficient, but I suspect looping is better programming practice than selecting a range anyway. It's nice to know what was causing the issue none the less. Being cognisant of when relative vs absolute references make a difference is essential. Thanks for the reminder! -- D.Farns "Arvi Laanemets" wrote: Hi Let's assume you have list values in range X1:X10 You defined your list using relative reference like =$X1:$X10 Now, on row 2 the reference will be =$X2:$X11 , on row 3 =$X3:$X12 , on row 11 =$X11:$X20 As you can see, the referred range 'moves off' the range with your list. To avoid this, use absolute reference instead =$X$1:$X$10 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "D.Farns" wrote in message ... I'm trying to build a drop down list using the values in a range of cells on the same sheet. I Select about 150 cells and then using the Add method of the Validation object to create the validation on this range of cells. The code runs and all 150 cells now have a drop down list. The problem is that the drop down list for the top cell in the range has a complete list of values. As you go down the list of cells and click the drop down list, the list of values gets shorter and shorter until finally it's blank when you get about half way down the list of 150 cells. The point at which the drop down list comes up blank seems to correlate to the number of cells in the range used as the Formula1 in .Add method. anyone seen this before or know what's going on? Love some suggestions. thanks -- D.Farns |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi
"D.Farns" wrote in message ... Arvi, no reason really. I'm still a bit of a "newby" in the Excel object model. I'm using named ranges elsewhere and found to be a pain redefining the name when it changes is size. I do see that using Names is This is for what dynamic named ranges are used. An example: You have a list on sheet MyList (list header in cell A1, list values starting from A2, without any gaps). Define named range Selections as =OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$A)-1,1) Whenever you add entries to list, or remove some, the named range adjusts automatically. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I think I get it. I'll play around with. I can see where it would cut down
on code and worth while to use. Thanks very much. -- D.Farns "Arvi Laanemets" wrote: Hi "D.Farns" wrote in message ... Arvi, no reason really. I'm still a bit of a "newby" in the Excel object model. I'm using named ranges elsewhere and found to be a pain redefining the name when it changes is size. I do see that using Names is This is for what dynamic named ranges are used. An example: You have a list on sheet MyList (list header in cell A1, list values starting from A2, without any gaps). Define named range Selections as =OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$A)-1,1) Whenever you add entries to list, or remove some, the named range adjusts automatically. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Arvi, thanks for posting this helpful 'trick' - a named range that adjusts
automatically! As one of the characters on the Guiness Stout television commercials (running in California) would say: "BRILLIANT!!" -- rpw "Arvi Laanemets" wrote: An example: You have a list on sheet MyList (list header in cell A1, list values starting from A2, without any gaps). Define named range Selections as =OFFSET(MyList!$A$1,1,,COUNTA(MyList!$A:$A)-1,1) Whenever you add entries to list, or remove some, the named range adjusts automatically. -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Drop down list for a single column | Excel Discussion (Misc queries) | |||
Data Validation using List (But needs unique list in drop down lis | New Users to Excel | |||
match and count words | Excel Worksheet Functions | |||
changing value of a cell by selecting an item from a drop down list | Excel Worksheet Functions | |||
drop down list multiple columns | Excel Discussion (Misc queries) |