Data Entry Form with Excel Table and Vlookup

In this post, we will learn how to create Data Entry form and use Excel Table to store the data. We will also learn how to use in Lookup Functions in Form to fetch some default or base data to reduce the data entry task.

Let me explain the three different sheets used in this data entry application.

Form sheet is to enter the data and transfer it to database sheet in a Table.


Second sheet is supporting data to fetch some default data e.g. Name and Gender.  User just needs to enter Emp ID in data entry form and form will automatically filled with Name and Gender.

Supporting Sheet

The third sheet is Database. In Database sheet, I have created a table name ‘EmpTable’ with headers e.g. Emp ID, Emp Name, Gender, Department, CTC, Submitted On and Submitted By.

Database sheet with EmpTable

To design this automated data entry form, just create these three different sheets with form, supporting data and Employee Table (EmpTable).

Once you create all these sheets, press F+11 to open VBE window.

Insert a blank module in this project.

Now move to Sheet1 (Form) code and write the below code to update Employee Name and Gender with lookup function.

Now, move to Module1 and write the below Sub Procedure to Transfer the data to EmpTable.

Now we have done with Trasfer Sub Procedure. Let us create a Sub Procedure so that it can be assigned on Transfer button in Data Entry Form.

Let us create another Sub Procedure to Reset the Form.

Now we have done with all the required codes. Let us move to Excel window and assign the Macros on Transfer and Reset button.

On Transfer button, assign the macro ‘TransferToTable’ and on Reset button, assign the ‘Reset’ procedure.

Please watch the complete tutorial on YouTube.

Click on below button to download the Excel file used in this tutorial.

Click to download

Please visit to our other data entry tutorial pages.


Please enter your comment!
Please enter your name here