Use Excel INDIRECT to dynamically refer to worksheets

Sometimes you want to make a reference to certain worksheets dynamically using the Excel indirect function. For example if you have data in the same format split over multiple worksheets and you want to select data from different sheets dynamically.

In this case, you can use the INDIRECT() function, which is available in both Excel and Google Spreadsheets. I’ll show you how to use it by running through a quick example.

INDIRECT():Returns the reference specified by a text string. References are immediately evaluated to display their contents.

Using Indirect() to dynamically refer to a worksheet

In the following example we want to look at the revenues of a specific product by dynamically referring to the data sheet of each product.

We have a sheet Revenues in which we want to see the revenue for a specific product. Next, we have two sheets with sales and revenue data for two product: Product1 and Product2.

Normally you can only reference to one worksheet
Normally you can only reference to one worksheet

We want sheet Revenues to show the revenues of one specific product and to be able to update the numbers dynamically when we change cell D1.

Unfortunately we can’t use a reference to D1 to specify the sheet we want in our VLOOKUP() function like this:

We can't use a reference to D1 to specify the sheet we want in our VLOOKUP() function like this
We can’t use a variable reference to D1 in our VLOOKUP()

For this to work, we have to use the INDIRECT() function. It allows us to use the value of cell D1 for creating a dynamic VLOOKUP referring to ranges on multiple sheets.

Using sheet names as variables with Indirect()
Using sheet names as variables with Indirect()

Now you can change cell D1 to “Product2” and the revenue numbers will dynamically update and get the numbers from the second worksheet.

Indirect() in Excel

So to recap, you can use INDIRECT() to refer to multiple worksheets variably like this:

=INDIRECT("'"&D1&"'!"&"A3:D6")

Indirect() in Google Spreadsheets

You can use INDIRECT() in Google Spreadsheets in a similar way:

