filmov
tv
Dependent Dropdown Lists in Excel with Power Query & Dynamic Arrays
Показать описание
When we build models in Excel we must anticipate crazy entries. In this tutorial we try to prevent crazy matches between labels and containers. A 4x5 label won't fit on our 3-ounce spray bottle, and a 1x1 label would look crazy on a 1-gallon jug.
First, we use Power Query and Unpivot to organize our source data. Then, we use the new functions UNIQUE and FILTER to make the dependent drop down lists, taking advantage of Excel's new calculation engine.
Those work fine, but there's a known problem with dependent dropdown lists (aka cascading dropdown lists): it's easy to end up with crazy matches because the lists don't re-set. It's easy to select something with the child list and then change the parent list to something crazy. I handle this by using COUNTIFS and the WingDings font to flag crazy entries; i.e. create an alert.
#DynamicArrays
#DropdownLists
#DataValidation
======
For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
My book: Guerrilla Data Analysis 2nd Edition
First, we use Power Query and Unpivot to organize our source data. Then, we use the new functions UNIQUE and FILTER to make the dependent drop down lists, taking advantage of Excel's new calculation engine.
Those work fine, but there's a known problem with dependent dropdown lists (aka cascading dropdown lists): it's easy to end up with crazy matches because the lists don't re-set. It's easy to select something with the child list and then change the parent list to something crazy. I handle this by using COUNTIFS and the WingDings font to flag crazy entries; i.e. create an alert.
#DynamicArrays
#DropdownLists
#DataValidation
======
For an intro to Get & Transform (Power Query) try my Lynda/LinkedIn course:
My book: Guerrilla Data Analysis 2nd Edition
0:07:16
Create multiple dependent drop-down lists in Excel [EASY]
0:11:57
Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)
0:10:59
Make Multiple Dependent Dropdown Lists in Excel (Easiest Method)
0:12:10
Create Dependent Drop Down List in Excel - EASY METHOD
0:11:10
Dependent Drop Down List in Excel Tutorial
0:04:50
MS Excel - Dependent Dropdown List
0:00:54
Multiple Dependent Drop Down List In Excel! Learn this in one minute! 😎 #excel #exceltips
0:11:15
NEW Excel Drop-Down Lists That Adapt to Your Data
0:01:09
Create dependent dropdown lists in excel🤯 #shorts #excel #spreadsheetmagic #dataextractor
0:15:29
Creating Multi-Level Dependent Drop Down Lists in Excel - New and Better Way!
0:00:58
Create a Dependent Drop-Down List in Excel - Excel Tips & Tricks
0:15:03
Multiple Dependent Drop-Down List in Excel | NEW Simple Method | Works with multiple rows
0:03:50
Excel Create Dependent Drop Down List Tutorial
0:11:42
Quickly Create Multiple Dependent Drop-Down Lists in Microsoft Excel
0:07:35
Even easier than easiest - Multi Level Dependent Drop Down Lists
0:04:36
Create dynamic Dependent Drop-Down Lists in Excel
0:07:39
Awesome Trick to Get Dependent Drop Downs in Excel (works for multiple rows too)
0:09:20
Make Multiple Dependent Dropdown Lists In Excel (Easiest Method) | Step by Step
0:12:02
Create Multiple Dependent Drop Down Lists in Excel (Demonstration with Example up to 3 Levels)
0:05:22
Dependent Dropdown Lists in Excel | Excel Tips & Tricks 017
0:07:24
Excel Dependent Drop Down List with Tabular Data without Named Ranges
0:00:52
Dependent Dropdown in Excel‼️ #excel
0:00:56
How to Make Automatic Dependent Drop-Down Lists in Excel | Easy Steps
0:15:55
Smart Dependent Drop-Down Lists in Excel: Expandable & Exclude Blank Cells
Комментарии