Edit validation list source

[Ken G.]

I have a sheet which uses data validation lists to select a name from the
list depending in a previously selected qualifier (a State). The validation
then displays the list for that state, so the actual source entry is quite
long, having a separate range for each state.

Editing the souce is very difficult due to the small size of the window and
if you try to use the arrow keys to move backwards or forwards, it inserts
cell references which tend to get lost in the range references. I could use
range names instead of cell references, but is there a way to either enlarge
the source window, or move backwards and forwards in some way?

 

[Ragdyer]

The work-around for this is to increase the zoom on the sheet, allow the
selection to be made, then return the zoom to normal.
There is code for this at Debra Dalgleish's web site:

http://www.contextures.com/xlDataVal08.html#Larger

 

[Debra Dalgleish]

If you press the F2 key, you can then use the arrow keys to navigate in
the text box.

However, you may find it easier to use dependent data validation lists.
There are instructions here:

http://www.contextures.com/xlDataVal02.html

 

[Ken G.]

Thanks Debra. The dependent data validation is the bees knees. Thanks for
this. I've converted the whole thing over so that the list source is now
"=indirect(xx)" instead of a huge list of data ranges. Thanks also for the F2
tip.