Рет қаралды 1,395
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