• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • SALE 20% Off All Courses
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Logout
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel INDIRECT Function

You are here: Home / Excel Formulas / Excel INDIRECT Function
Excel INDIRECT Function
December 14, 2011 by Mynda Treacy

The Excel INDIRECT Function has many applications and perhaps its simplest is to fix a range of cells you want to reference.

For example a standard SUM function looks like this:

=SUM(B16:B24)

If you insert a row within this range the formula dynamically updates and becomes:

=SUM(B16:B25)

It will dynamically update even if you absolute the reference like this:

=SUM($B$16:$B$24)

To stop the reference dynamically updating when you insert rows or columns use the INDIRECT function like this:

=SUM(INDIRECT("B16:B24"))

In most cases you’d want your formulas to dynamically update when you insert new rows or columns but there are some instances when this is a nuisance and that’s when INDIRECT is your ally.

How Does The INDIRECT Function Work?

Excel’s INDIRECT function returns a reference specified by a text string.

The text string can be obtained by:

  • referencing another cell, or
  • you can enter it in double quotes, or
  • you can generate it by nesting other functions

You can also use the ampersand (&) symbol to concatenate text and build your text string that way. More on using the ampersand in a moment.

Notice how the range specified in the INDIRECT function below is surrounded by double quotes, thus making it a text string.

=SUM(INDIRECT("B16:B24"))

INDIRECT Function Syntax

The syntax is:

=INDIRECT(ref_text,[a1])

ref_text – is the text string (like in the example above "$B$16:$B$24") or the cell you’re referring to containing the text string.

a1 – this is asking you if your reference uses the A1 reference style or R1C1 style. To use the A1 style* simply omit this argument, but if you want to use R1C1 enter ‘FALSE’.

*A1 reference style is what you’re probably familiar with and it is where the columns have letters and the rows have numbers. The R1C1 style uses the R to specify a row and then the number of that row, and the C to specify a column and then the number of that column.

In the example below the INDIRECT function in cell A4 is evaluating the contents in cell C4. I could have also typed this formula like this:

=INDIRECT("C4") and my result would again be ‘This text’.

INDIRECT Excel

Ok, that’s enough theory.

Other Uses for INDIRECT

1. Generate a Reference on the Fly

By nesting it with other functions we can generate references like this one:

=SUM(INDIRECT("B27:B"&ROW(B35)))

In this example we’ve used the ROW function which returns the row number of the selected cell, in this example it’s 35. Our formula above evaluates to:

=SUM(B27:B35)

Well why didn’t I just type in =SUM(B27:B35) I hear you say. And fair enough too.

The point of this example was to give you a taste of how you can nest other functions in the INDIRECT function to derive cell references. I recommend you try nesting other referencing functions like COLUMN, ADDRESS, and CELL.

Referencing Other Worksheets and Workbooks

You can reference other worksheets and workbooks with the INDIRECT function too.

References to other workbooks must be formatted like this:

=INDIRECT(" '[your_workbook_name.xlsx]your_sheet_name'!$A$3")

References to other worksheets must be formatted like this:

=INDIRECT(" 'your_sheet_name'!H34")

