List box question

[BMWE65]

Hi all,

I have a drop down list in my spreadsheet, showing the following car makes:

BMW
Toyota
Honda

I have these 3 makes, as these are the most common makes I work with, but very occasionally, I deal with other car makes.

I just wanted to if it's possible to be able to type in a different car make into the cell, such as Audi for example? I dont want to add this, or future car makes to the list.

When I type a different car make into the cell, such as 'Audi', and press enter, I get a message saying
'The value you entered is not valid. A user has restricted values that can be entered into this cell'.

So I just want to know if it is possible to be able to type something into the cell, which already has a list box in it.

Hope that makes sense!

Thanks all

 

[Akashwani]

Hi,

I don't think that's possible as you have pre determined in Data Validation that the cell contents can only come from a List.

You could always change your Data Validation list to one that will automatically expand as you add new data to your list. If the data for your list is in cells A2:A4 of sheet1, highlight the range, right click, Name a Range, give it a title then in the Refers to box put this formula....

=OFFSET(Sheet1!$A$2,0,0,MATCH("*",Sheet1!$A:$A,-1),1)

Good luck

Ak

 

[doofusboy]

So I just want to know if it is possible to be able to type something into the cell, which already has a list box in it.

The answer is yes, if you do the following. Select the cell with data validation. Select Data Validation from the menu. Select the 'Error Alert' tab of the dialog that pops up and UNCHECK the box labeled: Show error alert after invalid data is entered.

You'll now have use of the list but you'll also be able to type non-list items too.

 

[Akashwani]

Hey doofusboy

Thanks for that little tip, I'm going to have to change about a dozen different Data Validation settings now

Thanks

Ak

 

[doofusboy]

Sorry about that Akashwani [I think], but there are valid uses for both setups.

Use the error message when you absolutely want to restrict user to what's in list; no error when you want more flexibility.

 

[BMWE65]

The answer is yes, if you do the following. Select the cell with data validation. Select Data Validation from the menu. Select the 'Error Alert' tab of the dialog that pops up and UNCHECK the box labeled: Show error alert after invalid data is entered.

You'll now have use of the list but you'll also be able to type non-list items too.

That is absolutely perfect!!
Thank you so much!