Annoyingly enough I found this just _after_ I had learnt about validation and VLookup, but this is a much more concise and easier-to-follow explanation than what I had to sit through. Have an upvote.
I've been through a lot of videos, but none have been this instructional. Not only that, but you're voice is also very pleasant. You've helped me a ton, thank you!
NAILED it! Thank you very much for this tutorial. I found you after struggling through at least 25 other videos. Your explanation was clear and concise! Thanks again!
Thank you, thank you, thank you. I've spent the past 2 hours trying to figure out how to do this. Your video was extremely helpful and easy to follow. You're a lifesaver!
Great video! I learned how to do this and then learned how to use the MATCH function to make the column index dynamic. Saved me a load of time after setting it all up!
@@randomrandy3059 It depends on what you're doing and personal preference. But you can use them together. Basically, as long as you're headers are the same, you'd be using the MATCH function to find the column index number for you. I do find that I've been using a combo of INDEX and MATCH more frequently. Keep learning, once you get the hang of it, you'll want to move onto other ways to do the same tasks faster (depending on how much data you're working with).
@@nguyen3276 Cool. I can't wait to understand more. I have since gotten the hang of index and match, but im finding xlookup to be more convenient, but then I have to come back to index and match. It confusing at first, but like you say "Keep learning...." quick run down, I've hosted a football pool for 5 years after this season. I've always used numbers because it was simple. I knew about Excel but never bothered to get into it because, well, I thought Excel was very complex for something so simple that I was doing. A few months I decided to cross over, forcing myself to use and learn excel during the season, and man how mind opening it was for me. I started looking at my simple football pool and it was alot of work. Now im building a system to automate alot of things, and really make simple. Anyhow, thank you, and have a good day.
You are a life saver! Thank you so much for posting such a useful video lesson. I did not get it at first until I noticed the look up column has to be the the first column in the lookup sheet. Otherwise it does not work.
Thank you. Well described. Your description of what I found quite difficult to figure out, and I didn't until I found your video, was most helpful. Very much appreciate this!
Nothing I can say other than THANK YOU exactly what I needed I appreciate you taking the time so I have only 1 way I know how to repay by a great comment. AAAAA++
Hi Sally! Thank you so much for your Video! It was so HELPFUL! Now I know how to use VLOOKUPS!! : ) The other courses does not explain "As Hands On" as you did. Congrats! I subscribed to you as well! I will refer my friends to your trainings! Take care! : )
Thanks this video is super helpfully. Looking for this type of info for hours. Now im going to use dynamic dropdrown list and vlook up and save it as a webpage. 🙂
Thank you so much, this video was very helpful! I was wondering if there is a way to auto-populate with multiple selection drop-downs. Once I select two drop-downs within the same cell, I get an error message in my auto-populated cells. I'm using this as a tracker, and would like to have the option to select multiple items from the drop downs to avoid having too many cells.
Hey Sally, I liked the video, simple and easy to understand, i have a daily log book of material received and i have a daily material receive/consumption as per site book, i want to use data validation for date wherein i can choose the date and it shows me the record of material received and consumption in a single sheet. I have two different sheet of the material received and consumption.
Thanks for this tutorial ...i need more guide. I want that not only this formula and list work for only one customer but also need for other when i select other account on bottom cell ..as i have to made list in bottom all cell and select other account and their respective details ...how i can get this ..
Yes! One of my preferred ways to do this is using IFERROR with your VLOOKUP formula. Here's an explanation of how to use that: exceljet.net/formula/vlookup-without-na-error
Hi,, Sally, your videos are really nice and helpful. here I need to know that how to create a dropdown list which can display relevant data of that item available in other columns too.
This is a much easier way of completing this task, Thank you. Now my dilemma is I have a customer with multiple accounts and I want to display everything for that customer name. Any help you can provide would be greatly appreciated.
@1:16 ~ On Sheet 3, make your data into a table and name the table. Click anywhere in the data and hit CNTRL + T. You won’t have to select all of those empty cells for your drop down list. As you add records to the data table, your drop down will automatically include the new records. Then, when writing the vlookup formula, you can reference the name for your table as your array, as well as the column name in the table for the lookup value.
You seem to know some stuff! Do you mind answering a question for me? Is it possible to have that drop down list create a column of ascending numbers? For example, I would like to input F1 race lap times for a certain tyre. I make a drop down list from 1-100. The tyre goes 30 laps. So is it possible to choose 30 from the drop down list and have it populate the cells below from 1-30, like this... Column A Column B Row1 Drop down list - select 30 (and then it populates the column like this..) Row2 1 (lap time inputted after) Row3 2 (lap time) Row4 3 (lap time) Row5 4 (lap time) Row6 5 (lap time) Row7 6 (lap time) etc. all the way to 30... so then I can just put the lap times in the next column for each lap. Is this even possible?
nurfnurder ~ Drop down list do not creste new Records (Rows) in a table, they only allow you to input data (validate). However, after you creste your table you can right click in the table and insert rows from that menu. If you highlight multiple rows first and then right click the highlight rows, you can then insert multiple rows at one time. Once you have as many rows as you need, you can highlight “Row 1, Row 2” and drag that down. Excel will automatically number the remaining rows as Row 3, Row 4... etc. Hope I understood your question correctly and this answer works for what you are wanting to do.
Fantastic video. This is exactly what I was looking for. I do have a question for something that I would like to do. Can you show how that I can make a number that is in a drop down list show as a different color than all the others in the same drop down list so that it is easier to find than all of the black numbers. I want it to stand out so that I can find it quickly.
Hello. Thank you very much for the tutorial video. This really is a great help. If I'm sharing this form among my co-workers, how do I make sure they don't erase the data and mess everything up? ?Thanks.
I was looking for this!!! Omg I'm so happy.i wasn't sure what it's called so im just glad to find this.need it for my new work.thanks a lot for sharing
That's a good explanation for vlookup? I know something about the excel and am now creating formulas in workspace based in the data I have, in order to help my team to work easier. I have a question now. Am creating a drop down using data validation and I want know howw to auto populate the fields. For example, if I enter the po Box number, I want the address to be populated inside the drop down. Obviously we cannot scroll and check; if I have more adresses. I've referred to some vids in TH-cam regarding this. They're using SEARCH, IFRRROR, IF, MAX, VLOOKUP AND OFFSET formulas to do it. It would be great, if you explain it in detail about this, as the way you explain things is Fantastic. I used to be like that in my office and thank u for the post.
Many have asked for similar functionality and as far as I can tell, there's not a way to auto-complete data like that using this method. You may be able to get an add-on product that will help accomplish this or use VBA to make it work. Here's a link with some options that seem to do what you're wanting to do: stackoverflow.com/questions/19818075/excel-data-validation-with-suggestions-autocomplete I hope this helps!
Dear Sally, Thank you for this very easy to follow instructional. I wanted to know is there a way to do the same thing yet backwards? I want to create a form that will auto populate onto a spreadsheet.
THANK YOU THANK YOU THANK YOU!! This has been the most understandable tutorial i have EVER seen!! However, I have a question...I allowed a blank cell in my list, so if I have no value to select, then I can just keep blanks in the autopopulate cells. When I select the blank in my drop down box, in the cell I wish the data to autopopulate it displays #N/A. How do I make the formula return a blank cell if there is no value selected in my drop down list. Also, how would I use a drop down box to autopopulate data from 2 separate sources...for example, I have 2 manufacturers with multiple products and different prices for each. If I have a drop down for the product and a separate drop down for the manufacturers, i want the price box to reflect the price for the material and manufacturer selected. So if I select Wood (the product), then select PLAtech (Manufacturer), I want the result in the price box to return $10.95. Conversely, if I choose Nylon (Product), then select (Gerber Nylon), I want the price box to return $25.36.
This close to what I'm trying to do, but not quite. I do grocery deliveries and after trying out several mileage tracker apps I've decided to go old school and just use Excel. The only issue I'd have with it is typing repeat addresses, such as home or work, or repeat customers. What I'd like to do is make a list of recurring addresses and simply be able to type home, or the customers name, and have the full address appear on the log sheet. I intend to have a separate sheet for locations. Thanks for your help in advance.
Excel does not allow for an auto-lookup function as you describe, unfortunately, but this site has an option that seems easy to do: www.ozgrid.com/Excel/autocomplete-validation.htm. This seems really easy and doesn't require Visual Basic, just a hidden row of existing data. I hope this helps!
HI, Sally Joe here from Perth Australia, I love your videos, they have been very useful. I need guidance and help to finish an excel project. I have created a database and setup user form via vba to add and edit new contacts. It works well. I want to go to the next level. When I finish entering new entry via a command button, I want to automatically update cells in another sheet with some of the data I have just entered. for example. when we add a new contact I want to print a delivery label ( i have an excel template already in the next sheet of the workbook), the label will be used to ship them a sample pack, then a copy of the populated delivery dockets is saved in another file and the template gets unloaded??? regards Joe
This is an awesome video. I do have one question though. I am trying to apply the same vlookup formula to an entire column. I have the formula working in the first cell of the column but when I try and drag it down or copy and paste the formula, I get the #REF! error. Here is the formula I want to drag down: =VLOOKUP($C$3,'Expense Rates'!1:1048576,21,FALSE) Any help would be great! Thanks
Great presentations! Is there a way to make the same spreadsheet with multiple lookup values? In other words if I have a customer I want looked up not only by number, but lets say customer type?
Hey Sally, thanks for the great video! It answered most of my questions very well, but I do have an additional question that would take this tutorial a step further. I have a sheet used to create user rights on RFID chips. Most instances can be taken care of by using a table referencing the rights to the job type (Vlookup), however, sometimes I need to break from that set table and create a custom set of rights. Is there anyway I can do a custom field entry on a vlookup cell without adding it to the table? Thanks in advance and I look forward to hearing from you.
@@SimpleSoftwareTutorials Woaaa.. never thought you're gonna reply me. And comes with real solution 😊 Havent try it yet, but i will. I really much appreciate it. Success for you Sally. Hope the best for you. Thank you
Seriously Vlookup is that easy.
thank you so much until now u have been the only to explain vlookp in such a simple way..
Glad I was able to help you. Thanks for your feedback!
Annoyingly enough I found this just _after_ I had learnt about validation and VLookup, but this is a much more concise and easier-to-follow explanation than what I had to sit through.
Have an upvote.
Superb and simplistic....great mam
I've been through a lot of videos, but none have been this instructional. Not only that, but you're voice is also very pleasant.
You've helped me a ton, thank you!
So HAPPY I stumbled across this video today. You explained everything very well and I now feel confident that I can do what I need to do. Thank you!!
NAILED it! Thank you very much for this tutorial. I found you after struggling through at least 25 other videos. Your explanation was clear and concise! Thanks again!
Best tutorial on Vlookup and autopopulate I've seen!
Finally found this video for data validation and vlookup. Massive help.
This was the fastest, most succinct solution video I’ve found on this topic. Thank you so much!!
After searching for a couple hours your video was the only one that helped, thanks!!
This is the formula I have been looking for forever! You just saved our department a ton of time! Thank you!
Thanks very much for the very clear and well explained Vlookup. With your example, I now could figure out how and way to use it. Great.
Thank you, thank you, thank you. I've spent the past 2 hours trying to figure out how to do this. Your video was extremely helpful and easy to follow. You're a lifesaver!
Great video! I learned how to do this and then learned how to use the MATCH function to make the column index dynamic. Saved me a load of time after setting it all up!
So is the Match function better than VlookUp? Im trying to understand the differences.
@@randomrandy3059 It depends on what you're doing and personal preference. But you can use them together. Basically, as long as you're headers are the same, you'd be using the MATCH function to find the column index number for you. I do find that I've been using a combo of INDEX and MATCH more frequently. Keep learning, once you get the hang of it, you'll want to move onto other ways to do the same tasks faster (depending on how much data you're working with).
@@nguyen3276 Cool. I can't wait to understand more. I have since gotten the hang of index and match, but im finding xlookup to be more convenient, but then I have to come back to index and match. It confusing at first, but like you say "Keep learning...."
quick run down, I've hosted a football pool for 5 years after this season. I've always used numbers because it was simple. I knew about Excel but never bothered to get into it because, well, I thought Excel was very complex for something so simple that I was doing. A few months I decided to cross over, forcing myself to use and learn excel during the season, and man how mind opening it was for me. I started looking at my simple football pool and it was alot of work. Now im building a system to automate alot of things, and really make simple.
Anyhow, thank you, and have a good day.
I finally found someone to help after days of searching! Thank you for this
I had watched so many videos but your video is Easy to understand and is very very helpful for me.. thanks a lot !!
THANK YOU! I have been trying to figure out how to do this for over a month. I can finally easily convert project numbers to our new numbering system.
Thanks a million. May God bless you always. Love from Malaysia
You are a life saver! Thank you so much for posting such a useful video lesson. I did not get it at first until I noticed the look up column has to be the the first column in the lookup sheet. Otherwise it does not work.
Thank you. Well described. Your description of what I found quite difficult to figure out, and I didn't until I found your video, was most helpful. Very much appreciate this!
Man, I love you, I always wanted to do this. This will save me tons of time. GRACIAS
I have been looking for this technique for 2 year, be blessed,
I should really thank you for solving a huge issue :) Thanks for this very good video !!
Nothing I can say other than THANK YOU exactly what I needed I appreciate you taking the time so I have only 1 way I know how to repay by a great comment. AAAAA++
Thanks so much, I appreciate your feedback!
Precise and to-the-point. Thank you.
What a great and helpful video. Clear and on point. this has saved me hours....Thank you Sally.
Thanks for the feedback!
Very well done. Best explanation I have seen on the internet. You made it very easy. Thank you!!
really helpful...great and your voice is also nice!!
Thank you!
Took an hour to find your video..exactly what I was looking for! Thanks a lot :)
u have no idea how much this helped me. thanks a lot :)
Exactly what am looking for 🔥
Thank you! This was a very helpful tutorial.
THANKS FOR YOUR INFORMATION ITS REALLY HELPFULL FOR WORK
Excellent and remarkable learning video, thank you for your magnificent efforts.. Fantastic explanation.
Thank you, it is very helpful and very clear instructions,
Hi Sally! Thank you so much for your Video! It was so HELPFUL! Now I know how to use VLOOKUPS!! : ) The other courses does not explain "As Hands On" as you did. Congrats! I subscribed to you as well! I will refer my friends to your trainings! Take care! : )
Wow, this is perfect for what I needed.
Very useful, solved a difficult problem I was having!
I thank you !!!This was so helpful
Thanks a lot! like many others said below, after a long time I found your video which solved the issue I was having.
You are a lifesaver. Thanks so much for this
Glad it was helpful to you!
Thanks this video is super helpfully. Looking for this type of info for hours.
Now im going to use dynamic dropdrown list and vlook up and save it as a webpage. 🙂
Thank you so much, this video was very helpful! I was wondering if there is a way to auto-populate with multiple selection drop-downs. Once I select two drop-downs within the same cell, I get an error message in my auto-populated cells. I'm using this as a tracker, and would like to have the option to select multiple items from the drop downs to avoid having too many cells.
Thank you for this information, you have no idea how much it was needed today :)
Very useful - Thank you Ali
So Great. Can you please show how to print all the individual form to pdf in bulk? Thanks.
This was really helpful. How do you lock up cell formulas so that the sheet can be used for control
Sorry for the delay. Here's a video I made explaining locking, hiding and protecting Excel worksheets and workbooks. Thanks for your feedback!
Brilliant... Thank you.. very easy to follow and bang on.. Thank you
Thank you so much for this! It was the only video that really showed what I needed :) So very helpful!
THANK YOU SOOO MUCH!VERY EASY TO FOLLOW TUTORIAL :)
Hey Sally, I liked the video, simple and easy to understand, i have a daily log book of material received and i have a daily material receive/consumption as per site book, i want to use data validation for date wherein i can choose the date and it shows me the record of material received and consumption in a single sheet. I have two different sheet of the material received and consumption.
So very useful. Thank you again Sally, keep up the brilliant work.
Great video. Thank you. Is there a way of using vlookup / xlookup to also show comments for a particular client?
Thanks for this tutorial ...i need more guide. I want that not only this formula and list work for only one customer but also need for other when i select other account on bottom cell ..as i have to made list in bottom all cell and select other account and their respective details ...how i can get this ..
YOU ARE A LIFESAVER!!! THIS WAS AMAZING!
Can you make excel display blank rather than n/a before drop down selection? Thank you for such a helpful video!
Yes! One of my preferred ways to do this is using IFERROR with your VLOOKUP formula. Here's an explanation of how to use that: exceljet.net/formula/vlookup-without-na-error
Thank you that’s awesome!!!
Hi,, Sally, your videos are really nice and helpful. here I need to know that how to create a dropdown list which can display relevant data of that item available in other columns too.
Its a great video...So useful. Thank you again Sally, keep it up...
Time saving tips👌🏼
This is a much easier way of completing this task, Thank you. Now my dilemma is I have a customer with multiple accounts and I want to display everything for that customer name. Any help you can provide would be greatly appreciated.
Great tutorial!! A HUGE help for me!
Incredible Tutorial - Thank you so much!
Sally... Thank you so much.. really saved my mental stability :D :D :D
Thanks so much, Sally! This was really helpful :)
youre amazing thank you for the content.
@1:16 ~ On Sheet 3, make your data into a table and name the table. Click anywhere in the data and hit CNTRL + T.
You won’t have to select all of those empty cells for your drop down list. As you add records to the data table, your drop down will automatically include the new records.
Then, when writing the vlookup formula, you can reference the name for your table as your array, as well as the column name in the table for the lookup value.
You seem to know some stuff! Do you mind answering a question for me? Is it possible to have that drop down list create a column of ascending numbers? For example, I would like to input F1 race lap times for a certain tyre. I make a drop down list from 1-100. The tyre goes 30 laps. So is it possible to choose 30 from the drop down list and have it populate the cells below from 1-30, like this...
Column A Column B
Row1 Drop down list - select 30 (and then it populates the column like this..)
Row2 1 (lap time inputted after)
Row3 2 (lap time)
Row4 3 (lap time)
Row5 4 (lap time)
Row6 5 (lap time)
Row7 6 (lap time)
etc. all the way to 30...
so then I can just put the lap times in the next column for each lap.
Is this even possible?
nurfnurder ~ Drop down list do not creste new Records (Rows) in a table, they only allow you to input data (validate).
However, after you creste your table you can right click in the table and insert rows from that menu. If you highlight multiple rows first and then right click the highlight rows, you can then insert multiple rows at one time.
Once you have as many rows as you need, you can highlight “Row 1, Row 2” and drag that down. Excel will automatically number the remaining rows as Row 3, Row 4... etc.
Hope I understood your question correctly and this answer works for what you are wanting to do.
Awesome and so easy to understand!
Fantastic video. This is exactly what I was looking for. I do have a question for something that I would like to do. Can you show how that I can make a number that is in a drop down list show as a different color than all the others in the same drop down list so that it is easier to find than all of the black numbers. I want it to stand out so that I can find it quickly.
Hello. Thank you very much for the tutorial video. This really is a great help. If I'm sharing this form among my co-workers, how do I make sure they don't erase the data and mess everything up? ?Thanks.
Thank you so much. The video was really helpful. I have a query here. Instead of the dropdown at cell B3, can we make it with text as well?
Amazing help. Thank you !!
Thank you! This was extremely helpful!! Clear and concise! 🙌🏽
I love the way u explain n help ... Thumbs up n subscribed
Awesome tutorial, thanks
really you saved my day! thanks alot
This helped me so much! Thank you for saving me time and a major headache!
Awesome. ..been looking for this... thank you!
Many thanks for sharing. Much appreciated.
Thanks, really looked for this one, many thanks,
Great tutorial and show/tell! thank you!
thank you so much! i've been googling all night for this finally found it!
Thanks for the help.
I was looking for this!!! Omg I'm so happy.i wasn't sure what it's called so im just glad to find this.need it for my new work.thanks a lot for sharing
Thank you. this helped so much. I finally got it to work.
This made the anxiety bubble in my head deflate. Thank you
That's a good explanation for vlookup? I know something about the excel and am now creating formulas in workspace based in the data I have, in order to help my team to work easier. I have a question now. Am creating a drop down using data validation and I want know howw to auto populate the fields. For example, if I enter the po Box number, I want the address to be populated inside the drop down. Obviously we cannot scroll and check; if I have more adresses. I've referred to some vids in TH-cam regarding this. They're using SEARCH, IFRRROR, IF, MAX, VLOOKUP AND OFFSET formulas to do it. It would be great, if you explain it in detail about this, as the way you explain things is Fantastic. I used to be like that in my office and thank u for the post.
Sorry that's not a question mark...
Many have asked for similar functionality and as far as I can tell, there's not a way to auto-complete data like that using this method. You may be able to get an add-on product that will help accomplish this or use VBA to make it work. Here's a link with some options that seem to do what you're wanting to do: stackoverflow.com/questions/19818075/excel-data-validation-with-suggestions-autocomplete I hope this helps!
Dear Sally, Thank you for this very easy to follow instructional. I wanted to know is there a way to do the same thing yet backwards? I want to create a form that will auto populate onto a spreadsheet.
Is there a similar feature available in Office 365 (Outlook)?
THANK YOU THANK YOU THANK YOU!! This has been the most understandable tutorial i have EVER seen!! However, I have a question...I allowed a blank cell in my list, so if I have no value to select, then I can just keep blanks in the autopopulate cells. When I select the blank in my drop down box, in the cell I wish the data to autopopulate it displays #N/A. How do I make the formula return a blank cell if there is no value selected in my drop down list.
Also, how would I use a drop down box to autopopulate data from 2 separate sources...for example, I have 2 manufacturers with multiple products and different prices for each. If I have a drop down for the product and a separate drop down for the manufacturers, i want the price box to reflect the price for the material and manufacturer selected. So if I select Wood (the product), then select PLAtech (Manufacturer), I want the result in the price box to return $10.95. Conversely, if I choose Nylon (Product), then select (Gerber Nylon), I want the price box to return $25.36.
Awesome! Many thanks for this.
This close to what I'm trying to do, but not quite. I do grocery deliveries and after trying out several mileage tracker apps I've decided to go old school and just use Excel. The only issue I'd have with it is typing repeat addresses, such as home or work, or repeat customers. What I'd like to do is make a list of recurring addresses and simply be able to type home, or the customers name, and have the full address appear on the log sheet. I intend to have a separate sheet for locations. Thanks for your help in advance.
Excel does not allow for an auto-lookup function as you describe, unfortunately, but this site has an option that seems easy to do: www.ozgrid.com/Excel/autocomplete-validation.htm. This seems really easy and doesn't require Visual Basic, just a hidden row of existing data. I hope this helps!
THANK YOU! THIS IS EXACTLY WHAT I NEEDED!!!!!!
Thank you for your help
Thank you so much been searching how to do this for a few hours
HI, Sally Joe here from Perth Australia, I love your videos, they have been very useful. I need guidance and help to finish an excel project. I have created a database and setup user form via vba to add and edit new contacts. It works well. I want to go to the next level. When I finish entering new entry via a command button, I want to automatically update cells in another sheet with some of the data I have just entered. for example. when we add a new contact I want to print a delivery label ( i have an excel template already in the next sheet of the workbook), the label will be used to ship them a sample pack, then a copy of the populated delivery dockets is saved in another file and the template gets unloaded??? regards Joe
This is an awesome video. I do have one question though. I am trying to apply the same vlookup formula to an entire column. I have the formula working in the first cell of the column but when I try and drag it down or copy and paste the formula, I get the #REF! error. Here is the formula I want to drag down: =VLOOKUP($C$3,'Expense Rates'!1:1048576,21,FALSE) Any help would be great!
Thanks
You should use HLOOKUP formula made pretty similar to VLOOKUP but horizontal.
Great presentations! Is there a way to make the same spreadsheet with multiple lookup values? In other words if I have a customer I want looked up not only by number, but lets say customer type?
Hey, Can you please create one video to get a popup calendar in Excel 365?
Hey Sally, thanks for the great video! It answered most of my questions very well, but I do have an additional question that would take this tutorial a step further. I have a sheet used to create user rights on RFID chips. Most instances can be taken care of by using a table referencing the rights to the job type (Vlookup), however, sometimes I need to break from that set table and create a custom set of rights. Is there anyway I can do a custom field entry on a vlookup cell without adding it to the table? Thanks in advance and I look forward to hearing from you.
thanks for the great video tutorial.
but how if the data we wanna auto populate in the drop down list is from another drop down?
@@SimpleSoftwareTutorials Woaaa.. never thought you're gonna reply me. And comes with real solution 😊
Havent try it yet, but i will. I really much appreciate it. Success for you Sally. Hope the best for you. Thank you