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.
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:
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.
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!
This function works correctly in the previous version of Google Sheets, but for some reason it does not work with New Google Sheets.
You have to use “;” instead of “,”
Thanks for your help benj!
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?
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.
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))
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!
This works GREAT!!!!!!!!!!!!!!!!! thanks very much :)
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!
Awesome again Stefan!
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!
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.
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.
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.
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.
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.
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)
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))
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?
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
why not use something like =INDIRECT(“vlookup(Sheet”&RIGHT(A1,2)&”!G5:h8,2,0″))
Work perfectly
Awesome – thank you very much!
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!
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?
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.
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!
Very cool! Thank you very much!
Thanks a ton; this has made my life 700 times easy (I had 700 sheets of data collection)
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
I think you’d be better off using a combination of INDEX and MATCH functions.
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 ‘ ?
I want to dynamically access the last cell in my excel database.
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
Worked great for Google Sheets, everything I needed to know in order to get the job done. Thank you very much.
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!
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.
Thanks a lot!
I will see other topic.
On Google Sheets, how do you do conditional formatting BETWEEN sheets? For example, on Sheet1 Column A, I have a master list of guest names that were sent a party invitation. On Sheet2 Column A, there is a list of guest names of THOSE WHO RESPONDED (this column gets filled automatically from Google Forms). If a name in Sheet2 Column A MATCHES the name in Sheet1 Column A, I want that name to be highlighted in Sheet1 Column A.
For example:
John Smith is on our guest list (Sheet1) and he has submitted a response (Sheet2), so I want his name to be highlighted
Jane Smith is on our guest list (Sheet1) but she has NOT submitted a response (Sheet2), so her name should remain unhighlighted.
Please let me know if there is a workaround on Google Sheets. Thank you!!
I know this is an old question but here’s how I would approach that. Someone may have a better way but this should work. On Sheet1 (Invitation list), create a column in front of the names which will serve as an indicator of whether or not the name appears on sheet2 (RSVP list). In that column your formula will look like =IF(ISERROR(MATCH(cell with name, list of names on Sheet2, 0)), “”, X). This will place an X in the box for anyone whose name appears on Sheet2. Then you simply use a conditional format to highlight the names with an X next to them.
I’m sure you can put a similar formula directly into the conditional formatting but it takes so much playing around with quotes and “=” signs that I find it’s just easier to add the check column.
Hope this helps.
This is an old question but actually still very relevant (official wedding invites have not been sent out yet)! I will definitely try your suggestion and will let you know if it works. Thanks for taking the time to reply :)
Has anyone figured out a way around the INDIRECT() function returning #REF if the other file isn’t open?
I have a Master file that contains a set of standard variables that I want to use across a large number of files so that if I want to change the variables in all I only have to change the variables in one (the master file) and it will propagate through.
At the moment I am using =IF(‘[Master Sheet 2018.xlsx]Data’!A17=””,””,'[Master Sheet 2018.xlsx]Data’!A17), however, I would like to change it so that it calculates the filename using the financial year for the file which I have already got a function for.
I have tried this =INDIRECT(ADDRESS(1,1,1,1,”[Master Sheet “&’Variables (Hidden)’!$B$2&”.xlsx]Data”)) which works as long as the Master Sheet file is open but otherwise returns #REF
If not, does anyone know how to stop the existing value being replaced if the formula results in an error?
very helpful, I wasted hours to find this method. thanks.
I keep getting the error when typing ” ‘ ” & … Is there space in between these and what should be the order? Beside if I fixed half of the cell like C$2- copying horizontal doesn’t work. Could you please help? Many thanks!
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.
Hello! I’m wondering how I can combine these two functions…
1. simple SUM from another tab
=(SUM(indirect(“‘my_sheet’!G1:G50”)))
2. grabbing the sheet name from a column to the left (to use instead of hard-coding “my_sheet”)
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))
This does not work :(
=(SUM(INDIRECT((ADDRESS(ROW(),COLUMN()-1))!)G1:G50))
Thanks in advance!