How To Use ChatGPT To Make An Automated Data Entry Form In Excel

Описание: Download FREE Template Here 👇
https://drive.google.com/file/d/1JPgc...

In this tutorial you'll learn how use ‪@OpenAI‬'s ChatGPT to easily make an automated data entry form in Excel. I will show you how to limit excel cells to accept only text, number or a value with a specific format.
We will also use radio buttons/checkboxes and dropdowns to dynamically capture and save data such that once a user clicks submit, the data is automatically saved on a separate excel sheet.

First we will make a form template and the results sheet. Then we will use ChatGPT to automate the entire process.

#excel #exceltutorial #excelautomation #chatgpt

Chapters:
0:00 – What you'll learn and demo
1:02 - Making Excel Form Template
5:16 - Automating Excel form With ChatGPT
7:53 - Testing and Results
9:51 - Conclusion

ChatGPT Prompt Used in the Video:

I want to make an automated data entry form that captures values from one sheet and save it on the next sheet.
I have a sheet named “Form” that contains my main data entry form and I have another sheet named “Entries” where I want to save the data that is captured from the “Form” sheet.
(1) Here are the instructions for the sheet named “Form”.
(a) Cell D4 is an input field for “Full Name”. I want to accept only text in this field.
(b) Cell G4 is an input field for “Employee ID”. I want to accept both numbers and text in this field.
(c) Cell G6 is an input field for “Day”. I want to accept only numbers from 1 to 31 in this field.
(d) Cell H6 is an input field for “Month”. I want to accept only numbers from 1 to 12 in this field.
(e) Cell I6 is an input field for “Year”. I want to accept only the number “2024” in this field.
(f) Cell D8 is an input field for “Employee NIC”. I want to accept only numbers and dashes in this field but with a specific format. That is XXXXX-XXXXXXX-X i.e., 5 numbers followed by a dash, then 7 numbers followed by a dash and then 1 number at the end.
(g) Cell G8 is an input field for “Department”. I want to accept only text in this field.
(h) Cell D10 is an input field for “Designation”. I want to accept only text in this field.
(i) Cell G10 is an input field for “Gross Pay”. I want to accept only number in this field. Format the numbers as a dollar amount with two decimal places.
(j) Cell D12 is an input field for “Address”. I want to accept both numbers and text in this field.
(k) I also have two radio buttons. Both of them are linked with cell B1. Radio button “Male” represent number “1” and radio button “Female” represent number “2”.

(2) Here are the “validation rules” for the sheet named “Form”
(a) First of all check that no field should be empty.
(b) Then check that all cells/fields are properly filled according to the conditions described in (1) above.
(c) Check for errors in real time using “worksheet change event”.
(d) If any cells/fields are not according to the condition/format that is described in (1) above, turn that cell / field “red” as soon as the cell become in active after entering the value. But do not erase the already entered data if it is correctly entered.
(e) Do not allow user to submit the form and save data until all fields are correctly filled in according to the conditions/rules described in (1) above.

(3) Here are the instructions for the sheet named “Entries”
If all cells/fields are correctly filled according to the “instructions” in (1) above AND the “validation rules” in (2) above, then proceed to save the data on the sheet named “Entries” as follows;
(a) Cell D4 value should be saved in column A starting from row #2.
(b) Cell G4 value should be saved in column B starting from row #2.
(c) If Cell B1 value = 1, then save it as “Male” in column C starting from row #2 and if Cell B1 value = 2, then save it as “Female” in column C starting from row #2
(d) Concatenate and save the values of cells G6, H6 and I6 in column D starting from row #2. The values of cells G6, H6 and I6 should be concatenated and saved together in a single cell as follows = G6-H6-I6.
(e) Cell D8 value should be saved in column E starting from row #2.
(f) Cell G8 value should be saved in column F starting from row #2.
(g) Cell D10 value should be saved in column G starting from row #2.
(h) Cell G10 value should be saved in column H starting from row #2.
(i) Cell D12 value should be saved in column I starting from row #2.

Further instructions for sheet named “Entries”
If a new entry is added do not overwrite the existing data, paste it in the next row. Use “nextRow” argument to find the row that is filled last and paste data in the next row (+1).
Paste/save data in the same format in which it is captured from the “Form” sheet.
Make the data center align, auto size cells and add all borders.

(4) Based on the above scenario, write me the VBA/Macro to accomplish this.
(a) Give me proper instructions on how to implement this.