Data Validation Dropdown List to Display only those values from the source database which haven't yet been entered in previous rows.

x0nar

New Member
Joined
May 10, 2016
Messages
33
Hi there.
I was wondering if it was possible to create a custom data validation rule such that the dropdown list displays only those entries which haven't been entered in the previous rows.

For Example

Database
In Sheet having name "Source_Sheet", contains a list of entries for ID No.s of various customers.


Cells C2:C100 contains a list of ID Numbers. ie. 01 to 99. This list will appear as a dropdown menu in Entry Sheet.


Entry Sheet

In Sheet having name "Entry_Sheet", the data entry operator enters details pertaining to customers

Column B:B is specifically meant for entering ID. However it is expected of the user to select from the dropdown list to avoid errors.
The main emphasis is on the fact that one user ID can be entered only once.
Hence to ease the operator's functions, I was wondering if it was possible to ignore entries in the drop down list of column B if it has been previously entered.

So Suppose,
If in the cell B1 the dropdown list should display all values ie. 01-99. Now assume the user selects 23 from the list.
Now when he goes to cell B2, all values except 23 should be displayed in the dropdown list. Now from the above list assume the user selects 54.
Next when he goes to cell B3, all values except 23 and 54 should be displayed, so on and so forth.

I was wondering if doing something like this would be possible.

Thanks for your help.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Watch MrExcel Video

Forum statistics

Threads
1,127,626
Messages
5,625,950
Members
416,144
Latest member
JohnMacDonald

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
Top