The INDIRECT function uses a cell address or a range name as a data source you need to retrieve. INDIRECT functions often gather data from cells that have the same cell addresses but are in different worksheets. If A1 contains a sheet name, =INDIRECT(A1&"!B7") returns the value of B7 on that sheet.
- [Voiceover] On this worksheet called "INDIRECT One",…we've got some totals here,…and at first, it's not obvious where they're coming from,…but if you click on a cell, for example, like B3, you see…this is actually getting data from four other sheets,…California through New York,…and those sheets are to the left here,…and as I click on each sheet,…New York, Florida, Texas, California,…these sheets have exactly the same layout.…What we'd like to be able to do here, also,…is have a total for California right here.…Show the California total, the Texas total.…
So a simple little formula here,…I'll do it for California first, just manually,…"equal", click the California sheet,…the total's in that highlighted cell to the right.…It's in "G6".…And I'll press return.…On the other sheets, on Texas, Florida and New York,…just click one or two these we see,…the total here is in "G6", as it is…in the other states as well, too.…So, it would be great if we could somehow…copy this downward, but each time we do,…use a different state name.…
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: Return references with INDIRECT