How to use INDIRECT Function in Excel

The INDIRECT function in Excel displays the contents of a specified dynamic reference. The INDIRECT Function is great because you can reference each attribute of the reference (workbook name, worksheet name, and cell reference) individually to create a dynamic excel formula.

Something to point out at this stage is that when referencing workbooks, the INDIRECT function only returns the result of a reference to an open file.  This means if a workbook that the INDIRECT function is referencing is closed, then you will get the #REF! error.

To create a reference to a workbook using three different cell inputs as references for the workbook, worksheet, and cell link, follow the steps in the following examples.

Example 1

1. Open Excel.
2. In Book1, Sheet1, cell A1 type Excel Geek.
3. In Microsoft Office Excel 2003 or earlier, click New on the File menu, click Workbook, and then click OK.In Microsoft Office Excel 2007, click the Microsoft Office Button, click New, and then click Create.
4. In Book2, Sheet1, cell A1 type Book1.
5. In Book2, Sheet1, cell A2 type Sheet1.
6. In Book2, Sheet1, cell A3 type A1.
7. Save both workbooks.
8. In Excel 2003 or earlier, type the following formula in Book2, Sheet1, cell B1:

=INDIRECT("'["&A1&".xls]"&A2&"'!"&A3)

In Excel 2007 or greater, type the following formula:

=INDIRECT("'["&A1&".xlsx]"&A2&"'!"&A3)

The formula returns Excel Geek.

Example 2

In Excel 2003 or earlier, you can replace the formula above with multiple INDIRECT statements, as in the following formula:
=INDIRECT("'["&INDIRECT("A1")&".xls]"&INDIRECT("A2")&"'!"&INDIRECT("A3"))

In Excel 2007 and Excel 2010, type the following formula:
=INDIRECT("'["&INDIRECT("A1")&".xlsx]"&INDIRECT("A2")&"'!"&INDIRECT("A3"))

Example 1 references cells A1, A2, and A3 without the use of quotation marks, wheras Example 2 references the cells using quotation marks around the references.

The INDIRECT function references cells without using quotation marks. This function evaluates the result of the cell reference. For example, if cell A1 contains the text C1 and cell C1 contains the word Excel, the formula =INDIRECT(A1) returns the result Excel.

However, referencing a cell with quotation marks returns the result of the cell contents. In the example in the previous sentence, the formula returns the text string C1 instead of the contents of cell C1 (Excel).

2,177 thoughts on “How to use INDIRECT Function in Excel

  1. reklamy,

    continuously i used to read smaller content that as well clear their
    motive, and that is also happening with this piece
    of writing which I am reading here.

  2. App Smart Iptv Samsung

    Ponto IPTV a melhor programacao de canais IPTV do Brasil, filmes, series, futebol, lutas, shows, documentarios em alta definicao de imagem e som em SD, HD, FULL HD, 4K. compativel com todos aparelhos do mercado Brasileiro.

  3. garotas de programa campinas

    I think everything posted was very logical. But,
    what about this? suppose you added a little information? I mean, I don’t
    want to tell you how to run your website, but suppose you added a headline that makes people want more?

    I mean How to use INDIRECT Function in Excel | is a little boring.
    You ought to look at Yahoo’s home page and note how they create article titles to grab viewers to
    open the links. You might add a video or a pic or two to get readers excited
    about what you’ve got to say. In my opinion, it could bring your website a little bit more interesting.

  4. Moedah

    The next time I read a blog, I hope that it does not fail me just as much as this particular one. I mean, I know it was my choice to read through, however I genuinely thought you would probably have something useful to say. All I hear is a bunch of crying about something you can fix if you were not too busy searching for attention.

  5. udfauzi

    After I initially commented I seem to have clicked the -Notify me when new comments are added- checkbox and now whenever a comment is added I receive 4 emails with the exact same comment. Is there an easy method you can remove me from that service? Many thanks!

  6. genolyze

    I really love your blog.. Pleasant colors & theme. Did you make this amazing site yourself? Please reply back as I’m hoping to create my own personal site and want to learn where you got this from or what the theme is called. Cheers!

  7. vivoslot

    Greetings! Very helpful advice within this article! It is the little changes that produce the biggest changes. Thanks a lot for sharing!

  8. vivoslot login

    I must thank you for the efforts you have put in penning this blog. I really hope to see the same high-grade blog posts from you later on as well. In truth, your creative writing abilities has inspired me to get my very own site now 😉

  9. daftar vivo slot

    Howdy! I could have sworn I’ve visited this site before but after looking at many of the articles I realized it’s new to me. Anyhow, I’m definitely delighted I found it and I’ll be bookmarking it and checking back frequently!

  10. agen vivoslot

    Hi, I do think this is an excellent website. I stumbledupon it 😉 I will come back once again since I book-marked it. Money and freedom is the greatest way to change, may you be rich and continue to help other people.

  11. 谷歌优化

    Hey there! Someone in my Myspace group shared this website with us so I
    came to give it a look. I’m definitely enjoying the information. I’m bookmarking and will be tweeting
    this to my followers! Terrific blog and fantastic style and design.

  12. vivoslot apk

    I blog quite often and I truly thank you for your information. Your article has truly peaked my interest. I am going to book mark your blog and keep checking for new details about once a week. I opted in for your Feed as well.

  13. togel sydney

    Hi there! This article could not be written much better! Reading through this article reminds me of my previous roommate! He continually kept talking about this. I’ll forward this information to him. Pretty sure he’ll have a great read. Many thanks for sharing!

Comments are closed.