Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
D.Farns
 
Posts: n/a
Default Drop down list shrinks as you go down column

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   Report Post  
Posted to microsoft.public.excel.misc
Arvi Laanemets
 
Posts: n/a
Default Drop down list shrinks as you go down column

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   Report Post  
Posted to microsoft.public.excel.misc
D.Farns
 
Posts: n/a
Default Drop down list shrinks as you go down column

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   Report Post  
Posted to microsoft.public.excel.misc
Arvi Laanemets
 
Posts: n/a
Default Drop down list shrinks as you go down column

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   Report Post  
Posted to microsoft.public.excel.misc
D.Farns
 
Posts: n/a
Default Drop down list shrinks as you go down column

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   Report Post  
Posted to microsoft.public.excel.misc
Arvi Laanemets
 
Posts: n/a
Default Drop down list shrinks as you go down column

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   Report Post  
Posted to microsoft.public.excel.misc
D.Farns
 
Posts: n/a
Default Drop down list shrinks as you go down column

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   Report Post  
Posted to microsoft.public.excel.misc
RPW RPW is offline
external usenet poster
 
Posts: 52
Default Drop down list shrinks as you go down column

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Drop down list for a single column AJ Excel Discussion (Misc queries) 1 February 21st 06 10:38 PM
Data Validation using List (But needs unique list in drop down lis Tan New Users to Excel 1 July 8th 05 04:32 PM
match and count words David Excel Worksheet Functions 5 July 4th 05 03:24 AM
changing value of a cell by selecting an item from a drop down list Bobby Mir Excel Worksheet Functions 6 June 8th 05 09:33 PM
drop down list multiple columns c Excel Discussion (Misc queries) 9 January 27th 05 04:13 PM


All times are GMT +1. The time now is 05:16 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"