- 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
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.
Related Courses
-
Excel Tips Weekly
with Dennis Taylor32h 57m Intermediate -
Excel 2016 Essential Training
with Dennis Taylor8h 53m Beginner -
Excel 2016: Pivot Tables in Depth
with Curt Frye3h 42m Intermediate
-
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
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press 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