=INDIRECT(CONCATENATE(D1,"!A3:D6")

As always, let me know in the comments whether this approach worked or if you have any questions!

  • Courtney

    This function works correctly in the previous version of Google Sheets, but for some reason it does not work with New Google Sheets.

    • benj

      You have to use “;” instead of “,”

    • Hey Courtney,
      I just tried it like it says, and for me it still works.

      Try this simpler function first: =INDIRECT(CONCATENATE(D1,”!A3″)

      And make sure you have something in cell A3 on the sheet you’re referring to.

      Does it work?

  • Craig Grant

    Thanks for the tip. Is there a way to do this using an INDEX/MATCH instead of a VLOOKUP? An example of my original formula is =INDEX(Players!$I$2:$I$353,MATCH(C2,Players!$E$2:$E$353,0)). If “Players” is my first sheet, then “Players2” would be my second sheet. Thanks in advance.

    • robotik

      let’s say you put the first sheet’s name in D1, as in the example above. then your formula is
      =INDEX(INDIRECT(“‘”&$D$1&”‘!$I$2:$I$353”),MATCH(C2,INDIRECT(“‘”&$D$1&”2’!$E$2:$E$353”),0))

  • Chloe

    Great example, thanks.
    I want to populate a large spreadsheet with this indirect formula, looking up on multiple sheets at once.

    I have two questions:

    Is there a way to write the formula so it sums across the product sheets, without just doing + INDIRECT(“‘”&E1&”‘!”&”A3:D6”) – if E1 was going to contain the word Product2? This is because the formula will get really long if I use it across 20 sheets.

    Also, as I want to use the formula across an entire sheet, is there a way of doing this so that the cells A3:D6 are not locked as text, because they won’t change automatically when I drag it and the process will be very laborious.
    Thanks in advance!

  • Kevin Rico

    This works GREAT!!!!!!!!!!!!!!!!! thanks very much :)

  • dobot

    Hi, if i would like to combine indirect function, if function and data validation list. Is it possible? How does it works? Please kindly help. Thanks

    • Yes it is definitely possible.

      =IF(B4=0,0,VLOOKUP(B4,INDIRECT(“‘”&$F$1&”‘!”&”A”&”:”&”B”,2,FALSE))

      F1=Customer List

      The formula says
      If B4=0, then this cell equals 0, if not then compare value B4 to a sheet specified by column F1 (Customer list!) and search all rows in that sheet in column A for the value in B4 and return the value in the same row but from column B from that sheet.

      Not sure why you would need the indirect formula in this, but here you go!

  • I was having the same problem as Chloe’s second question. I was trying to apply this formula across 1000 cells and dragging did not work. I just figured it out.

    Here’s my formula:
    =INDIRECT(“‘”&$D$1&”‘!”&M1)

    In cell M1 text says D1. Then I dragged that formula in cell M1 down to read D1, D2, D3 and so on. Then when you drag the INDIRECT formula down it references M1, M2, M3, and so on.

    For yours your formula needs to read A3:D6, A4:D7, A5:D8 and so on. Excel will not recognize the pattern A3:D6, A4:D7, A5:D8. So simply create another column next to M1=A3 reading N1=D6., then adjust your formula to read.
    =INDIRECT(“‘”&$D$1&”‘!”&M1&”:”&N1)

    • Awesome, thanks for the explanation Stefan!

      • Soriba Sylla

        An INDIRECT formula can refer to cells in other workbooks, but the problem is that the result

        will return a #REF! error if that workbook is closed.

    • Mark

      Hey, buddy – great stuff. However, I came here for what is probably a simple question, but I can’t tell if you answered it yet:

      I have a spreadsheet to generate configurations based on named cells on another page. I have it so that the input are rows … but the only row referenced is the top one, with the named cells (referenced in another tab, as my configuration).

      So … in that first row, my first cell you enter the # of the row you want to populate your named cells with. It works well … except, if I insert a column, all of my INDIRECT() formulae keep referencing the column letter that they WERE, rather than dynamically remaining referencing the apppropriate cell below them.

      How can I accomplish this? I have many columns, and it gets tiresome checking each formula when I insert a new column.

      • Mark

        Looks like I might have to use COLUMNLETTER() in conjunction with INDIRECT() so that I pick the current column letter and concatenate it with the # I enter in my ‘iRow’ named cell.

        • Mark

          Well, that doesn’t exist … COLUMN() sort of does, but it returns the number of the letter … which I could probably accomplish (1=A, 2-B, etc) but it sounds like an awful lot of unnecessary work.

          • Mark

            http://superuser.com/questions/316239/how-to-get-the-current-column-name-in-excel has some ideas I may try … nested functions, but if they implemented ‘COLUMNLETTER()’ like someone suggested existed, my problem might be solved.

          • Mark

            It’s kludgy, but it works … and I am the unfortunate master of kludge (but, a kludge in the hand is worth two in the bush):
            =INDIRECT(CHAR(COLUMN()+64) & iRow)

          • Mark

            So … effectively … ‘COLUMNLETTER()’ would be the equivalent of ‘CHAR(COLUMN()+64)’. You would save 3 key-strokes and it is easier to read.

  • I was having the same problem as Chloe’s second question. I was trying to apply this formula across 1000 cells and dragging did not work. I just figured it out.

    Here’s my formula:
    =INDIRECT(“‘”&$D$1&”‘!”&M5))

    In my cell M5 text says D5. Then I dragged that formula in cell M5 down to read D5, D6, D7 and so on. Then when you drag the INDIRECT formula down it references M5, M6, M7, and so on.

    For your formula needs to read A3:D6, A4:D7, A5:D8 and so on. Excel will not recognize the pattern A3:D6, A4:D7, A5:D8 if you drag the formula. Simply create another column using N5, and put text saying D6, D7, D8 and so on.

    Adjust your formula to this:
    =INDIRECT(“‘”&$D$1&”‘!”&M5&”:”&N5))

  • Kelly Kennedy

    Thanks so much! I am trying to use the VLOOKUP/Indirect formula to pull information from other files. It works fine when those files are open but I get a “#REF” formula once I close them. Is there anyway to make this work without opening all the files I am pulling from?

  • Jewels

    Lets say i have cell A1 that has 1103-44 in it. I have a spreadsheet named after the last two digits in this cell “44”. is there a way to use vlookup and indirect to find the last two digits in cell A1 and go to the specified spreadsheet to return information using vlookup? I have the same number “1103-44” in cell A1 on the “44” spreadsheet but need to vlookup values in other columns in the same row as “1103-44”. I hjope this makes sense

    • Bill Flippen

      why not use something like =INDIRECT(“vlookup(Sheet”&RIGHT(A1,2)&”!G5:h8,2,0″))

  • Jack

    Work perfectly

  • Stephanie

    Awesome – thank you very much!

  • Royale W. Cheese

    Fantastic! I run lots of biology (genomics) experiments using the same set of 23,246 genes every time. I used your tip in Excel for Mac 2011 v. 14.5.9 to to pass raw data from one sheet into a dynamic table in another sheet, which passes data a standard set of pretty charts. Beats the heck out of copy-pasting (replacing) all of the raw data into columns every time I need to make a chart for an experiment!

  • JeffDB

    I’m trying to calculate the number of Sundays in a given month with indirect. The formula works in Excel but not in Sheets =SUMPRODUCT(N(TEXT(ROW(INDIRECT(“J$85″&”:”&EOMONTH(J$85,0))),”ddd”)=”Sun”))”))))) with J85 equal to the first of the month. Syntax error?

  • Soriba Sylla

    An INDIRECT formula can refer to cells in other workbooks, but the problem is that the result
    will return a #REF! error if that workbook is closed.

  • Mohamed Abul Hasan Arshad

    He Hi,
    I need to refer to an excel tab from another excel workbook. Like creating an alias of a tab or referring to a tab – wherein i can view the referred tab, data will get updated in the referring tab when i update the referred tab.
    Kindly suggest!

  • Michel Denizot

    Very cool! Thank you very much!

  • Dattatreya Krishna

    Thanks a ton; this has made my life 700 times easy (I had 700 sheets of data collection)

  • Dileep

    Hi i would like to get the sum of range in one sheet in another sheet.
    I want Sheet2.A1 = sum(Sheet1!A1:Sheet1!A4)
    but this column name in Sheet 1 is dynamic for me i.e it can be A or D or AB etc. can some one help me how can i get this done

  • Clara Wong

    is the tab can’t contain ” ‘ ” create the ref ? i have REF on my formulas right now. but if i take it out ” ‘ ” it works. how to make the formulas work include ‘ ?

  • Austin Deming

    I want to dynamically access the last cell in my excel database.

  • Stephen Meissner

    Hello – very interesting stuff – I have similar issue, trying to use LOOKUP instead of VLOOKUP to reference data in other worksheets

    Example of Code I am trying:
    =LOOKUP(H10,INDIRECT(“‘”&K10&”‘!”&”$B$2:$B:$5000”),INDIRECT(“‘”&K10&”‘!”&”$D$2:$D$5000”))

    Where H10 contains the value that I wish to lookup in Column B in spreadsheet name located in cell K10 and return value that aligns with matched row in Column D in spreadsheet name located in cell K10.

    This does not appear to be working – resulting in #REF error.

    Any help – greatly appreciated.

    Thanks,

    Steve

  • BivDog

    Worked great for Google Sheets, everything I needed to know in order to get the job done. Thank you very much.

  • samaresh

    Hello,
    I need help for an excel situation. I am doing a call flow analysis of programs, and a lot of programs are called multiple number of times. So to keep it simple I have moved those common programs and their subsequent flows to a separate tab with a link to the cell. Now as I am pointing to this new location from many cells, is there any way I can determine from which cell the control came from and I can create a link(dynamically) to go back to the same cell?
    Thanks!

  • McKenzie Gibson

    I am going round the bend trying to use INDIRECT in Google sheets. I have a stream of data in column D. The data is in banks of 8. I wish to transpose each bank into a row format. That works OK but I want to automate and wish to use INDIRECT so that I can use a formula to transpose each successive bank. I have tried various combinations but here is a minimum trial
    =TRANSPOSE( INDIRECT(“E381”):D417) where E381 contains D409. ie I wish to transpose D409 to D417 into a row. when I get indirect to work I will try to figure out how to “automate” the process. Any help will be much appreciated.

  • Wera Archanon

    Thanks a lot!
    I will see other topic.