Популярное

Музыка Кино и Анимация Автомобили Животные Спорт Путешествия Игры Юмор

Интересные видео

2025 Сериалы Трейлеры Новости Как сделать Видеоуроки Diy своими руками

Топ запросов

смотреть а4 schoolboy runaway турецкий сериал смотреть мультфильмы эдисон
dTub
Скачать

Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)

Автор: Leila Gharani

Загружено: 2020-03-26

Просмотров: 1970492

Описание:

🔥 400,000+ professionals trust our courses—start your journey here 👉 https://link.xelplus.com/yt-d-all-cou...

Learn how you can create MULTIPLE dependent data validation lists in Excel on the SAME sheet using a single formula. This formula also excludes blank cells on the bottom of the data validation list.

⬇️ DOWNLOAD the workbook here: https://pages.xelplus.com/multiple-de...

👩‍🏫 Step-by-Step Tutorial:
Start with Basic Drop-Downs: Learn how to create a basic drop-down list for divisions.
Tackle the Challenge: Dive into creating a dependent drop-down list for apps based on the division selected, using the OFFSET formula.
Formula Magic: Discover how to dynamically change the drop-down contents using OFFSET and MATCH functions.
Avoid Empty Choices: Learn to dynamically adjust the number of choices in your drop-down to avoid empty selections.
Apply to Multiple Rows: Easily copy and paste the validation to multiple rows for a consistent, user-friendly experience.

🔧 Key Techniques Covered:
OFFSET Function: Understand the OFFSET function for dynamic range selection.
MATCH Function: Use MATCH to locate and select the correct column based on division.
Dynamic Counting: Implement COUNTA with OFFSET for dynamic list sizing.

In our example the selection of a division will drive the available choices for an app in an Excel data validation list. We'll use the Excel OFFSET function and the MATCH function for the dependent drop down selection. This way you can have as many categories as you like.

The challenge we have is that the number of choices inside the dependent drop down may vary depending on the division. I'll show you 2 solutions for this:

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.

00:00 Dependent Drop-Down Lists in Excel on EVERY Row
01:20 Creating the First Drop Down
01:58 Creating the Dependent Drop Down List
05:55 Fixed Number of Choices in the Dependent Drop-Down
07:15 Dynamic number of Choices in Drop-Down
10:21 Copy Down Data Validation

LINK to related OFFSET video:    • Excel OFFSET Function for Dynamic Calculat...  
Complete Excel Data Validation Playlist:    • Drop Down Lists in Excel (Dependent, Uniqu...  

➡️ Join this channel to get access to perks:    / @leilagharani  

🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/

🎥 RESOURCES I recommend: https://www.xelplus.com/resources/

🚩Let’s connect on social:
Instagram:   / lgharani  
LinkedIn:   / xelplus  

Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!

#excel

Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)

Поделиться в:

Доступные форматы для скачивания:

Скачать видео mp4

  • Информация по загрузке:

Скачать аудио mp3

Похожие видео

