Use the INDIRECT function, along with pick lists (set up with Data Validation) to allow one set of lookup values be based on the value chosen by selecting a value in another pick list. The INDIRECT function uses range names created for the pick list that depend on the value selected in the first pick list.
- [Voiceover] In this worksheet called Indirect-2…we've got some order numbers in column A,…potentially a huge list.…What we'd like to be able to do here,…is record the state and city where the order occurred.…But instead of people typing entries here,…we want them to pick from a list.…Now, let's first set up this idea.…You might not be familiar with how this is done.…It's relatively straightforward.…Let's say we've got a master list of states somewhere.…And let's say we do in this case.…Later this list might grow,…for the moment here's our list, here.…Now, we can set up what's called a pick list,…that's an unofficial term we use in Excel at times.…
This is going to be based on a capability…called Data Validation.…In column B, we'd like to set up a pick list.…Now we do this first by selecting the area,…often it's going to be an entire column, not always,…then go to the Data tab in the ribbon…and choose Data Validate.…Now, there are lots of options we can choose here…under the Allow banner, right here, on the settings tab.…
Author
Dennis Taylor
Released
5/26/2016- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome46s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
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: INDIRECT with Data Validation for two-tiered pick list scenarios