Multiple dependent drop-down list in Excel (using OFFSET)

Hi everyone. It's easy to create MULTIPLE dependent drop-down lists in Excel. In this video, I'm showing a solution that only requires one single formula of OFFSET. Also, if you used UNIQUE & FILTER, and shared the file with someone who doesn't have paid version of Office 365, they will not be able to view the lists or it may display as error. This formula is a quick and great solution to overcome that problem. Furthermore, this formula also excludes blank cells on the bottom of the data validation list. Enjoy!
00:00 Multiple Dependent drop-down list using UNIQUE & FILTER
03:03 Multiple Dependent drop-down list using OFFSET
06:20 Fixed number of choices in the dependent drop-down
08:36 Dynamic number of choices in drop-down
Fixed number of choices in the dependent drop-down:
If you do not mind empty spaces (blank cells) at the bottom of the drop down you can fix the height argument in the OFFSET function to a specific (maximum) number of choices.
Dynamic number of choices in drop-down:
If we want to restrict the number of visible cell values inside the dependent drop-down we can expand the formula to get rid of the blank cells on the bottom. This way we can eliminate empty spaces in the drop down selection. We will use another OFFSET formula inside a COUNTA formula to dynamically count the number of available choices.
#AUS #wwwausedu #BecauseTomorrowMatters #Proud_AUSer #worthit #UAE #nazyamm #nazyam #microsoft #Excel #msexcel #practicemakespermanent #Excelformula #exceltutorial #exceltips #excelfunction

[@marjorieherrera3031]

Thank you! I was trying this with offset and indirect in the data validation but that was not working out.

[@NazyaM]

Thank you so much for your comment, it means a lot to be sharing the knowledge and knowing it helped even a single person. If there are any other questions you would like me to cover, feel feel to reach out. Much love and God bless.

[@marjorieherrera3031]

@@NazyaM that’s so kind of you!
Thanks. I’ll make sure to search through your channel or ask if I find another road block. I’m usually working with Power BI, but sometimes I have to do a few things in excel😅