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

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


On Thu, 5 Oct 2006 18:47:02 -0700, pastrami_dave
wrote:

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

Gord Dibben MS Excel MVP

Gord Dibben wrote:
If your drop-down list is from Data Validation, you cannot have Autocomplete.

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

On 5 Oct 2006 21:53:36 -0700, "Ken Johnson" wrote:


Gord Dibben wrote:
If your drop-down list is from Data Validation, you cannot have Autocomplete.

Hi Gord,

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

Ken Johnson

Gord Dibben wrote:
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.

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