The Magic of the INDIRECT Function of Excel - Five Examples of how to Use it

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
6 Lessons (20m)
    • 1. Introduction

      2:35
    • 2. Using INDIRECT with Named Ranges

      2:49
    • 3. Referencing other Sheets with INDIRECT

      4:27
    • 4. Return the Last Value from a Row

      4:24
    • 5. Using INDIRECT with VLOOKUP

      2:27
    • 6. Create Dependent Drop Down Lists

      3:14

Project Description

Open the indirect-project.xlsx file.

  1. On the Dependent Lists sheet, name the range E2:E5 as romance. The other ranges have already been named.
  2. Create a Data Validation list in cell G2 that uses the genre named range as its source.
  3. Create a Data Validation list in cell H2 that is dependent upon the selection in cell G2.
  4. On the Sum sheet, in cell C3 write a SUM function to sum the values of column B of the month selected from the list in cell B3.

Student Projects