Check data entry in Excel

Regarding checking data entry in Excel in addition to using VBA, there is another simpler way that is using Data Validation tool of Excel.

For example, when checking product codes, suppose you have a product code contained in the range A1: A20 of Sheet1 , the item code entry part in range A1: A10 in Sheet2 .

To make it easier to manage this range, name it by selecting Insert à Name à Define , naming the Sheet1! A1: A20 area as Mahang , clicking OK .

Check data entry in Excel Picture 1

To enter the test conditions for this region, do the following:

Go to Sheet2 à Select area A1: A10 and Select Data and Data Validation .

On the Settings tab , Validation Criteria select Allow : List , Source enter = Mahang , check the Ignore Blank box if you want to ignore the empty boxes, In-cell dropdown box if you want a drop-down list box.

Switch to the Input Messsage tab, enter the instructions, select Show input . if you want the instructions to appear when the cell is selected.

Switching to the Error Alert tab enter the warning information when it is entered incorrectly, select Show error . if you want to show the warning information.

Click OK .

Check data entry in Excel Picture 2

Check data entry in Excel Picture 3

Check data entry in Excel Picture 4

Now when entering data in Sheet2! A1: A10 area , a Dropdown List box appears and almost certainly you don't have a chance to enter the wrong code!

Check data entry in Excel Picture 5

Check data entry in Excel Picture 6

In addition, you can refer to other advanced data entry techniques at the following websites:

- English: www.contextures.com/tiptech.html, download the example file www.contextures.com/DataValSample.zip

- Vietnamese: www.webketoan.com, www.giaiphapexcel.com (http://www.giaiphapexcel.com/forum/showthread.php?t=98&highlight=Data+Validation).

Nguyen Trong Thanh
Email : ng.tr.thanh@gmail.com; ntthanhhp@vnn.vn