array(10) { [0]=> object(stdClass)#6138 (5) { ["video_id"]=> int(9999999) ["related_video_id"]=> string(11) "57MARBvYVs4" ["related_video_title"]=> string(53) "Excel DGET Function Solves 2 of Your VLOOKUP Problems" ["posted_time"]=> string(19) "5 лет назад" ["channelName"]=> string(13) "Leila Gharani" } [1]=> object(stdClass)#6111 (5) { ["video_id"]=> int(9999999) ["related_video_id"]=> string(11) "RXSH2UiDc5g" ["related_video_title"]=> string(49) "NEW Excel Drop-Down Lists That Adapt to Your Data" ["posted_time"]=> string(27) "9 месяцев назад" ["channelName"]=> string(19) "MyOnlineTrainingHub" } [2]=> object(stdClass)#6136 (5) { ["video_id"]=> int(9999999) ["related_video_id"]=> string(11) "VxReJslyJm8" ["related_video_title"]=> string(78) "Smart Dependent Drop-Down Lists in Excel: Expandable & Exclude Blank Cells" ["posted_time"]=> string(19) "6 лет назад" ["channelName"]=> string(13) "Leila Gharani" } [3]=> object(stdClass)#6143 (5) { ["video_id"]=> int(9999999) ["related_video_id"]=> string(11) "VQTrwc-eMVk" ["related_video_title"]=> string(59) "Infinite Multiple Dependent Dropdown Lists In Google Sheets" ["posted_time"]=> string(19) "1 год назад" ["channelName"]=> string(8) "Hash Ali" } [4]=> object(stdClass)#6122 (5) { ["video_id"]=> int(9999999) ["related_video_id"]=> string(11) "FGOeHbv0L2g" ["related_video_title"]=> string(73) "How to Create a Dependent Drop Down List in Excel (Step-by-Step Tutorial)" ["posted_time"]=> string(19) "1 год назад" ["channelName"]=> string(15) "Kevin Stratvert" } [5]=> object(stdClass)#6140 (5) { ["video_id"]=> int(9999999) ["related_video_id"]=> string(11) "4c0CLUER6nw" ["related_video_title"]=> string(65) "How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 Examples)" ["posted_time"]=> string(19) "5 лет назад" ["channelName"]=> string(13) "Leila Gharani" } [6]=> object(stdClass)#6135 (5) { ["video_id"]=> int(9999999) ["related_video_id"]=> string(11) "8l9Wp0T_SOE" ["related_video_title"]=> string(89) "Multiple Dependent Drop-Down List in Excel | NEW Simple Method | Works with multiple rows" ["posted_time"]=> string(19) "1 год назад" ["channelName"]=> string(30) "Rebekah Oster - Excel Power Up" } [7]=> object(stdClass)#6145 (5) { ["video_id"]=> int(9999999) ["related_video_id"]=> string(11) "RnHC1XiNWS8" ["related_video_title"]=> string(94) "Венедиктов – страх, Симоньян, компромиссы / вДудь" ["posted_time"]=> string(19) "3 дня назад" ["channelName"]=> string(10) "вДудь" } [8]=> object(stdClass)#6121 (5) { ["video_id"]=> int(9999999) ["related_video_id"]=> string(11) "a3-nkc_VUF0" ["related_video_title"]=> string(123) "Эти ОШИБКИ совершает КАЖДЫЙ новичок в Excel. Избавься от них НАВСЕГДА!" ["posted_time"]=> string(19) "3 дня назад" ["channelName"]=> string(54) "Эксперт ЭКСЕЛЬ и ГУГЛ-ТАБЛИЦЫ" } [9]=> object(stdClass)#6139 (5) { ["video_id"]=> int(9999999) ["related_video_id"]=> string(11) "FRiFfKb_B_A" ["related_video_title"]=> string(60) "Create SMART Drop Down Lists in Excel (with Data Validation)" ["posted_time"]=> string(19) "7 лет назад" ["channelName"]=> string(13) "Leila Gharani" } }
Excel DGET Function Solves 2 of Your VLOOKUP Problems

Excel DGET Function Solves 2 of Your VLOOKUP Problems

NEW Excel Drop-Down Lists That Adapt to Your Data

NEW Excel Drop-Down Lists That Adapt to Your Data

Smart Dependent Drop-Down Lists in Excel: Expandable & Exclude Blank Cells

Smart Dependent Drop-Down Lists in Excel: Expandable & Exclude Blank Cells

Infinite Multiple Dependent Dropdown Lists In Google Sheets

Infinite Multiple Dependent Dropdown Lists In Google Sheets

How to Create a Dependent Drop Down List in Excel (Step-by-Step Tutorial)

How to Create a Dependent Drop Down List in Excel (Step-by-Step Tutorial)

How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 Examples)

How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 Examples)

Multiple Dependent Drop-Down List in Excel | NEW Simple Method | Works with multiple rows

Multiple Dependent Drop-Down List in Excel | NEW Simple Method | Works with multiple rows

Венедиктов – страх, Симоньян, компромиссы / вДудь

Венедиктов – страх, Симоньян, компромиссы / вДудь

Эти ОШИБКИ совершает КАЖДЫЙ новичок в Excel. Избавься от них НАВСЕГДА!

Эти ОШИБКИ совершает КАЖДЫЙ новичок в Excel. Избавься от них НАВСЕГДА!

Create SMART Drop Down Lists in Excel (with Data Validation)

Create SMART Drop Down Lists in Excel (with Data Validation)

© 2025 dtub. Все права защищены.



  • Контакты
  • О нас
  • Политика конфиденциальности



Контакты для правообладателей: [email protected]