Limit the input can be enter into a cell

[owens_pastor]

I have a list input allowed to enter into a cell, how do you enforce that in excel (without messy vlookup or combo box)

 

[just_jon]

Do you want to a] limit the allowable input to a list of possible entries, or b] limit the input to any entry as long as it is of a given length?

 

[owens_pastor]

a)

 

[just_jon]

On some sheet create a listing down a single column of all available valid entries. You will be able to add and delete from this list as needed.

For this, I'm assuming the list is Text values and is entered in Sheet2, column A starting in row 1.

Now click Insert | Name | Define

In the Names in workbook box, enter the name you want to refer to this list by - cannot include any spaces. I'll assume here you chose MyList

In the Refers to box, enter:

=INDEX(Sheet2!$A:$A,1,0):INDEX(Sheet2!$A:$A,MATCH(REPT("z",255),Sheet2!$A:$A),0)

Click OK.

Now go to the sheet and cell in which you want to restrict data entry to the choices from that list and select the cell.

Now click Data | Validation

In the allow box, select List.

In the Source box, enter: =MyList

Check ignore blank, check In-cell dropdown.

On the Error Alert tab, select Stop in the Style box. Add any titles/messages you want on that tab and on the Input Message tab.

Click OK.

From now on, your entry will be restricted to values from Sheet2 column A.

 

[owens_pastor]

just_jon
thanks a bundle, it works