Hello can you please add another automation that will bring back a selected data from the data tab back to form. For example selecting row 2 will populate the data contained in that row back to the original form. Or may be adding another button that will bring back a selected data from the data tab based on index or identifier like combination of the names or unique values. Thanks
A few hours later . . . now I have the dopest production delay entry from. This video gave me EXACTLY what I needed to reduce my process to one step. Thank you so much!
@@MarkUSAfreedom hi Mark, on this website called BHN Software i found a product key for Office, it totally worked. You might want to try it so you can use excel without any problems, hope it helps!
nice video. Couple things you should change, in the protection tab, don't allow selection of protected cells, this way the cursor stays only in the cells the are unprotected, so it works more like a user form where you can't select the titles. you also need to unprotect the refers to cell for the Option buttons. The option buttons are trying to change a value in a protected cells. this is why you get the error message.
The problem with the above is that the macro will not be able to select the grayed out data below the form which is coped to the data sheet. To overcome this just unlock those cells then your above solution will work.
I consider myself an advanced expert in excel and yet I learned new things in this video today, simplicity sometimes beats complexity and this video proved it for me, appreciate it
@@euh4367 That's very true, the only way you can call yourself an expert is dependent on the expertise level of the surrounding people, in certain contexts I say I am an advanced expert, with other people I may say I'm an expert, and certainly with some specific people I'll be humbled and say that I'm an intermediate compared to them (people I have watched online in the Excel world championship). and at the end, each of us is knowledgeable in certain areas, there is always the possibility of people beating you in a unique method you've never seen before, or with other areas you're simply not knowledgeable about. I believe this applies to any set of skills.
Thank you for the video! I would like to suggest a slight alteration to the process. 8:05 - You could omit the need to transpose (and by proxy, the "Raw" sheet) by moving the refence cells (C21:D30) to the top of the Data tab as a single row instead of as a column on the input sheet. Then, you could paste directly to a table on the same page using your process.
Hi Kenji. From what I know, there is possibility to unlock option buttons. After drawing the option button on the sheet, you need to right-click on it and choose Format Control. Then, in the Protection tab, you should disable the Locked option. After you protect the sheet, you will be able to select the Entry Time.
Hi Kenji thanks for the video. I wanted to add that a solution to correcting the form buttons is by right clicking and going to format control, you will see an option tab for protection. Once you uncheck the "locked" option, it will allow the user to select the button even when the sheet is in a protected state.
Great! We always learn something new with you. But here is an other way to generate a form more easily by creating the data array first then select a cell in the array and in the search console, Search for the function "form ". Excel will show a you a form based on the informations in the first row. However this video allows us to learn more functions. My methode is for lazy persons 😂.
You are a genius, Kenji. I used your tutorial to create a purchase and sale form for my family business. I will link it to an income statement and balance sheet. Thank you so much.
Good Job Kenji I made it just following you Lesson. I was always confused the why is it that excel does not add entries down instead it was just doing the first 2 lines? and I loop the issue and never figure it out. and it was very simple! Yes! You teched me a lesson. Thanks again
As an HR practitioner, I find this tutorial very important for us. Reporting and system entry are not my strong points, so I am trying to improve through online learning. Thank you for this video. I plan to create a similar data entry format for performance reviews and recruitment. Could you please send me a similar link, as well as information on printing the final product? Thank you very much.
This is super helpful and easy to follow. You are great in explaining these steps. Do you any videos on how pull up a contact to populate all their information in a form?
Hi Kenji! Your explanation is so clear and I enjoyed it so much. I managed to make my own! One question though, I tried to save this workbook as shared workbook so that my colleagues are able to do the survey at the same time during our common lessons with our students but I received an error message saying macro cannot be viewed or edited in shared workbook. Can explain about this? Thank you!
Hi Kenji, in the macro where the data gets copied while the previous data move down, instead of this how can i do it so that new data gets added to the immediate row and continue to add to the rows below.
Great video, easy to follow. One question, is it possible to make each submitted line item to appear below the previous one rather than above it on the Data tab, so the first form submitted is on B3, the second form submitted is on B4 and so on
This is great! I've spent a lot of time messing about with VBA forms and hate writing data validation code. It's great how you put Excel to work to accomplish so much without having to write code it's a real time saver, very cool indeed!
perfect video I have a question in the "form" you have the item "Staff ID" how is it possible to make the "Staff ID" value so that I don't have to enter it manually, but it would always be new with a new record example: first staff value 1 second staff value 2 etc... something like n+1 always on a new record
This was a great help. What's the best way to share this booking form without people seeing the other tabs. Ideally I want the booking form to be filled out remotely and the master spreadsheet updates automatically.
Thank you so much for your sharing. Just wonder what if we need to modify something, can we use some trick to have the form with information displayed again?
Hi! This is a great video, thank you for creating it. I wanted to ask how you would create a date picker in the form and then have it part of the automation process?
Thanks for the wonderful video. I created a similar macro but I want to create another button that does the same thing but if it finds the same information as one in the list already to delete the new entry and write something beside the old one. Can anyone please help.
When hiding information that is off the end of the intended user work area it's generally more secure and less prone to data corruption to hide the rows and columns rather than match the colour.
Thanks Kenji.... This is great !!! How do you shift cells up? I have a table of students in number order and need the new entry to appear at the bottom of the list, not at the top.
This is very awesome, thank you! Is it possible to make the data entered via this entry form editable from the same form? Perhaps they can input something like the Staff ID and have the rest of the information populate in the form, that way they can make changes, and then submit the new information?
Hi Kenji, thank you for all your excel tutorials. Can I trouble you to consider showing your viewers how to do a dashboard that tracks monthly dividends?
This is good when you have 15 or less questions What about if you have 350 questions data entery of 500 respondents 😅. Sadly, i wish my professor use Google form.
Thanks for this work. Following this work and using it for a cooperate task, I vlookup some of the section to a master data file. And the goal to make the form user friendly as the vlookup will pre-populate some sections and we wil require the others to confirm the already pre populated sections if any is wrong there is a section to correct. However after assign the submit button a new record does not clear previous edits. Any help please
Hey Kenji, Thank you very much for the great video. Question: Can you create a search field with the option to retrieve the client's information and edit it? Looking to have the same thing as you but would like to have the option to retreat the information back to the form in order to edit it if changes are made Search to be made by searching by Number, email, first and last name (first and last name are separate tabs) will need both tabs to retrieve the right person Ideally, the search bar will give options like a drop down menu as I type the information. this way, I can pick someone from the list of search Thank you very much, Andrey
awesome video Kenji , thank you for explaining this patiently. I have been regularly watching your videos . Now ,I also have a youtube channel and I am commenting from same channel page
Thank you Kenji for a wonderful tutorial. Just a quick question under Dietary Restrictions: When I inserted the Formula in case the Answer is a "Yes", if one opts for a "No", then it also indicates a "False" just below it. How do I correct that please?
Very helpful video. It should be noted though that you cannot use any of the developer functions in Excel Online. They simply are not available as a feature. Unfortunate.
Thanks Kenji, great video to follow! How would we stop the 2 other tabs (Data and Raw) from being viewable as there could be sensible information on there?
Hide the sheets. Right lick on each sheet(Data and Raw) , you will see the option to Hide. To unhide them, right click on the Form or any unhidden sheet: it will show you all hidden sheets and you will be able to unhide them from there
Thank you so much for this video, i just have one question. I've hidden raw sheet and when i run the macro i get an error message. how can i resolve this? thank you one more time.
Hi. Try unhiding the 'raw sheet' by right-clicking anywhere on the sheet tabs, you'll get a box of all hidden tabs. From Kenji's method, it seems to me that the raw sheet is essential. Unless maybe you copy directly from the form values to the data table while recording the macro.
Thank you sir. Your instructions are very easy to follow. I was able to automate my library book stocks. I currently in planning of using excel to automate a library lending system.
Thank you for this video. So very helpful. QQ: How do you protect the data tab from users? What if I don’t want anyone to see the data once entered? Can I only share the form and still keep it linked so that the data still gets collected in the data sheet?
This is great! One question, I added a date to my form, the date is entered, but when data is transferred to the Data sheet, the date shows as number not related to the date. Does it sound like I'm missing a step?
Hi Kenji. Thanks for this tutorial. I did everything but my problem was when I entered my new transaction it was posted on the top instead on the below column, I redo everything many times but seemed the result is the same. (insert copied cell, sift cell down). Is there any way to fix this?
🔥 Take our VBA & Macros Course: www.careerprinciples.com/courses/vba-macros-for-business-automation
Hello can you please add another automation that will bring back a selected data from the data tab back to form. For example selecting row 2 will populate the data contained in that row back to the original form. Or may be adding another button that will bring back a selected data from the data tab based on index or identifier like combination of the names or unique values. Thanks
A few hours later . . . now I have the dopest production delay entry from. This video gave me EXACTLY what I needed to reduce my process to one step. Thank you so much!
Glad it was helpful :)
You helped me a lot! I'm watching your video for the first time and you are very clear on every step, thanks Kenji!!
heyy could you help me? I'm trying to set up Office on my computer to use excel, but idk what to do. Any tips?
@@MarkUSAfreedom hi Mark, on this website called BHN Software i found a product key for Office, it totally worked. You might want to try it so you can use excel without any problems, hope it helps!
@@keithM7 you helped me!! I bought it and it worked perfectly, thankss
Couldn't agree more on this. Professional
nice video. Couple things you should change, in the protection tab, don't allow selection of protected cells, this way the cursor stays only in the cells the are unprotected, so it works more like a user form where you can't select the titles. you also need to unprotect the refers to cell for the Option buttons. The option buttons are trying to change a value in a protected cells. this is why you get the error message.
The problem with the above is that the macro will not be able to select the grayed out data below the form which is coped to the data sheet. To overcome this just unlock those cells then your above solution will work.
I consider myself an advanced expert in excel and yet I learned new things in this video today, simplicity sometimes beats complexity and this video proved it for me, appreciate it
i think Excel has grown so so much bigger, even if i learn advanced tools i'ld be reluctant to call myself an expert..
@@euh4367 That's very true, the only way you can call yourself an expert is dependent on the expertise level of the surrounding people, in certain contexts I say I am an advanced expert, with other people I may say I'm an expert, and certainly with some specific people I'll be humbled and say that I'm an intermediate compared to them (people I have watched online in the Excel world championship).
and at the end, each of us is knowledgeable in certain areas, there is always the possibility of people beating you in a unique method you've never seen before, or with other areas you're simply not knowledgeable about.
I believe this applies to any set of skills.
Thank you for the video!
I would like to suggest a slight alteration to the process.
8:05 - You could omit the need to transpose (and by proxy, the "Raw" sheet) by moving the refence cells (C21:D30) to the top of the Data tab as a single row instead of as a column on the input sheet.
Then, you could paste directly to a table on the same page using your process.
Yup, ikr?
Guessing he is just giving additional knowledge.
To solve your problem with the radio button, you need to unlocked the cell where you link the value of the radio button. and that's it. Nice Video!!!
Hi Kenji. From what I know, there is possibility to unlock option buttons. After drawing the option button on the sheet, you need to right-click on it and choose Format Control. Then, in the Protection tab, you should disable the Locked option. After you protect the sheet, you will be able to select the Entry Time.
You're amazing bro... Love how calm you're when talking and explaining.. good luck
Hi Kenji thanks for the video. I wanted to add that a solution to correcting the form buttons is by right clicking and going to format control, you will see an option tab for protection. Once you uncheck the "locked" option, it will allow the user to select the button even when the sheet is in a protected state.
Mine doesn't work
tried this - still doesn't work
Great!
We always learn something new with you.
But here is an other way to generate a form more easily by creating the data array first then select a cell in the array and in the search console, Search for the function "form ". Excel will show a you a form based on the informations in the first row.
However this video allows us to learn more functions.
My methode is for lazy persons 😂.
Really helpful video for me to replicate for stock valuation template that I am working on.
You are a genius, Kenji. I used your tutorial to create a purchase and sale form for my family business. I will link it to an income statement and balance sheet. Thank you so much.
This is my first time watching your video and i found you do amazing while watching. Clear, direct, detailed and concised.
Hi, thank you so much. How do you back to one record to add something (you forgot) like DOB?
Great Video. Ensure the cell link for the two radio buttons is unlocked before you lock the sheet. It should work after that.
Even I had the same thought
very well noted
I honestly don't know how to use Excell I want to learn how to enter data. I just subscribed I Will watch all your video to learn. Wish me luck
Thanks a lot. Enjoyed the Video while learning. It felt easy while learning.
Good Job Kenji I made it just following you Lesson. I was always confused the why is it that excel does not add entries down instead it was just doing the first 2 lines? and I loop the issue and never figure it out. and it was very simple! Yes! You teched me a lesson. Thanks again
This helps me alot...in this tutorial, can you add a tutorial for like sorting / saving address specifically in different spreadsheet when clicking...
You one among the Millions, Thank you Mr. Kenji
As an HR practitioner, I find this tutorial very important for us. Reporting and system entry are not my strong points, so I am trying to improve through online learning. Thank you for this video. I plan to create a similar data entry format for performance reviews and recruitment. Could you please send me a similar link, as well as information on printing the final product? Thank you very much.
This is super helpful and easy to follow. You are great in explaining these steps. Do you any videos on how pull up a contact to populate all their information in a form?
Hi my friend. Great video, In order for the buttons to work I think you have to unblock their linked cells as well
geniusss
Hi Kenji! Your explanation is so clear and I enjoyed it so much. I managed to make my own! One question though, I tried to save this workbook as shared workbook so that my colleagues are able to do the survey at the same time during our common lessons with our students but I received an error message saying macro cannot be viewed or edited in shared workbook. Can explain about this? Thank you!
This is very helpful! I tried this one and it worked for me. Your instructions very simple and very clear. Life saver! :)
Hi Kenji, in the macro where the data gets copied while the previous data move down, instead of this how can i do it so that new data gets added to the immediate row and continue to add to the rows below.
Question, after i click the submit button, can we make the new data below the old data. Its like making the oldest data on the top row ?
Great video, easy to follow. One question, is it possible to make each submitted line item to appear below the previous one rather than above it on the Data tab, so the first form submitted is on B3, the second form submitted is on B4 and so on
This is great! I've spent a lot of time messing about with VBA forms and hate writing data validation code. It's great how you put Excel to work to accomplish so much without having to write code it's a real time saver, very cool indeed!
Thanks Kenji, you make my work life easier with this. Well explained....! I learn Lot of Things and Keep Uploading New Advance Methods and Tricks
Love the tutorial and idea. Can you think of a way to send this by email survey and collect the data online? Google Sheets, perhaps?
This was my first macro thank you very much for clear instruction you made one of my life time dream come true.
Glad it helped!
Thank you very much for this very good sharing. Please advise how to read the existing record so that user can update the record. Thanks again.
perfect video
I have a question
in the "form" you have the item "Staff ID"
how is it possible to make the "Staff ID" value so that I don't have to enter it manually, but it would always be new with a new record
example:
first staff value 1
second staff value 2
etc...
something like n+1 always on a new record
Nice tutorial, clear and to the point.
How would one make an Android app that displays the data entry box and then sends it to excel?
I wanted to check if 2 or more people can save the data at the same time on this workbook? Thank you much.
That would be possible if it's a Google spreadsheet I guess because if it's an excel, it would be on separate files.
You only need to unprotect the cell where you link the option buttons, in this case E27. After you do that it will work
With VBA (and not only recording macro), you can add 2 buttons to go back or forward on previous inputs, to permit modification.
Thanks Kenji, that was great presentation and a new learnings. Also, may i know how to correct the data already submitted?
This was a great help. What's the best way to share this booking form without people seeing the other tabs. Ideally I want the booking form to be filled out remotely and the master spreadsheet updates automatically.
Thank you so much for your sharing. Just wonder what if we need to modify something, can we use some trick to have the form with information displayed again?
this is a great video! I do have one questions, how do would you make a graph of the data that updates live?
Hi! This is a great video, thank you for creating it. I wanted to ask how you would create a date picker in the form and then have it part of the automation process?
Thanks for the wonderful video.
I created a similar macro but I want to create another button that does the same thing but if it finds the same information as one in the list already to delete the new entry and write something beside the old one.
Can anyone please help.
Hi. informative video. Thanks alot. Can I ask how do I keep the date and currency format as is when I update to the DATA tab?
When hiding information that is off the end of the intended user work area it's generally more secure and less prone to data corruption to hide the rows and columns rather than match the colour.
yeah right, but as long as the sheet is protected with the cells locked, making only your intended work area active, then your data remains intact...
@@rawconceptcorrect
Thank you for useful video! How can I stop users to copy and paste into drop down list?
Thanks Kenji.... This is great !!! How do you shift cells up? I have a table of students in number order and need the new entry to appear at the bottom of the list, not at the top.
This is very awesome, thank you! Is it possible to make the data entered via this entry form editable from the same form? Perhaps they can input something like the Staff ID and have the rest of the information populate in the form, that way they can make changes, and then submit the new information?
Thanks a lot for this amazing tutorial. Never knew Excel was so powerful .
Glad it was helpful!
Great video. I wonder if the submitted data could feed a dinamic table as well.
informative video! but just wondering how this value adds if I can just key in manually into the records
Kenji, just to let you know that You are the Best. Outstanding video
Thanks so much 😁
Hi Kenji, thank you for all your excel tutorials.
Can I trouble you to consider showing your viewers how to do a dashboard that tracks monthly dividends?
This is very interesting for my learning, I believe this will build me knowledge of data analysis. I will keep on following and sharing your link
First of all, great video! Question: how about when it is a very first record?
The automation section was great! I never would have thought about that! Thank you!
This is good when you have 15 or less questions
What about if you have 350 questions data entery of 500 respondents 😅.
Sadly, i wish my professor use Google form.
Meeeen 😮😮
I can make a accounting app for where Im working...
You are amazing 🙏🙏
Thanks ❤❤
I appreciate this tutorial, so smooth and easy explained. Thanks Kenji,
Glad it was helpful!
Exel is king of data entry programms
Plain and simple explanation ❤ Thank you sir 🫡
Hi Kenji! I learned a lot from your tutorial vids. Hope you wont stop doing this and kudos! 👍👍👍
Thanks for this work. Following this work and using it for a cooperate task, I vlookup some of the section to a master data file. And the goal to make the form user friendly as the vlookup will pre-populate some sections and we wil require the others to confirm the already pre populated sections if any is wrong there is a section to correct. However after assign the submit button a new record does not clear previous edits. Any help please
Hi Kenji, Great video! You made excel seem so easy!
How can I export this as a form and distribute it in an email?
You're a great teacher. Very cogent and very knowledgeable. I'll be watching more of your videos.
Wow what an excellent presenter! Thank you for this clear tutorial!
Amazing. I have learnt something new today. Looks very simple but very powerful.
Hey Kenji,
Thank you very much for the great video. Question: Can you create a search field with the option to retrieve the client's information and edit it?
Looking to have the same thing as you but would like to have the option to retreat the information back to the form in order to edit it if changes are made
Search to be made by searching by Number, email, first and last name (first and last name are separate tabs) will need both tabs to retrieve the right person
Ideally, the search bar will give options like a drop down menu as I type the information. this way, I can pick someone from the list of search
Thank you very much, Andrey
awesome video Kenji , thank you for explaining this patiently. I have been regularly watching your videos . Now ,I also have a youtube channel and I am commenting from same channel page
Thank you Kenji for a wonderful tutorial. Just a quick question under Dietary Restrictions: When I inserted the Formula in case the Answer is a "Yes", if one opts for a "No", then it also indicates a "False" just below it. How do I correct that please?
Very helpful video. It should be noted though that you cannot use any of the developer functions in Excel Online. They simply are not available as a feature. Unfortunate.
your tutorial are worth watching.. thanks for the guides!
By far the most helpful content I've watched in a decade! Well done Kenji
Thanks so much for a simplified version. It's so much helpful for projects that I'm doing for work.
Thanks for the video. How do we share the form with others?
Thanks Kenji, great video to follow! How would we stop the 2 other tabs (Data and Raw) from being viewable as there could be sensible information on there?
Hide the sheets. Right lick on each sheet(Data and Raw) , you will see the option to Hide.
To unhide them, right click on the Form or any unhidden sheet: it will show you all hidden sheets and you will be able to unhide them from there
Thanks Kenji for this work. I would like to know if I can do the same macro for "update" as "submit" to update the data.
Sir please make such kind of videos on Power BI
But you are making excellent videos on excek ❤❤
'm literally watching you while having lunch. Thanks for the entertainment/learning video.
Nice! Hope lunch is a good one haha
Simple and clear instructions!! Amazing teaching skill... ❤️ much appreciated!!!
Thanks Kenji, you make my work life easier with this. Well explained and have change a lot of my data entry .
Thank you so much for this video, i just have one question. I've hidden raw sheet and when i run the macro i get an error message. how can i resolve this?
thank you one more time.
Hi. Try unhiding the 'raw sheet' by right-clicking anywhere on the sheet tabs, you'll get a box of all hidden tabs. From Kenji's method, it seems to me that the raw sheet is essential. Unless maybe you copy directly from the form values to the data table while recording the macro.
Thank you sir. Your instructions are very easy to follow. I was able to automate my library book stocks. I currently in planning of using excel to automate a library lending system.
I appreciate your help very much. I am observing each stage, and it is clear
Very helpful....is it possible to restrict access to a form link to ensure only certain people can fill it out
Thanks a lot for your valuable details. This matter is well arranged class. Very good. Appreciated
Thank you for this video. So very helpful. QQ: How do you protect the data tab from users? What if I don’t want anyone to see the data once entered? Can I only share the form and still keep it linked so that the data still gets collected in the data sheet?
This is great! One question, I added a date to my form, the date is entered, but when data is transferred to the Data sheet, the date shows as number not related to the date. Does it sound like I'm missing a step?
Excellent, concise and informative tutorial.👍
Your post are very simple and concise to understand
Thank you. A question, what if I want to have new data added to the last row not first one?
TQ Kenji for the sharing. if we want to change any info, how ya ? TQ in advance
Hi Kenji. Thanks for this tutorial. I did everything but my problem was when I entered my new transaction it was posted on the top instead on the below column, I redo everything many times but seemed the result is the same. (insert copied cell, sift cell down). Is there any way to fix this?
Thank you very much. Very useful Vedio.................
Thanks a lot.
Can you help with inventory management software like this.
Very informative. I have a few ideas for work that I will try this with.
That was Amazing
Is it possible to edit a submitted Data?
hello Kenji very nice video. I'm using the ( , )to seperate Yes and No but it doesn't work what else can i do?
Exactly what I wanted. Thank you so much for the help