Note 1: spaces between double quotes and apostrophe (“ '[your) are so you can clearly see all of the components. You don’t actually put a space in your formula.

Note 2: you can reference other worksheets and workbooks but if you reference another workbook it must be open otherwise you will get a #REF! error.

2. Use INDIRECT with a Named Range

This is probably the second most useful application for the INDIRECT function.

Let’s say you’ve got 4 named ranges of data; North, South, East, West.

INDIRECT Excel

You’ve got a drop down list (data validation list or combo box) that allows you to choose from the 4 different regions (with names that are exactly the same as the named ranges you’ve set up for your regions).

When you make your choice the report automatically updates with the relevant figures without the need for any IF Functions or other jiggery pokery.

INDIRECT Excel

You can use the INDIRECT function in any formula that requires a reference to a cell or range of cells.

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the workbook and look at the examples in more detail.

Excel INDIRECT Function

More Excel Formulas Posts

top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions

TEXTSPLIT, TEXTBEFORE and TEXTAFTER Functions

TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.

Top 10 Intermediate Excel Functions

Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.




Category: Excel Formulas
Previous Post:Excel VLOOKUP Multiple ValuesExcel VLOOKUP Multiple Values
Next Post:INDEX MATCH With a TwistINDEX MATCH With a Twist

Reader Interactions

Comments

  1. Dr. Prakash Kulkarni

    March 16, 2018 at 3:40 pm

    In this formula =INDIRECT(“Abst!Z”&AD8+3) I want to add MG/DL. How can I do that? I tried =INDIRECT(“Abst!Z”&AD8+3)+”MG/DL” & few other combinations.

    Reply
    • Catalin Bombea

      March 17, 2018 at 3:14 pm

      Hi,
      You can build references from text only inside INDIRECT argument, can you clarify what + “MG/DL” should do?

      Reply
  2. Taha

    December 20, 2016 at 1:34 pm

    Hi there,
    I have a summary sheet where I intend to populate data from 25 sheets i.e. AA, AB, AC, AD and so on. My Cell 30 of Sheet AA = “Canada (150)”. I used following the formula to extract “Canada” and “150” in separate fields.

    Canada = LEFT(C30,FIND(” (“,C30&” (“)))
    150 = (MID(C30,SEARCH(“(“,C30)+1,SEARCH(“)”,C30)-SEARCH(“(“,C30)-1))

    I am wondering if I could use INDIRECT Function to extract data. Kindly advise.

    Reply
    • Catalin Bombea

      December 21, 2016 at 3:13 am

      Hi Taha,
      INDIRECT is meant to allow you to create cell references from text strings, not for extracting data, with its specific advantages and disadvantages. To extract data from a large number of sheets, Power Query is your friend, if the structure is the same in all sheets. If you don’t have the possibility to use it, you can use as you said the INDIRECT function to build a 3D reference, have a look at this tutorial
      Catalin

      Reply
  3. mano

    November 1, 2014 at 9:05 am

    thanks fully

    Reply
  4. kayakbob

    May 24, 2013 at 11:59 pm

    Mynda – This function is considerably more practical for my current need than a series of embedded IF statements. I would never have even thought to look for a function like this without your tutorials. Thanks so much!

    kayakbob

    Reply
    • Mynda Treacy

      May 25, 2013 at 8:51 am

      Thanks, Kayakbob 🙂 Glad to have helped.

      Reply
  5. venkat

    May 1, 2013 at 1:03 am

    very good introductory lesson on indirect function. one can work on it. extremely usefu. Indirect function is one of those fucntion whilch confuses even those who are familiar with excel and excel vba. thanks a lot. is there any advanced lessons on this funcion.

    Reply
    • Mynda Treacy

      May 1, 2013 at 10:06 am

      Thanks, Venkat 🙂

      Reply
  6. Terry Sawyer

    September 10, 2012 at 6:13 pm

    It’s really a nice and useful piece of information. I am satisfied that you simply shared this helpful info with us. Please keep us informed like this. Thanks for sharing.

    Reply
    • Mynda Treacy

      September 11, 2012 at 9:00 am

      🙂 Cheers, Terry.

      Reply
      • Bill Richards

        March 28, 2013 at 2:46 am

        I have the following MAX OFFSET formula in one workbook that is referencing to another workbook. It returns a #VALUE! until the source workbook is open. Once opened data appears and is updated. I’m hoping there’s a way to make it a MAX INDEX formula or some sort of MAX LOOKUP formula to resolve this issue, but I just can’t seem to figure it out completely.

        =MAX(OFFSET(‘[C:\Desktop\Book1.xlsx]1’!$G$34,0,0,1,$A$1))

        I came up with:
        =MAX(‘[C:\Desktop\Book1.xlsx]1’!$G$34:INDEX(‘[C:\Desktop\Book1.xlsx]1’!$G$34:$AP$34,0,$A$1))

        This works fine if both workbooks are open, but when only Book2 is open it returns the correct results when a 1 or 2 is input into $A$1 & the result stays without going back to #VALUE!. Great!! However; any other number (3 through 36 in this case) returns #REF!. Open Book1 a correct result appears. Close both, Open Book2 #REF! again.

        Can you help?

        Formula is in cell A3 for Book1.xlsx sheet 1; in cell A4 for Book1.xlsx sheet 2 and so on.

        Results – If I type 4 in A1 of Book2.xlsx; then it returns the max value within Book1.xlsx G34, H34, I34, J34 (below example – Answer 199). If I type 36; then it goes through AP34; which is the last cell with data (below example – Answer 241). Row 19 in BOOK1.XLSX does have numbers 1-36.

        BOOK2.XLSX

        A B
        1 4
        2
        3 =MAX(OFFSET(‘[C:\Desktop\Book1.xlsx]1’!$G$34,0,0,1,$A$1))
        4 =MAX(OFFSET(‘[C:\Desktop\Book1.xlsx]2’!$G$34,0,0,1,$A$1))

        BOOK1.XLSX Sheet 1

        G H I J K AP
        19 1 2 3 4 5 36

        34 186 199 145 190 241 200

        Reply
        • Carlo Estopia

          March 28, 2013 at 3:17 pm

          Hi Bill,

          Please send this via HELP DESK.

          Thanks.

          CarloE

          Reply
  7. terry Sawyer

    September 4, 2012 at 11:05 am

    Heya i’m for the primary time here. I found this board and I find It really helpful & it helped me out a lot. I’m hoping to
    give one thing back and aid others such as you helped me.

    Reply
    • Mynda Treacy

      September 4, 2012 at 5:03 pm

      Cheers, Terry. Glad you found it helpful 🙂

      Reply
  8. Yasir Khan - online marketer

    January 2, 2012 at 6:44 pm

    Thank you for sharing these tips Mynda 🙂

    You portal is well organized and lightening for Excel’s lovers.

    -Yassir

    Reply

Trackbacks

  1. Slicer Controlled Interactive Excel Charts • My Online Training Hub says:
    August 12, 2016 at 9:15 am

    […] simpler approach would be to use the INDIRECT function like […]

    Reply
  2. What the CELL! • My Online Training Hub says:
    October 30, 2014 at 12:05 pm

    […] above =CELL(“filename”) formula can also be used in INDIRECT formulas to populate the worksheet name but you need to isolate just the worksheet name component from the […]

    Reply
  3. INDEX MATCH With a Twist • My Online Training Hub says:
    September 14, 2014 at 12:10 pm

    […] INDIRECT function returns a reference specified by a text […]

    Reply
  4. Excel Data Validation With Dependent Lists • My Online Training Hub says:
    September 14, 2014 at 11:43 am

    […] More on the INDIRECT Function. […]

    Reply
  5. Excel Factor 15 The Lazy Lookup • My Online Training Hub says:
    September 14, 2014 at 10:13 am

    […] in laziness, get someone else to build the formula by giving them data validation lists and use the INDIRECT function to build the formula on the […]

    Reply
  6. Excel VBA List Sheet Names says:
    June 17, 2014 at 2:02 pm

    […] use them to create references on the fly with the INDIRECT function and much […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Course Sale

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
trustpilot excellent rating
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.