Excel - Dependent Drop Down List Without Indirect with Automatic Updates & No Named Ranges
HTML-код
- Опубликовано: 21 авг 2024
- Learn how to create dependent dropdown list in Excel with automatic updates with no effort from the user to make changes. In this tutorial I don't use named ranges or INDIRECT to apply data validation, instead I have OFFSET, MATCH & COUNTA functions doing the magic.
#excel #dropdownlist #learn
Thank you so much. IT did what I needed it to do AND I understand all those numbers and commas more. I am going to look for other things you have done because you teach this in a way my brain gets.
Just what I was looking for! Thank you so much, greatly appreciated.
Nice work man. It helps me a lot. I am facing an error while validating data. It say;" you can't select data from another sheet." Will you help me to sort out this
Sorry, but it's impossible to know what happened by reading your comment.
Thx it's helpful but can we do the same in Google sheet. I can't find the 'list' in data validation.?
In the video at 8 min 17 sec you have shown data validation- allow type list- in source we paste formula that created by using offset counta and match function. But we use reference data from option sheet. My problem is while entering formula in source it says; you cannot use references to other worksheet for data validation criteria
Try using INDIRECT. You can see an example of that somewhere half way through this video ruclips.net/video/NhEqCqt-nl4/видео.html
Hi - You have gotten me so close to what I need, however, my headers go across 11 columns with the longest column having 55 rows of data. The formula =OFFSET(Options!$A$1,1,MATCH(D10,Options!$1:$1,0)-1,COUNTA(Options!$A:$A)-1) works correctly; but it is only showing 5 rows of data and never going any further down. Might you be able to help me get the formula to go to show all 55 rows of data when applicable? Any help would be greatly appreciated.
I was able to figure out the issue [=OFFSET(Options!$A$1,1,MATCH(E10,Options!$1:$1,0)-1,COUNTA(Options!$A$1:$K$55)-1], but wanted to say thank you very much for your video! The way you present information is awesome & extremely helpful.
When I type in formula I'm getting all results showing in the row, as opposed to just one. Can someone please tell me why this might be. I'm typing the formula exactly as demonstrated.
I keep getting a #REF! error Array result was not expanded because it would overwrite data in B7. I'd really like this to work correctly. Hopefully you can help.
Can we use formulas for dropdown list in Google Sheets?
No.
if you looking for Dependent Drop down yes you can. Here is and example docs.google.com/spreadsheets/d/1Vf8A8wVQg7ynWltIOSZ4n_R9MyB-P3KMOagQmNgj6TE/edit#gid=0 I just put this together really fast
let me ask why are you doing things on Excel. Your name is "Learn Google Spreadsheets
" You might want to start another channel up as "Learn Excel Spreadsheets
". There are very few channels showing Google sheets. There are plenty explaining Excel. I lean from Excel then try to put into google sheets. Many times I had to make the formula work. To make it work like Excel. I was a fan of Excel. with Excel if you do not save your work it is lost. You might have spent hours working on your project pooph
. the power goes out. Doodle this does not happen. I started in Excel back in 1998. It has been the last 5 years I have been using Google and I was hooked. Yes Google does not have all the bells and whistles like Excel. But in the last 5 years I have seen so many changes. Me I have never written script every time I do something goes wrong. I just stick with formulas. I watched your videos on scrip but I get very confused. I am used to making formulas to do the same thing. What I am saying You need to stay on Google sheets and show people things. What you showing now I learned years ago on another channel.
Thanks for your input.