Limit the input can be enter into a cell

owens_pastor

New Member
Joined
Feb 21, 2002
Messages
6
I have a list input allowed to enter into a cell, how do you enforce that in excel (without messy vlookup or combo box)
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top