Can Validation against a list do auto-fill in a cell?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm an Excel novice, so forgive a probably basic question.

I set up a spreadsheet where a set of cells does Validation against a list.
I did this by doing the following:

1) On Worksheet 1, I made a list of values (e.g. Red, Blue, Green).
2) I then selected that list and gave it a Name.
3) On Worksheet 2, I selected a set of cells, and choose to Validate those
cells against the Named list.

So that's great. In Worksheet 2, each cell now has a dropdown arrow where I
can choose a List value from Worksheet 1.

But what I want is to start typing into a cell in Worksheet 2 and have the
value auto-fill from the Worksheet 1 list. For example, if I type "r", I
want "Red" to auto-fill in that cell.

Can I do this? If not, MSFT, this is a feature request.

Thanks.

Dave
 
Hi Dave,

I think the best that can be done is to make use of Excel's existing
AutoComplete option (Tools|Options|Edit|Allow AutoComplete for cell
values is ticked).

You could insert 3 rows at the top of the sheet, and in each column
that you are wanting the behaviour you have described, those three top
rows could have Red in one cell, Blue in another and Green in the
third.You could then hide rows 1 to 3.
If the "Enable AutoComplete for cell values" option on the "Edit" tab
of the "Options" dialog is selected, then you should get the desired
result.

I've checked that it works in conjunction with your Validation.

Ken Johnson
 
If your drop-down list is from Data Validation, you cannot have Autocomplete.

Try a combo-box drop-down list.

Debra Dalgleish has instructions.

http://www.contextures.on.ca/xlDataVal10.html


Gord Dibben MS Excel MVP


I'm an Excel novice, so forgive a probably basic question.

I set up a spreadsheet where a set of cells does Validation against a list.
I did this by doing the following:

1) On Worksheet 1, I made a list of values (e.g. Red, Blue, Green).
2) I then selected that list and gave it a Name.
3) On Worksheet 2, I selected a set of cells, and choose to Validate those
cells against the Named list.

So that's great. In Worksheet 2, each cell now has a dropdown arrow where I
can choose a List value from Worksheet 1.

But what I want is to start typing into a cell in Worksheet 2 and have the
value auto-fill from the Worksheet 1 list. For example, if I type "r", I
want "Red" to auto-fill in that cell.

Can I do this? If not, MSFT, this is a feature request.

Thanks.

Dave
Click to expand...

Gord Dibben MS Excel MVP
 
Gord said:
If your drop-down list is from Data Validation, you cannot have Autocomplete.
Click to expand...

Hi Gord,

I'm using Xl2003 and managed to get both Data Validation and
AutoComplete to work.

Ken Johnson
 
If the DV dropdown is directly below or above your list range......yes.

But not very practical in the real world IMO where most users will have a list
range on another sheet or another column.


Gord
 
Gord said:
If the DV dropdown is directly below or above your list range......yes.

But not very practical in the real world IMO where most users will have a list
range on another sheet or another column.
Click to expand...

Hi Gord,

The list range can be anywhere, you just have to have a copy of the
list range at the top of each column in which you also want
AutoComplete, and I suggested hiding those top rows.

Ken Johnson
 
Back
Top