The INDIRECT worksheet function has two arguments; ref_text and a1. Ref_text is some representation of a cell address. It can be a string like “B10”, a range reference like C10 where cell C10 contains the string “B10” or even the result of a formula like “B” & (5*2).
The a1 argument can be True or False. True indicates that the ref_text you supply is in the A1 style. True is the default of this optional argument and can be omitted. False indicates that ref_text is in the R1C1 style.
Obviously passing a string like “B10” to INDIRECT isn’t very efficient. You can just refer to it directly like =B10. However, if you have to build your cell address from other cells, INDIRECT can be very useful. Here’s some examples of INDIRECT in action.
You can also use INDIRECT to reference cells on another sheet or in another workbook. It won’t work on closed workbooks, so beware of that. The syntax can be tricky, so I usually create a real reference without indirect to see what it should look like, then build my ref_text using the example.
In the examples above, I used a sheet name and workbook name that had a space in it. This is to illustrate that you need apostrophes around the worksheet and workbook names in some circumstances. It’s a good practice to include them even if you don’t need them. The whole point of INDIRECT is to build ref_text from cells that may change. If you start with a ref_text that doesn’t need apostrophes and a cell changes such that you do need apostrophes, you’ll get a #REF! error. Better safe than sorry.
Hi,
I am using the following inputs for a combined VLOOKUP and INDIRECT function. I think I have put everything necessary but I still get the #REF! error.
VLOOKUP(A3,INDIRECT(“‘”&”I:Finance2004IT DEPARTMENTIT PROJECTS TRACKING1 – Individual files”&B78&”.”&B79&”.”&B80&”[“&C1&” “&B78&”.”&B79&”.”&B80&”.xls]Template”&”‘”&”!$A$1:$ZZ$200?,TRUE),5,FALSE)
It will be great if you could tell me where I may be going wrong so that I can use this combination to make my spreadsheet easier to use and modify.
Sincerely,
Neil
INDIRECT functin can be used on many occasions. for e.g. you have a range name from A1 to A200 even using dynamic reference like offset. But when you delete one of the rows there is problem.
you can write a detailed article giving all the possible examples of using INDIRECT and publish in this blog.
Hey Neil
Try Nesting the indirect statement in another indirect
i.e.
indirect(indirect(“Reference”))
This seems to get rid of the error. Don’t know why.
Dick-
Thank you so much for this help! First time on this site and WILL NOT BE MY LAST. I have been wanting to do this for years. I knew that it should work, but didn’t know the steps. Now I just have to remember where I wanted to use this…
This is one more Excel mystery off my list. Thanks again, and keep the knowledge coming.
Thanks,
Andy
is there a way to replicate the indirect function in vba code?
anki –
In VBA, it’s all working with strings, the way INDIRECT does. Just manipulate the strings in the expression:
Workbooks(String1).Worksheets(String2).Range(String3)
is like INDIRECT(“[Book1.xls]”&”Sheet1!”&”$A$1?)
I’m pulling (what’s left of) my hair out. I have an indirect function as follows:
=INDIRECT(“‘u:production statistics[Shell Prod Adj Factor Calcs.xls]Factor Calculations’!” & ADDRESS(138,2))
If the other workbook is open, the reference is successful and I get the resulting value. If the other workbook is not open, I get the #REF! error result. I’ve tried the nested indirect functions, but that doesn’t seem to solve my issue. Any guesses?
Tim – Indirect() just doesn’t work with a closed workbook.
There are some alternatives here: http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/
Indirect is a great function. I have used it myself many times. The one thing that is not mentioned here is that it is a Volatile function. It will be recalculated every time a calculation executes (similar to the Now() function). So if you want to use a handful of these functions then more power to you, but if you have hundreds or thousands of these functions embeded in formulas that are slow to calculate (like VLookup or such) then performance may become an issue for you.
Here is another trick to avoid popups and #REF with INDIRECT if the other workbook is closed.
It seems that ISNUMBER is wrapping the error INDIRECT produces and so you can make an IF work on this.
=IF(ISNUMBER(INDIRECT(“‘[otherworkbook.xls]sheet1’!$A$1?));INDIRECT(“‘[otherwoorkbook.xls]sheet1’!$B$”&C2);”please open otherworkbook.xls to make it work”)
Of course it won’t update until you open the needed workbook.
Hi, i have a workbook with an expanding number of worksheets. i have a macro to copy a template sheet to the book and to re-name the sheet from Templ(2) to the project number (say) 2510. To set this new sheet up i need to navigate (under macro control) between the sheets in the book. My base macro needing to cater for all new sheets assigns the Project ID 2510 to a variable “tabName”. Some of the sheets are static like “summary” and can be reference by – Sheets(“summary”).Select however when in the same macro i try to get back to my new sheet with a similar Sheets(“tabName”).Select i get an out or range error returned.
i believe excel is not recognising the assignment as it will switch with (say) a Sheets(“Sheet9?).Select, but not using the variable name. investigated perhaps the use of INDIRECT but can’t find the syntax. Help please.
best regards,
Bob mcglynn
Bob: Remove the quotes around your variable name
INDIRECT is only necessary when the worksheet portion of the constructed reference could vary. Otherwise, for variable references into a static worksheet, INDIRECT(“R”&i&”C”&j,0) is always equivalent to INDEX($1:$65536,i,j) and INDIRECT(“‘whatever’!R”&i&”C”&j,0) is always equivalent to INDEX(‘whatever’!$1:$65536,i,j).
INDIRECT can be useful when dereferencing names defined as static (NOT dynamic) ranges, e.g., YieldCurve_current referring to =XYZ!$A$1:$B$20 and YieldCurve_prior referring to =XYZ!$D$1:$E$20, and cell X99 contains a drop-down list containing Current and Prior. That cell’s value could be used in formulas like
=VLOOKUP(MonthEntry,INDIRECT(“YieldCurve_”&X99),2)
But the equivalent functionality could be provided by nonvolatile functions using a few more defined names, e.g., with some range named YieldCurveList containing Current and Prior and the drop-down list for cell X99 tied to that range, and the name YieldCurve_selected defined as
=CHOOSE(MATCH($X$99,YieldCurveList),YieldCurve_current,YieldCurve_prior)
the VLOOKUP formula could be rewritten as
=VLOOKUP(MonthEntry,YieldCurve_selected,2)
The same sort of approach could be used to refer to ranges in different worksheets. As long as there are only a few such ranges (CHOOSE itself is limited to 29, but length of name definitions would likely kick in before that), CHOOSE(MATCH(.),…) can replace the volatile INDIRECT.
Dick Kusleika:- many thanks for the response, have tried just about every combination with without etc etc. have just tried without to double check and i get a runtime error “9? – “subscript out of range”
which i believe is an array error – but i cant see how to correct?
any suggestions greatfully recieved
regards
B
Bob: Subscript out of range in this case means there is no sheet with that name. I believe your problem is that you’re trying to find a sheet with the number 2510 and you need to be looking for the string 2510. This works
Dim tabName As String
tabName = “2510”
ThisWorkbook.Sheets(tabName).Select
End Sub
If tabName is a Long rather than a String, it will look for the sheet whose Index is 2510. If it’s a String, it will look for the sheet whose Name is “2510?. If 2510 is a number in your code, you can do:
Just make sure you’re passing a string to the Sheets property.
Dick:
Once again many thanks for the response and you are correct that works fine, and u have nailed my error correctly. i have tried a number of things around this and not found the solution. you provide that – Thanks.
i do however have a further problem, my macro is universal for all new sheets added, so i don’t know what the sheet id will be ahead of the game. i am using a variable “projNumber” picked off a cell within the new sheet (in this case 2510) to change the tab name. this works fine. i did think of your above point and have formatted all instances of the Proj Number as “Text”, hoping that when i picked the “number” up it would pick up with it a consistant Text Format as well.
However if i change your snippit to use my variable rather that the literal 2510, it fails with the same subscript error when i try to navigate back to the sheet from elsewhere in the workbook.
i believe you have identified the solution to my error, but my limited vba does not permit me to find the correct syntax when using Variable assignments.
Regards
Bob
Is there a way to structure an INDIRECT function that references a range of worksheets? E.g., I have a Workbook that has a summary sheet of the worksheets contained. The trick here is that I will ADD to the number of worksheets and I would like to have the summary sheet include the new worksheets.
For example I have a workbook with the following worksheets: Summary, Data1, Data2
In cell A1 of each of the “Data#” sheets is a number that will be summed in the summary sheet.
In the summary sheet I’ve contructed the following SUM function =SUM(“Data1:Data2!A1?) with successful results. However if I add a new Data worksheet, I must modify the function to include new sheet. One way to address this is to insert the new worksheet between the Data1 and Data2 worksheets. I will still have successful results in this case.
However, I would like the add the new worksheets after the current last worksheet, and just maintain a cell with the number of the last worksheet.
For example; before adding a new sheet to the workbook, cell A1 in the summary sheet would contain the value 2, to represent the current last worksheet “Data2?
I’ve constructed the following function =SUM(INDIRECT(“Data1:Data” & A1 &”!A1)) This results in a #REF error for which I cannot solve.
Is it possible to do what I wish to do, or is there an inherent error in the INDIRECT function handling of ranges of sheets?
Larry – INDIRECT can only return range objects, and 3D references aren’t range objects, so no way to generate dynamic 3D references in Excel.
You could use the classic approach of adding blank worksheets as bounds for multiple worksheet blocks. For example, insert a worksheet named Data_Begin just before Data1 and another worksheet named Data_End just after Data2. Change your formula to
=SUM(Data_Begin:Data_End!A1)
or with cell A1 the active cell in ANY worksheet define the name Data referring to
=Data_Begin:Data_End!A1
note that the cell address is RELATIVE and change the cell formula to
=SUM(Data)
You could hide Data_Begin and Data_End, and as long as the active worksheet is between Data_Begin and Data_End when you insert new worksheets, those new worksheets will also be between Data_Begin and Data_End.
fzz:
I have a workbook with a lot of INDIRECT functions in it, and I’m specifically looking for advice on how to replace them with CHOOSE() functions to speed performance.
Here’s a sample of one of the formulas:
=INDEX(INDIRECT(ShipChooser1Up),1,7)
Where ShipChooser1Up grabs the name of a named range for the INDEX function to pull a specific reference from.
I’m trying to get the same functionality with this code snippet:
=INDEX(CHOOSE($DJ$5,$DJ$8,$DJ$9),1,7)
Where DJ8,DJ9,etc, just have the names of the named ranges I want to switch between.
However, when I do this, I get #REF errors.
What am I missing here? (If I can figure out how to do this, I can make about 9,000 INDIRECT() calls go away.)
Ken:
If DJ8 and DJ9 “contain” named ranges, you would need to use INDIRECT() on them. How else would EXCEL know to use them (indirectly) as range names instead of (directly) as the “ranges” they are? Since the “ranges” are only a single cell, your “7? parameter causes the #REF! error — it’s trying to refer to a cell outside of a single-cell range.
Try one of these instead:
=INDEX(INDIRECT(CHOOSE($DJ$5,$DJ$8,$DJ$9)),1,7)
=INDEX(CHOOSE($DJ$5,Range1,Range2),1,7)
Thank you – I’ll try using the second formulation.
My problem is that the pages I’m working with use INDEX(INDIRECT()) to pull data from different workbook tabs; it’s basically a report generator. All well and good, until it’s doing calls on over 4000 cells with INDIRECT statements, and some of them with error catching IF statements that require an evaluation of the INDIRECT content before deciding if it’s going to print blank or a value.
Second formulation works.
Hi,
I have a summary sheet followed by a number of data sheets as well. Each of the data pages has a cost code column that a number is input into.
The summary page has room for a maximum of 30 data sheets, but we have never gotten close to it.
There is a range N10:N39 on the summary page which contains the data sheet tab names.
I use a counta(N10:N39) TO give me the number of actual datasheets in the workbook.
On the breakdown sheet I have the formula F1 below, which sums all the values on all the data sheets if they correspond to the number assigned to the breakdown item. I am trying to neplace the 16 in the $N$16 references with a value generated by a counta function (which will give me the amount of data sheets) “(COUNTA(Sum!$B$10:$B$39)+9)” — there are 9 header rows on the summary page, plus 7 referenced data sheets = 16!
=SUMPRODUCT(SUMIF(INDIRECT(“‘”&Sum!$N$10:$N$16&”‘!$A$10:$A$44?),TBD!A2,INDIRECT(“‘”&Sum!$N$10:$N$16&”‘!$O$10:$O$44?)))
I would have thought just replacing the $N$16 with INDIRECT(“$N$”&(COUNTA(Sum!$B$10:$B$30)+9)
which gives
=SUMPRODUCT(SUMIF(INDIRECT(“‘”&Sum!$N$10:INDIRECT(“$N$”&(COUNTA(Sum!$B$10:$B$30)+9)&”‘!$A$10:$A$44?),TBD!A2,INDIRECT(“‘”&Sum!$N$10:INDIRECT(“$N$”&(COUNTA(Sum!$B$10:$B$30)+9)&”‘!$O$10:$O$44?)))
would work, but I get “the formula contains an error” and it highlights the first occurance of “$N$”
I am struggling to see the problem. I would love if someone could point it out. It’s more than likely syntax.
If you can come up with a better (smaller) function, by all means post and I will try it out!
Cheers!
I am trying to use an INDIRECT function to define a data series in an XY plot, but the series formula won’t accept the INDIRECT function (I assume it has something to do with it being a volitile function). I have tried using the indirect function to name a range and enter the range as the series, but it still won’t accept even the named range. The named range definitely selects the correct data, but I guess the series formula still sees it as a volitile entry.
In my spreadsheet I need to allow the user to manually enter a start and end line for the plot, and it needs to be flexible so that the start and end line can be changed to obtain a better curve fit for the data. Typical strategies for defining dynamic ranges don’t work here because I need to be able to define the end of the data series manually.
Any help would be appreciated.
PJ –
Charts often crap out when INDIRECT is used in a name definition.
If you can use OFFSET, instead of
=OFFSET($A$1,1,0,COUNT($A:$A)-1,1)
have the user put the start row in D1 and finish row in D2 and use this:
=Offset($A$1,$D$1,0,$D$2+1-$D$1,1)
I’ve got a doozy that is killing me!!
In Sheet 1, I have >200 rows with a start time (ex, A1:A200) and an end time (ex, B1:B200). On Sheet 2, I have a column (call it column “C”) with multiple (>10,000) times (ex, 1/1/2010 10:00 AM). I want to flag each minute of data IF that minute is within any one of the >200 start/end times.
Below is a way to do it for one set of start/end times:
IF(AND(C1>’Sheet 1′!$A$1,C1’Sheet 1′!$A$1,C1’Sheet 1′!$A$2,C1’Sheet 1′!$A$3,C1200 start/end times, not to mention a pain-in-the-rear to write!
I tried creating an INDIRECT formula so I could drag the formula to the right and make the next column reference the next row down rather than the next colmumn over. I’ll end up being limited by the number of columns to the right, not to mention the fact that I can’t get the forumla to work and even if I did, the file will be huge and take forever to update on my pc.
Any ideas on how to have one formula that looks at all >200 start/end times and return whether or not the time is within one of the >start/end times? I would be eternally greatful. Thanks in advance!
It appears that not all of my problem statement was copied. Below is the complete problem. Sorry for the confusion.
I’ve got a doozy that is killing me!!
In Sheet 1, I have >200 rows with a start time (ex, A1:A200) and an end time (ex, B1:B200). On Sheet 2, I have a column (call it column “C”) with multiple (>10,000) times (ex, 1/1/2010 10:00 AM). I want to flag each minute of data IF that minute is within any one of the >200 start/end times.
Below is a way to do it for one set of start/end times:
IF(AND(C1>’Sheet 1′!$A$1,C1’Sheet 1′!$A$1,C1’Sheet 1′!$A$2,C1’Sheet 1′!$A$3,C1200 statements!!
I tried creating an INDIRECT formula so I could drag the formula to the right and make the next column reference the next row down rather than the next colmumn over. I’ll end up being limited by the number of columns to the right, not to mention the fact that I can’t get the forumla to work and even if I did, the file will be huge and take forever to update on my pc.
Any ideas on how to have one formula that looks at all >200 start/end times and return whether or not the time is within one of the >start/end times? I would be eternally greatful. Thanks in advance!
Dan –
I’m not sure I understand the problem right, but I think this is an approach:
=SUMPRODUCT(($C1>Sheet1!$A$1:$A$200),($C1<Sheet1!$B$1:$B$200))
Change as appropriate if equalling a start or stop time is OK. Fill down.
A number greater than zero says there is one, or many.
Doesn’t tell which one though.
…mrt
Can you tell me how to take a list like this and make it into an excel spread sheet that could then be used to make labels?
Charles E. Heath CPA, P.A.
32-C SE Osceola, Stuart, FL 34994
Phone: (772)286-5669
TMI Accounting
570 SE Port St. Lucie Boulevard, Port St. Lucie, FL 34984
Phone: (772) 971-5852
TMI & Associates
529 NW Prima Vista Boulevard, Suite 301 B, Port St. Lucie, FL 34983
Phone: 866-675-9995
Diamond Accounting & Consulting
1180 SW Ithaca St, Port St Lucie, FL 34983
Phone: (772)342-6770
Schuemann & Company, LLC
107 Mulligan Place, Jupiter, FL 33458
Phone: (561)625-4136
Brian Pybus
601 Heritage Drive #105, Jupiter, FL 33458
Phone: (561)282-1870
Hello,
I need to fill column B of one sheet that references cell f37 in the consecutive worksheets 1-100. I think I need to use the indirect function as autofill was not working, I’m just not sure how?
Lisa, you don’t NEED an INDIRECT, all you need is to code this into Column B
| B |
=Sheet1!F$37
=Sheet2!F$37
… etc …
However, the problem with the above example is that the sheet reference won’t automatically be updated, and you would have to edit the sheet name in each B cell … BUT, you can code the series 1-100 in a “hidden column” referencing the sheet number and reference it with an indirect
Say column Z contains the series 1-100
Then use
| B | …. … | Z |
=INDIRECT(“Sheet” & Z1 & “!F$37?) 1
=INDIRECT(“Sheet” & Z2 & “!F$37?) 2
… etc …
Note: in the above example of indirect, you only need to code the first function, then copy and paste it into the following cells because the Z1 reference will automatically be incremented to refer to the next row.
Larry
Hello,
I have a range of numbers in a row (for example: A3 – A13). In cells A14, A15, and A16, we list the Average, Max, and Min values using the Excel formulas (e.g. A14=Average (A3:A13)). When we get a new data set we insert the data as a new column A3 and shift everything to the right. Unfortunately the formulas also move to A4:A14. Is there a way to always start with A3 and let the right side shift as columns are added?
This was really helpful. I was using a 25+ worksheet excel workbook and needed to gather and pull the data into one table on one worksheet from all the different worksheets. Using this post, I created two nested INDIRECT functions to look up the worksheet name and the position of the data on each worksheet. It saved a great deal of time as I simply needed to pull the cell positions for the data that I needed and paste once into one row instead of into a formula for each column. Thank you for your post.
I first created a new worksheet (I named it “aggregated from all”) and in a column (COLUMN A) listed the names of all of the worksheets (had to do that manually – may be a way to pull that quickly but I only found VBA solutions – beyond my level). So it is a column of values with the name of each worksheet that I wanted to pull the data from on individual rows (e.g., SHEET1, SHEET2, etc. in column A)
On that same worksheet I filled row 1 (ROW 1) with the cell references / positions for the data that I need (e.g., Cell B1 contained: B3004, B3 contained: R3002, etc.) and row 2 (ROW 2) with the description of the data. I then had basically the headers and rows of a report shell, just needed the data to be filled in. (I should note that the cell positions were the same on every worksheet – I was simply looking for a way to systematize compiling the data from the sheets).
For my row 10, column B, the following pulled the worksheet name from column A in row 10 and position of the value that I wanted:
=INDIRECT(“‘”&$A10&”‘!”&B$1)
The formula parts broken down:
=INDIRECT(“‘”&$A10&”‘!”
This is the INDIRECT function looking in cell A10 for the worksheet name; in my example, this referenced SHEET25. The $ locked the column so I could paste across all columns and lookup the sheet name.
&B$1)
This is the INDIRECT linkage to cell B1 on this worksheet, that contained the value R3002 . The $ loocked the row to row 1 so I could past down into all rows.
In my worksheet SHEET25, the cell R3002 contained the value $1,117,229. I then simply copied and pasted into all parts of the report.
Hi AnotherSG,
You mentioned that the cell positions were the same on every worksheet. Are all of these worksheets one after another? If so you could use a 3D reference such as =SUM(Sheet1:Sheet25!C1), which would sum the cell C1 on every sheet between and including Sheet1 and Sheet25.
Kyle
I am trying to calculate an average value using a Vlookup and the INDIRECT function and am having some problems.
Basically, I want to enter a date in one cell in a worksheet (call it cell A1 in Sheet1), then use a VLOOKUP to find this date from a date column in another sheet (call it Column A in Sheet2). Once it finds this date, I want to go back 90 days and take the average of a value in a column – in this instance a rate in column B, and return this 90 day average to a cell in Sheet1. Any thoughts?
Brad: Assume your dates are in Sheet2!A5:A150 and the numbers are in column B. This array-entered formula should get you what you want.
Dick K-
Thank you so much for the help. It is exactly what I needed and works like a charm.
Brad
=INDIRECT(“H”&SMALL(INDEX(((‘Above Ground’!B6:B800=’Pipe Stringing_Lowering’!C8)*ROW(‘Above Ground’!B6:B800))+((‘Above Ground’!B6:B800’Pipe Stringing_Lowering’!C8)*10^8),0,0),2))
Can someone tell me how I can get this to work on a different sheet then what all the date is on? As you can see I am pulling the data from Above ground to Pipe Stringing but I cant get a way to make this work. Works fine on the above ground sheet but if I cant find a way to get it to work on pipe stringing.
Please Help.
Thank you!
Never mind – was just losing my mind. Got it.