Join Dennis Taylor for an in-depth discussion in this video Using INDIRECT with Data Validation for two-tiered pick list scenarios, part of Excel 2016: Advanced Formulas and Functions.
- In this worksheet called INDIRECT-2…we're setting up a list in columns A through D…and we want data entry to be simple…and straightforward.…Instead of typing state names here…we want users to be able to pick from a list.…And if, for example, a person were to pick Arizona…then we'd like to have a pick list here…that shows only Arizona cities.…We're seeing some of these to the right.…Picking California,…have a pick list here to show only the California cities.…So we can do this by way of Data Validation…and also using the function INDIRECT.…
Let's start with the idea that we simply…want to set up a pick list here for column C.…We'll be using Data Validation.…The state names that we want to appear here…are for the moment starting in cell F1.…This list that we see to the right…eventually would be better placed on a different worksheet,…it's just fine for right now.…And it's good to have it here as we check out…some of the things that we want to do with these pick lists.…So selecting column C,…and there will be times that you…
Author
Dennis Taylor
Updated
3/1/2016Released
1/28/2016- Displaying and highlighting formulas
- Converting formulas to values
- Tabulating data from multiple sheets
- Understanding the hierarchy of operations in formulas
- Using absolute and relative references
- Creating and expanding nested IF statements
- Looking up information with VLOOKUP, MATCH, and INDEX
- Using the powerful COUNTIF family of functions
- Analyzing data with statistical functions
- Calculating dates and times
- Analyzing data with array formulas and functions
- Extracting data with text function
Skill Level Advanced
Duration
Views
Q: This course was updated on 03/01/2016. What changed?
A: We added one tutorial about the new formulas in Excel 2016.
-
Introduction
-
Welcome50s
-
-
1. Formula and Function Tips and Shortcuts
-
Using Auditing tools6m 15s
-
2. Formula and Function Tools
-
3. IF and Related Functions
-
IF logical tests4m 27s
-
-
4. Lookup and Reference Functions
-
Nesting lookup functions4m 12s
-
5. Power Functions
-
Using MAXIFS and MINIFS4m 14s
-
Preventing double counting4m 48s
-
6. Statistical Functions
-
7. Math Functions
-
8. Date and Time Functions
-
9. Array Formulas and Functions
-
10. Reference Functions
-
11. Text Functions
-
12. Information Functions
-
Conclusion
-
Next steps33s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Using INDIRECT with Data Validation for two-tiered pick list scenarios