Excel’s Indirect function allows the creation of a formula by referring to the contents of a cell, rather than the cell reference itself. Of all the functions covered in our Excel courses, it is often Indirect() that attendees haven’t come across but find an immediate use for, often saving a great deal of time and effort in the process.
If you have several sheets, each with information for a single department for example, you may want to set up a summary sheet. Rather than creating separate formulae to refer to each sheet, Indirect() can allow you to create a single set of formulae all of which use a reference to a sheet name held in a cell – hopefully an example will make this clearer.
To refer to cell A2 on a sheet named ‘Cuddly Toys’ we would use a formula like this:
=’Cuddly toys’!A2
However, sometimes it would be useful to be able to change a whole series of references to, for example, a different sheet.
We could type the sheet name into a cell on our main sheet, say A1. We could then write a formula to refer to cell A2 on the sheet typed into cell A1.
If we simply type:
=A1!A2
Excel, not unreasonably, looks for a sheet named A1 and fails to find it.
However, we can use the Indirect function instead. Here is the screen from the Paste Function dialog for Indirect:
“‘“ & $A$1 & “‘!A2“
Our Ref_text entry is a little confusing, so we have highlighted the pairs of speech marks in different colours. We have two items of text, and sandwiched in between them, an absolute reference to the contents of cell A1 – as you can see this correctly returns the contents of that cell – Cuddly toys. The ampersands are used to join the 3 elements of our Ref_Text together. The first text section simply holds a single apostrophe – this is necessary because, if our sheet name contains a space, it must be surrounded by apostrophes to be correctly identified. The second section contains an absolute reference to cell A1 – the cell where we type the name of our sheet. The third text section contains the closing apostrophe for the sheet name, together with the exclamation mark that separates sheet name from cell reference, and the cell reference itself – A2.
This works well to return the contents of cell A2 on our cuddly toys sheet, and if we were to type in ‘Boardgames’ for example, it would automatically return the contents of cell A2 on a sheet named ‘Boardgames’.
However we do have a problem left to solve. We need to refer to many cells on the price list sheets, but if we copy our Indirect cell, the reference to A2 doesn’t change, because it is just text. We can solve this by using a row/column style reference instead of A2:
“‘“ & $A$1 & “‘!RC“
Note that we have to set the ‘A1’ argument of the function to ‘False’ to use this reference. RC will return the current row and column – so a formula in cell A2 will refer to A2 on cuddly toys, A3 to A3 and so on. If we need to refer to a different cell we would add numbers in square brackets after R and C. So R[1]C[1] would look at the cell one row down and one column right for example.
21 responses so far ↓
Ashish Handa!! // December 28, 2007 at 12:39 pm |
Superb!!
Doug Schaller // April 30, 2008 at 3:26 pm |
Greetings. I have been using INDIRECT for some time and find it very useful. However, I can’t seem to figure out a way to use it to reference a range across several worksheets. I can get the range working okay on one worksheet, but not several. There is no indication that you can’t do this, so I am still trying. Any suggestions? This would allow me to pick up YTD figures from a series of identical mthly worksheets with just one cell change – if I can get it working.
simontkb // April 30, 2008 at 8:59 pm |
Hi Doug
Good question and one that doesn’t seem to have an easy answer. There is a discussion and rather involved possible answer here:
http://www.eggheadcafe.com/software/aspnet/31803284/how-do-i-set-up-a-3d-refe.aspx
Amran // May 10, 2008 at 11:34 am |
Hey there,
Can you help me with my query, i have several worksheets in one workbook containing different expenses, what i am looking for is to make a summary page in which the expense total of each entity from different work sheet comes up automatically every times its get changed.
Rgds,
Amm.
simontkb // May 12, 2008 at 4:01 pm |
Hi Amran
If I understand correctly, it depends on whether you can include a cell on each worksheet that calculates the sum of the expenses you want. If so, you could give each of the cells in the individual worksheets a different name (in case their position changes) then refer from the summary worksheet with something like: =Asheet!Asum, =Bsheet!Bsum etc. If the individual summaries cells are static you could just link to the cell reference e.g. =Asheet!C1 or whatever.
James // May 29, 2008 at 7:19 pm |
Can you tell me how to propery delimited this formula?
=SUM(IF(indirect(C1)!$E$2:$E$500=”N”,1,0))
No matter what combination of quotes I try I keep getting an error…
C1 = May08 (sheet name)
Thanks!
James
simontkb // May 30, 2008 at 2:25 pm |
Hi James
There are a few problems with what you have at the moment, in particular the E2:E500 bit. If what you are trying to do is to count the cells in the range that contain the letter N (lower or upper), try something along the lines of (do check carefully – no guarantees!):
=COUNTIF(INDIRECT(C1 & “!$E$2:$E$500″),”N”)
Let me know if this is what you want.
Kind regards
Simon
Let
Brian Tkatch // June 3, 2008 at 1:16 pm |
Fantastic!
I just found the indirect function, but wanted to know how to update the references, and yours helped.
My case is i have two lists of ordered numbers but not all numbers are in both lists. For a quick comparison, i use (in column C) =A1=B1 (and conditional formatting to turn FALSE into read, and !FALSE to green), and drag the field to populate all the fields in column C.
The problem is that when i find a gap, i either add empty fields, or cut & paste the fields to match the other column (leaving the gap there). The purpose, is so all populated fields match.
The problem was that cut&paste or adding that field, updated the reference. Hence indirect, but then indirect could not be easily automagically copied.
Using this post helped. I could not quite figure our how to use your final example, but i did finally get it with =INDIRECT(“R” & ROW() & “C1″, FALSE) = INDIRECT(“R” & ROW() & “C2″, FALSE).
Thanx for the post.
simontkb // June 3, 2008 at 1:24 pm |
Hi Brian – thanks very much for your comment – glad the post helped, and well done on working out how to adapt it to solve your problem.
Regards
Simon
Scott Harris // March 9, 2009 at 8:20 pm |
I’m successfully able to create data using indirect statements, to result in for example “John Smith”. However, I’m trying to use this as the reference for a vlookup on other data. It’s not working. What I’m finding is that I also cannot perform a simple “find” on the generated data using Ctrl-F and entering “John”, which is probably a precursor to using this as a reference in a VLookup statement. Any ideas how to make the resultant Indirect information recognizable?
Thank you.
simontkb // March 10, 2009 at 9:50 am |
Hi Scott
The find should work if you set the find options to search values rather than formulas, and indirect() should work OK as the lookup value in a Vlookup function. I’ve tried it on a simple example without a problem – could you give me a bit more information about the Indirect and Vlookup formulae you are using?
Regards
Simon
Scott Harris // March 10, 2009 at 3:29 pm |
It turns out that my spreadsheet was large and it was taking several minutes to calculate. I wasn’t waiting those several minutes. Everything is working now.
Thanks, Scott
Guy // May 14, 2009 at 6:10 pm |
Thanks for the help with the Indirect function. Here is my problem.
When I use =INDIRECT(A1) in SheetA where A1 points to cell D1, I get the value of D1.
However, I need to reference cell A1, as above, from SheetB. The formula I am using is =INDIRECT(Sheet1!A1)
This returns the value 0 and not the value of D1 in SheetA
Any help would be appreciated.
Guy
simontkb // May 14, 2009 at 6:25 pm |
Thanks Guy – did you mean:
=INDIRECT(“SheetA!A1″) ?
Presumably there’s some good reason why you’re using INDIRECT() rather than just the formula =SheetA!A1?
Regards
Simon
Guy // May 14, 2009 at 7:47 pm |
I’m adding in one row each day to SheetA, at row 1 so that the info in D1 today, becomes D2 tomorrow. However, I don’t want the reference to refer to D2, tomorrow, but to D1.
Regards,
Guy
simontkb // May 15, 2009 at 8:06 am |
Thanks Guy – I understand now – did you get it to work with the SheetA reference in the end?
Regards
Simon
Guy // May 15, 2009 at 3:58 pm |
Yes, I did get it to work. Here’s the principle.
I have two worksheets, Sheet1 and Sheet2. Column D, (D1,D2,D3, etc.), in Sheet1 needs to call the data from Column A, (A1, A2, A3, etc.), in Sheet2.
However, since I am adding rows above row 1 in Sheet2 on a daily basis, I still want D1 in Sheet1 to call from A1 in Sheet2. The problem is that if I want to add a row to Sheet2, all of my references increment by 1 in Sheet1 so that D1 in Sheet1 points to A2 in Sheet2 and not to A1.
Solution:
I added the following to column A in Sheet1:
Cell A1: Sheet2!A1
CellA2: Sheet2!A2
CellA3: Sheet2!A3
Etc.
Note: There is no “=” sign before the entry.
Now I pointed D1 on Sheet1 to A1 on sheet1 using the indirect function shown below. Since A1 is text and not a formula, no “=” sign, it does not increment when I add a row to Sheet2, which means that it will always point to the same absolute reference.
Therefore; Cell D1 in Sheet1, calls the info from Cell A1 in Sheet1, which in turn calls the information from Cell A1 in Sheet2.
The formula for D1 on Sheet1 looks like this:
=INDIRECT(A1)
as opposed to
=INDIRECT(Sheet2!A1) which returns a #REF.
Hope that helps.
Guy
simontkb // May 15, 2009 at 4:11 pm |
Hi Guy
I’m not sure whether you just didn’t put the quotes in the comments, or they didn’t come through on the blog, but I think =INDIRECT(Sheet2!A1) should be =INDIRECT(“Sheet2!A1”)
I think that should avoid the need to enter the text in column A. I hope I’ve understood correctly this time.
Regards
Simon
Guy // May 15, 2009 at 6:09 pm |
Thanks. That’s even better.
Simple, when you know how.
Guy
Anupam // August 6, 2009 at 9:36 am |
I have been using the Indirect function for quite sometime. But I have always used it to refer to values on some other worksheet of same workbook as you have shown in your example.
can we use it to refer to value of cell in another work book save some where without opening it (like we use links) this will help us to change the path of the file in one cell and hence the values. I am trying for the same but its not working and always gives me #REF ! error. Any help available on this issue.
simontkb // August 6, 2009 at 5:28 pm |
Hi Anupam – I’m pretty sure that you will always get #REF when using INDIRECT() with a closed workbook. The Contextures site has a detailed description of the use of INDIRECT() including reference to its inability to cope with closed workbooks:
http://www.contextures.com/xlFunctions05.html