XLS data validation question

**tirvin** · 03-10-2010, 04:38 AM

I recently learned how to use data validation under the XLS Data tab and create a selection list. I would like to create a list, "drop down" for selection of text options and still be able to manually enter text in the cell. Is this possible? Can someone tell me how? Thank you.

**rwgrietveld** · 03-10-2010, 04:46 AM

A list is a list, but the list itself can be made dynamic.

e.g.
Option 1) AA, BB, CC will give you a selection option of these three
Option 2) =A1:A3 will give you the selection option of the content in A1:A3

An alternative to option 2 is to make a Name which refers to A1:A3, the result is the same but a Name can also contain a formula AND a name can be used throughout the Workbook (on differenent sheets).

Most often a Name is used that has a construction like:
=OFFSET(Sheet1!$A$1,1,0,Counta(Sheet1$A:A$)-1,1)

What this does is returm a list of values from A1 till Ax, where Ax is the cell before the first blank cell (No gaps allowed)

If you would like to extend your list, simple add a listitem to the first blank cell of the currrent list. e.g. A1:A3 contain 'AA' , 'BB', 'CC', then add 'DD' in A4.

**tirvin** · 03-10-2010, 09:59 AM

now that i have someone willing to help, let me give you a little more info. i elvaluate maintenance programs for aviation and the current course of action is to type the fault found again and again on an un-user friendly word doc. i believe i could create the same faults (sentences) and select them from a drop down and save time. i can do that with data validation, however i cannot do that and type text of my choice in the same cell. i think it important to note the xls will be different from one program inspection to the other, and that it will be one that has rows added and removed depending on the inspection results. the xls format will stay the same, but the amount of faults subject will change. it is possible the path i am on is not the right answer. is there an alternative to this? a list from another sheet? thanks.

**rwgrietveld** · 03-10-2010, 11:11 AM

Tirvin,

I your case I think the hole point is to have clasified "types of faults". Then you could do analysis later on, besides the fact that you don't have to retype.

Not that the ValList should be in the same Workbook, but not necessarily in the same Worksheet as in the attachement.

**tirvin** · 03-11-2010, 05:54 AM

i guess i need all this in laymans terms. i kinda get it, but not.
the simple val list isn't a viable option, i.e. fault 1, fault 2. the list i would like to select from would be in the form of sentences with details.

see the attached xls, i get the list thing and option 1 and 2 you described earlier (see A1 and B1 cells)

the sticking point to all of this is i still need to enter text to that same cell with the list.

is there a formula or set of values that equal the ability to type in a cell with a list that could be inserted as a choice?

i tried to type in the cell after selecting a list choice but get the warning banner in the attched word doc.