HomeApplicationOffice informationCheck data entry in Excel

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

5 ★ | 1 Vote
datacomwwwenter

You should read it

May be interested

  • 10 quick operations with MS OfficePhoto of 10 quick operations with MS Office
    office is a well-known office suite that has made a name for microsoft. this software suite is very big, office users only use less than 15% of the software suite, up to this 245 mb.
  • Manually print shortcuts in MS WordPhoto of Manually print shortcuts in MS Word
    in order for readers to increasingly use the microsoft office tools in the most effective way, tipsmake.com will provide a series of tutorials on simple but effective tricks of the two most popular programs in this popular office suite: ms word and ms excel
  • Open and save Word documentsPhoto of Open and save Word documents
    for those who are too familiar with informatics, these operations are too simple, if it is said, it is 'not worth'. but for those who are new to informatics especially office, you also have to struggle
  • Explore the MS Word toolbarPhoto of Explore the MS Word toolbar
    the following tips will help you customize the microsoft word environment in order to increase the usefulness of toolbars and speed up your work.
  • Print text filePhoto of Print text file
    you want to print your own text file with a printer already available at home, but don't know how to install and use it? tipsmake.com will guide you how to install the printer directly with the current computer, how to print in ms word and cancel the print job.
  • Text file formatPhoto of Text file format
    many times you are struggling to know how to shoot a horizontal page in a whole page of text. or every time you create a new file you have to reformat the text for the page even though the previous and next margin sizes do not change, etc. tipsmake.com will guide you on how to format the page margins, rotate the horizontal page, vertical page

Office information