Make an Advanced Data Entry Form in Excel (Fully Automated)

How to make an automated data entry form in Excel.

In this video, you'll learn to make an advanced data entry form in Excel. This will include input areas, dependent dropdowns, and automated error messages. When a user submits the form, it sends an automatic email with the Excel file attached, and stores the data in the dashboard tab summarizing key metrics like employee count, average salary, or average age. Making the registration form is done with Excel formatting, data validation for dropdowns, and a nested IF statement with an ISTEXT error checker. The data collection is done with an Excel table, and it has KPI's in the form of shapes up top. These use formulas such as COUNTA, DATEDIF, or COUNTIF to make them dynamic. The automation is done using Macros, and later using Visual Basic to write the code for sending the email notification. Finally, we'll protect the form submission worksheet to prevent accidental changes.

LEARN:

▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬

Chapters:
0:00​ -​ Registration Form
5:48​ - Dashboard
10:26​ - Automate Form
14:15​ - Email Notification
16:50​ - Protect File

[the_awakening_msaha]

What an amazing instructor we have got! Thanks Kenji!! 😊

[brimstone]

They need to invent a Super like button for videos like this.

[avierez]

As always, it's a pleasure to watch your videos and learn a lot from them.

[vriesbelligos]

Thank you Kenji. Can you do a tutorial about stock management with opening, in, damage out feature :)

[samuel-vanq.]

Thank youi very much for your help ! I had like to know if there is a way to input for example for the role new ones if there is not the one we want and next time we come, there will be also the new role ? Thank you again. I hope that you will find the time for my question !😄

[abdulrahmankiarie]

wow kenji does it again.! salute from kenya

[michellegill]

Kenji, this is a great video. Thanks for sharing. Do you have a video that allows input from a form to an Excel table and then automatic completion of a Word or PDF document that can be previewed and then printed? The Word document should be automatically created without mail merge.

[manamedia]

As always Kenji, AMAZING!! Thank you so much. Is there a way to use the Form to edit data in the Dashboard? As in, a record recall function or something to that effect?

[kenshijavi]

This is great info. What if you wanted to add an option for finding a specific employee and update the information?

[m.s.]

Hi Kenji, great video, thank you very much. Once users receive this FORM created from excel file with macros, will they need to allow macros first?

[jacksonmubacia]

Super Great tutorial... Now, does it mean I have to download all filled forms from my email address so as to combine them and do data analysis?

[Juno_Rx_VODS]

This is an amazing video! However, is it possible to update already existing information?

[ibrahim_akanni]

Kenji for the win!!!! Great job you're doing

[triplex]

Awesome. This is so useful to collect data. Thank you.

[jacksonmubacia]

Thanks so much. Super great tutorial. However, I have a problem with the VBA code. Do I need to have logged in in Outlook or I can just use Gmail?

[charlesajayi]

Great Video. Thank you. Please, permit me to ask if I can achieve the same thing with VLOOKUP if yes, how? Because I run an older version of Excel.

[rajeshkanadiya]

Hi Kenji, Thanks for this video. How can I automate Invoice details where I have one Invoice with 3 to 5 product line items in each invoice. I am not able to automate this, because it is getting overwritten on the same cell. Could you please make video for Invoice automation with multiple line item. ? Thank you.

[rafayhussain]

Keep it up sir as always video is great keep growing

[ravinduakalanka]

What is the best major for investment banking(investment banking division M&A and IPO side)?

[debbiemchugh]

Amazing. So clever. Thank you so much :)