This Excel INDIRECT tutorial explains the function's syntax, basic uses and provides a number of formula examples that demonstrate how to use INDIRECT in Excel.
A great lot of functions exist in Microsoft Excel, some being easytounderstand, other requiring a long learning curve, and the former being used more often than the latter. And yet, Excel INDIRECT is one of the kind. This Excel function does not perform any calculations, nor does it evaluate any conditions or logical tests.
Well then, what is the INDIRECT function in Excel and what do I use it for? This is a very good question and hopefully you will get a comprehensive answer in a few minutes when you've finished reading this tutorial.
Excel INDIRECT function  syntax and basic uses
As its name suggests, Excel INDIRECT is used to indirectly reference cells, ranges, other sheets or workbooks. In other words, the INDIRECT function lets you create a dynamic cell or range reference instead of hardcoding them. As a result, you can change a reference within a formula without changing the formula itself. Moreover, these indirect references won't change when some new rows or columns are inserted in the worksheet or when you delete any existing ones.
All this may be easier to understand from an example. However, to be able to write a formula, even the simplest one, you need to know the function's arguments, right? So, let's have a quick look at Excel INDIRECT syntax first.
INDIRECT function syntax
The INDIRECT function in Excel returns a cell reference from a text string. It has two arguments, the first is required and the second is optional:
ref_text  is a cell reference, or a reference to a cell in the form of a text string, or a named range.
a1  is a logical value that specifies what type of reference is contained in the ref_text argument:
 If TRUE or omitted, ref_text is interpreted as an A1style cell reference.
 If FALSE, ref_text is treated as a R1C1 reference.
While the R1C1 reference type might be useful in certain situations, you'll probably want to use the familiar A1 references most of the time. Anyway, nearly all INDIRECT formulas in this tutorial will use A1 references, so we will be omitting the second argument.
Basic use of the INDIRECT function
To get into the function's insight, let's write a simple formula that demonstrates how you use INDIRECT in Excel.
Suppose, you have number 3 in cell A1, and text A1 in cell C1. Now, put the formula =INDIRECT(C1)
in any other cell and see what happens:
 The INDIRECT function refers to the value in cell C1, which is A1.
 The function is routed to cell A1 where it picks the value to return, which is number 3.
So, what the INDIRECT function actually does in this example is converting a text string into a cell reference.
If you think this still has very little practical sense, please bear with me and I will show you some more formulas that reveal the real power of the Excel INDIRECT function.
How to use INDIRECT in Excel  formula examples
As demonstrated in the above example, you can use the Excel INDIRECT function to put the address of one cell into another as a usual text string, and get the value of the 1^{st} cell by referencing the 2^{nd}. However, that trivial example is no more than a hint at the INDIRECT capabilities.
When working with real data, the INDIRECT function can turn any text string into a reference including very complex strings that you build using the values of other cells and results returned by other Excel formulas. But let's not put the cart before the horse, and run through several Excel Indirect formulas, one at a time.
Creating indirect references from cell values
As you remember, the Excel INDIRECT function allows for A1 and R1C1 reference styles. Usually, you cannot use both styles in a single sheet at a time, you can only switch between the two reference types via File > Options > Formulas > R1C1 check box. This is the reason why Excel users rarely consider using R1C1 as an alternative referencing approach.
In an INDIRECT formula, you can use either reference type on the same sheet if you want to. Before we move any further, you may want to know the difference between A1 and R1C1 reference styles.
A1 style is the usual reference type in Excel that refers to a column followed by a row number. For example, B2 refers to the cell at the intersection of column B and row 2.
R1C1 style is the opposite reference type  rows followed by columns, which does take some time getting used to : ) For example, R4C1 refers to cell A4 which is in row 4, column 1 in a sheet. If no number comes after the letter, then you are referring to the same row or column.
And now, let's see how the INDIRECT function handles A1 and R1C1 references:
As you see in the screenshot above, three different Indirect formulas return the same result. Have you already figured out why? I bet you have : )
 Formula in cell D1:
=INDIRECT(C1)
This is the easiest one. The formula refers to cell C1, fetches its value  text string A2, converts it into a cell reference, heads over to cell A2 and returns its value, which is 222.
 Formula in cell D3:
=INDIRECT(C3,FALSE)
FALSE in the 2^{nd} argument indicates that the referred value (C3) should be treated like a R1C1 cell reference, i.e. a row number followed by a column number. Therefore, our INDIRECT formula interprets the value in cell C3 (R2C1) as a reference to the cell at the conjunction of row 2 and column 1, which is cell A2.
Creating indirect references from cell values and text
Similarly to how we created references from cell values, you can combine a text string and a cell reference within your INDIRECT formula, tied together with the concatenation operator (&).
In the following example, the formula: =INDIRECT("B"&C2) returns a value from cell B2 based on the following logical chain:
The INDIRECT function concatenates the elements in the ref_text argument  text B and the value in cell C2 > the value in cell C2 is number 2, which makes a reference to cell B2 > the formula goes to cell B2 and returns its value, which is number 10.
Using the INDIRECT function with named ranges
Apart from making references from cell and text values, you can get the Excel INDIRECT function to refer to named ranges.
Suppose, you have the following named ranges in your sheet:
 Apples  B2:B6
 Bananas  C2:C6
 Lemons  D2:D6
To create an Excel dynamic reference to any of the above named ranges, just enter its name in some cell, say G1, and refer to that cell from an Indirect formula =INDIRECT(G1)
.
And now, you can take a step further and imbed this INDIRECT formula into other Excel functions to calculate the sum and average of the values in a given named range, or find the maximum / minimum value within the rage:
=SUM(INDIRECT(G1))
=AVERAGE(INDIRECT(G1))
=MAX(INDIRECT(G1))
=MIN(INDIRECT(G1))
Now that you've got the general idea of how to use the INDIRECT function in Excel, we can experiment with more powerful formulas.
INDIRECT formula to dynamically refer to another worksheet
The usefulness of the Excel INDIRECT function is not limited to building "dynamic" cell references. You can also utilize it to refer to cells in other worksheets "on the fly", and here's how.
Suppose, you have some important data in Sheet 1, and you want to pull that data in Sheet 2. The following screenshot demonstrates how an Excel Indirect formula can handle this task:
Let's break apart the formula you see in the screenshot and understand.
As you know, the usual way to reference another sheet in Excel is writing the sheet's name followed by the exclamation mark and a cell / range reference, like SheetName!Range. Since a sheet name often contains a space(s), you'd better enclose it (the name, not a space : ) in single quotes to prevent an error, for example 'My Sheet!'$A$1.
And now, all you have to do is to enter the sheet name in one cell, the cell address in another, concatenate them in a text string, and feed that string to the INDIRECT function. Remember that in a text string, you have to enclose each element other than a cell address or number in double quotes and link all elements together using the concatenation operator (&).
Given the above, we get the following pattern:
Going back to our example, you put the sheet's name in cell A1, and type the cell addresses in column B, as demonstrated in the screenshot above. As the result, you get the following formula:
INDIRECT("'" & $A$1 & "'!" & B1)
Also, please pay attention that if you are copying the formula into multiple cells, you have to lock the reference to the sheet's name using the absolute cell references like $A$1.
Notes
 If either of the cells that contain the 2^{nd} sheet's name and cell address (A1 and B1 in the above formula) is empty, your Indirect formula will return an error. To prevent this, you can wrap the INDIRECT function in the IF function:
IF(OR($A$1="",B1=""), "", INDIRECT("'" & $A$1 & "'!" & B1))
 For the INDIRECT formula that refers to another sheet to work correctly, the referred sheet should be open, otherwise the formula will return a #REF error. To avoid the error, you can use the IFERROR function, which will display an empty string, whatever error occurs:
IFERROR(INDIRECT("'" & $A$1 & "'!" &B1), "")
Creating an Excel dynamic reference to another workbook
The Indirect formula that refers to a different Excel workbook is based on the same approach as a reference to another spreadsheet. You just have to specify the workbook's name is addition to the sheet name and cell address.
To make things easier, let's start with making a reference to another book in the usual way (apostrophes are added in case your book and/or sheet names contain spaces):
'[Book_name.xlsx]Sheet_name'!Range
Assuming that the book name is in cell A2, the sheet name is in B2, and the cell address is in C2, we get the following formula:
=INDIRECT("'[" & $A$2 & "]" & $B$2 & "'!" & C2)
Since you don't want the cells containing the book's and sheet's names to change when copying the formula to other cells, you lock them by using absolute cell references, $A$2 and $B$2, respectively.
And now, you can easily write your own dynamic reference to another Excel workbook by using the following pattern:
Note. The workbook your formula refers to should always be open, otherwise the INDIRECT function will throw a #REF error. As usual, the IFERROR function can help you avoid it:
=IFERROR(INDIRECT("'[" & A2 & "]" & $A$1 & "'!" & B1), "")
Using Excel INDIRECT function to lock a cell reference
Normally, Microsoft Excel changes cell references when you insert new or delete existing rows or columns in a sheet. To prevent this from happening, you can use the INDIRECT function to work with cell references that should remain intact in any case.
To illustrate the difference, please do the following:
 Enter any value in any cell, say, number 20 in cell A1.
 Refer to A1 from two other cells in different ways:
=A1
and=INDIRECT("A1")
 Insert a new row above row 1.
See what happens? The cell with the equal to logical operator still returns 20, because its formula has been automatically changed to =A2. The cell with the INDIRECT formula now returns 0, because the formula was not changed when a new row was inserted and it still refers to cell A1, which is currently empty:
After this demonstration, you may be under the impression that the INDIRECT function is more a nuisance than help. Okay, let's try it another way.
Suppose, you want to sum the values in cells A2:A5, and you can easily do this using the SUM function:
=SUM(A2:A5)
However, you want the formula to remain unchanged, no matter how many rows are deleted or inserted. The most obvious solution  the use of absolute references  won't help. To make sure, enter the formula =SUM($A$2:$A$5)
in some cell, insert a new row, say at row 3, and… find the formula converted to =SUM($A$2:$A$6)
.
Of course, such courtesy of Microsoft Excel will work fine in most cases. Nevertheless, there may be scenarios when you don't want the formula to get changed automatically. The solution is to use the INDIRECT function, like this:
=SUM(INDIRECT("A2:A5"))
Since Excel perceives "A1:A5" as a mere text string rather than a range reference, it won't make any changes when you insert or delete a row(s).
Using INDIRECT with other Excel functions
Apart from SUM, INDIRECT is frequently used with other Excel functions such as ROW, COLUMN, ADDRESS, VLOOKUP, SUMIF, to name a few.
Example 1. INDIRECT and ROW functions
Quite often, the ROW function is used in Excel to return an array of values. For example, you can use the following array formula (remember it requires pressing Ctrl + Shift + Enter) to return the average of the 3 smallest numbers in the range A1:A10:
=AVERAGE(SMALL(A1:A10,ROW(1:3)))
However, if you insert a new row in your worksheet, anywhere between rows 1 and 3, the range in the ROW function will get changed to ROW(1:4) and the formula will return the average of the 4 smallest numbers instead of 3.
To prevent this from happening, nest INDIRECT in the ROW function and your array formula will always remain correct, no matter how many rows are inserted or deleted:
=AVERAGE(SMALL(A1:A10,ROW(INDIRECT("1:3"))))
Here's a couple more examples of using INDIRECT and ROW in conjunction with the LARGE function: How to sum N largest numbers in a range.
Example 2. INDIRECT and ADDRESS functions
You can use Excel INDIRECT together with the ADDRESS function to get a value in a certain cell on the fly.
As you may remember, the ADDRESS function is used in Excel to get a cell address by the row and column numbers. For example, the formula =ADDRESS(1,3)
returns the string $C$1 since C1 is the cell at the intersection of the 1^{st} row and 3^{rd} column.
To create an indirect cell reference, you simply embed the ADDRESS function into an INDIRECT formula like this:
=INDIRECT(ADDRESS(1,3))
Of course, this trivial formula only demonstrates the technique. And here are a few examples that may prove really useful:
 INDIRECT ADDRESS formula  how to switch rows and columns.
 VLOOKUP and INDIRECT  how to dynamically pull data from different sheets.
 INDIRECT with INDEX / MATCH  how to bring a casesensitive VLOOKUP formula to perfection.
 Excel INDIRECT and COUNTIF  how to use the COUNTIF function on a noncontiguous range or a selection of cells.
Using INDIRECT with Data Validation in Excel
You can use the Excel INDIRECT function with Data Validation to create cascading drop down lists that display different choices depending on what value the user selected in the first dropdown.
A simple dependent dropdown list is really easy to make. All it takes is a few named ranges to store the dropdown's items and a simple =INDIRECT(A2)
formula where A2 is the cell displaying your first dropdown list.
To make more complex 3level menus or dropdowns with multiword entries, you will need a bit more complex INDIRECT formula with a nested SUBSTITUTE function.
For the detailed stepbystep guidance on how to use INDIRECT with Excel Data Validation, please check out this tutorial: How to make a dependent drop down list in Excel.
Excel INDIRECT function  possible errors and issues
As demonstrated in the above examples, the INDIRECT function is quite helpful when dealing with cell and range references. However, not all Excel users eagerly embrace it mostly because extensive use of INDIRECT in Excel formulas results in lack of transparency. The INDIRECT function is difficult to review since the cell it refers to is not the ultimate location of the value used in the formula, which is pretty confusing indeed, especially when working with large complex formulas.
In addition to the above said, like any other Excel function, INDIRECT may throw an error if you misuse the function's arguments. Here's a list of most typical mistakes:
Excel INDIRECT #REF! error
Most often, the INDIRECT function returns a #REF! error in three cases:
 ref_text is not a valid cell reference. If the ref_text parameter in your Indirect formula is not a valid cell reference, the formula will result in the #REF! error value. To avoid possible issues, please check the INDIRECT function's arguments.
 Range limit is exceeded. If the ref_text argument of your Indirect formula refers to a range of cells beyond the row limit of 1,048,576 or the column limit of 16,384, you will also get the #REF error in Excel 2007, 2010 and Excel 2013. Earlier Excel versions ignore the exceeded limit and do return some value, though often not the one you'd expect.
 The referred sheet or workbook is closed. If your Indirect formula refers to another Excel workbook or worksheet, that other workbook / spreadsheet must be open, otherwise INDIRECT returns the #REF! error.
Excel INDIRECT #NAME? error
This is the most obvious case, implying that there is some error in the function's name, which leads us to the next point : )
Using the INDIRECT function in nonEnglish locales
You may be curious to know that the English name of the INDIRECT function has been translated into 14 languages, including:


If you are interested to get the full list, please check out this page.
A common problem with nonEnglish localizations is not the INDIRECT function's name, but rather different Regional Settings for the List Separator. In the standard Windows configuration for North America and some other countries, the default List Separator is a comma. While in European countries, the comma is reserved as the Decimal Symbol and the List Separator is set to semicolon.
As a result, when copying a formula between two different Excel locales, you may get the error message "We found a problem with this formula…" because the List separator used in the formula is different from what is set on your machine. If you run into this error when copying some INDIRECT formula from this tutorial into your Excel, simply replace all commas (,) with semicolons (;) to get it fixed.
To check which List Separator and Decimal Symbol are set on your machine, open the Control Panel, and go to Region and Language > Additional Settings.
Hopefully, this tutorial has shed some light on using INDIRECT in Excel. Now that you know its strengths and limitations, it's time to give it a shot and see how the INDIRECT function can simplify your Excel tasks. Thank you for reading!
213 comments
Hey. I am using office 2019 and no sequence fxn is available here to have a sequence of numbers in subsequent cells. From google i got to know a way to do so using row and indirect fxn by using the following command.
=row(indirect("5:10"))
It is working for some youtubers but for me it only returns the first value. In this case "5".
Is this some compatibility issue or version issue again.
3D NOTE: As of 2024, INDIRECT does not seem to work with 3D References (multipleworksheet ranges).
=IFERROR(VLOOKUP(F1;INDIRECT("'["&B9&".xlsx]Sheet2'!B1:f52");C1;FALSE);"No Data")
 $b$9 name workbook
 sheet2 b1:f52 is the sheet name in another workbook
Which part of the formula above needs to be corrected if you want to create dynamic sheet names in another workbook because after I change the formula below it doesn't work (No Data)
=IFERROR(VLOOKUP(F1;INDIRECT("'["&B9&".xlsx]"&B11&"'!");C1;FALSE);"No Data")
b11, name of the sheet you want to make dynamic
 c1, cell address
Thank you in advance
Hi! Compare your two formulas carefully. In the second formula, there is no reference to the cell range. In the first formula, it is B1:f52.
I have a workbook that successfully pulls information from other tabs using the INDIRECT function with cellbased references. For example, A1 contains a CONCATENATE function to pull the cell ID of the other workbook so the referenced cell address doesn't need to be copied into the formula for each instance  there's about 20 columns of data and I need to be able to copy it down about 200 rows.
This works fine if the reference is in the same workbook or another workbook that's currently open, but as the article and other comments state the INDIRECT function only works with open workbooks and the INDEX function can only reference locations manually entered in the formula.
How would I go about setting up a closed workbook reference where the following conditions are met:
1. External workbooks are open or closed (I can work with closed only if need be)
2. The same format is used in the external workbooks so all the cell references from one row to the next will be identical (A1 in workbook 1 will be the same data type as A1 in workbook2)
3. I need to be able to point to a cell with the path of the external workbook as the reference
4. I'd prefer formulas, but I'm not opposed to using VBA
Hello Scott!
If I understand your task correctly, to get data from a closed Excel workbook, you can use a hyperlink. The following tutorial should help: How to use Excel HYPERLINK function to create different link types.
=IFERROR(VLOOKUP($F$7;INDIRECT("'["&$B$9&".xlsx]kelas 7'!$B$1:$f$52");C$1;FALSE);"No Data") I want to retrieve data in another file ($B$9.xlsx)
Can you enlighten me about the correct formula. Thank You
Hi! I don't have your data, so I can't create a formula for you. Pay attention to the following paragraph of the article above: Making a dynamic reference to another workbook. It covers your case completely.
I have a template that has multiple section with the following formula in each column section
=SUM(INDIRECT("Plan_EmployeeCosts["&C$6&"]"))
Would you explain what's going on inside the brackets? Is the $ being concatenated with a 6 digit number?
Hi! The INDIRECT formula creates a structured reference using the value in cell C6. For more details, click here: Structured reference in Excel tables.
Hi Alexander.
Is there a way to use Indirect to reference another FORMULA? I mean, based on another cell value, the Indirect function should choose between two or more different formulas that are defined on a specific range (as text, not as an actual formulas):
Ex: Cell "A1" contains: "=Sum(B1:B10)"
Cell "A2" contains: "=SumProduct(C1:C10)"
I want an Indirect formula, say on Cell "D1", to reference "A1" or "B2" and gives the result of the respective sum.
Can Indirect function reference and perform the calculation of the chosen formula?
Sorry, to reference "A1" or "A2", not "B2"...
Hi! Pay attention to the following:
1. The INDIRECT function can only be used to create a reference to a cell or range. The article above explains this in detail.
2. A formula that is written as a text can only be executed by using VBA.
I recommend using the IF function, which can calculate based on one or more conditions.
Read more: Nested IF in Excel – formula with multiple conditions.
I tried to refer one cell reference (B11) with column heading and get the data from other sheet.
SN Roll No (B11) Amount (C11)
The following formula used whereas it is showing error #REF!
HLOOKUP(C$1,'B2019'!$C$2:$DB$1970,$B11,FALSE)
Please guide
Hi! I can't check a formula that contains unique references to your data, which I don't have. I can't see your data, but this formula doesn't return a #REF! Have a close look at the syntax of the HLOOKUP function in this article: Excel HLOOKUP function with formula examples. B11 is the column number, not the column heading.
Perhaps you can solve the problem with the help of this article: INDEX MATCH MATCH in Excel for twodimensional lookup.
Or provide me with an example of the source data and the expected result.
Is there a way to indirect function to another tab in a worksheet?
Example
Cell E1 (Rib Rack) has the same tab as the tab its refencing.
Cell F1 (=SUM('Rib Rack'!C:C)) is refencing a total from that tab.
Is there a way to reference sell E1 into F1s formula to avoid typing it in each time I add a new product?
Hi! If I understand your task correctly, you want to create a dynamic reference to the worksheet. To do this, you can use the INDIRECT function as described the following paragraph of the article above: INDIRECT formula to dynamically refer to another worksheet.
Based on your information, the formula might look something like this:
=SUM(INDIRECT("'" & $E$1 & "'!" & "C:C"))
Is it possible to use INDIRECT in a reference to a range. For example, I want the data range in my chart to extend to a date that I type into a cell (say A1 on Sheet 1). The adjacent cell (B1) then finds the row for that date in the data in another sheet (Sheet2) using a MATCH formula, returning just the corresponding row number (e.g. "450"). I want to use INDIRECT to define the series range in the chart on Sheet1. The range will always s start at Sheet2 A1, I just need the INDIRECT function to tell it to extend to Sheet2 A450. How would this look in the formula?
thanks
Hi! You can create a range reference using the INDIRECT function as described in the article above. For example:
=INDIRECT("Sheet2!A1:A"&B1)
But you can't use this formula to specify a range of data for a chart. Read more about creating a chart here: How to create a chart (graph) in Excel.
Thanks. Is there any way to make a chart range automatically adjust to a date entered in a cell?
You cannot do this using standard Excel features. You can use VBA.
hi, I have the following input into 'conditional formatting' for a cell in 'Summary' tab
=IF(AND(INDIRECT($A5&"!$Q$301")>INDIRECT($A5&"!$Q$300"),INDIRECT($A5&"!$Q$300")>INDIRECT($A5&"!$Q$299")),"G1","G2').
though there is no error message, there is also no output either
I am working on creating formula for stock ticker, say AAPL (Apple).
so while cell A5 in 'Summary' tab would contain 'AAPL', another tab (in same workbook) titled 'AAPL' would contain some data, and i wish check values in cell Q301, Q300, Q299 in the 'AAPL' tab.
may i know why there is no output in this case. thanks
Hi! If you replace the single quote "G2' with a double quote "G2" in your formula, it will work.
hi Sir, much appreciated. further to above question, I am trying to identify turning point of a histogram, from negative to positive and vice versa.
Say I have only 4 cells of concern (Q298 to Q301, on respective ticker tab) and I have the following conditions to be satisfied (that is, 'AND')
IF Q299 > Q298 AND
Q300 > Q299 AND
Q301 > Q300 AND
Q301 > 0
IF Q300 > 0, output "G2", IF Q300 < 0, output "G1". (that is, if trend of histogram just turned positive at Q301, denote as "G1")
Conversely IF Q299 < Q298 AND
Q300 < Q299 AND
Q301 < Q300 AND
Q301 < 0
IF Q300 0, output "R1". (that is, if trend of histogram turned neg at Q301, denote as "R1")
I would like to return "empty" for all other scenarios (for which there are many)
May I know how to go about it (if I would like to use INDIRECT again as per above?). Otherwise, could please direct me what function would be most efficient for this purpose?
very much obliged. Thanks.
Hi! Your formula has 2 conditions in the AND function. You can add 2 more similar conditions to them as described here: IF AND formula in Excel.
hi Sir, my very last Q pls. say (using IF formula) output for
Column AB AC AD AE AF AG
output is "G" or "" "G2" or "" "G1" or "" "R" or "" "R2" or "" "R1" or "" (nothing)
I would like to for my Column AA to contain (just) one output  from either Column AB to AG or to output nothing  with expectedly priority ranking. Incidentally "G1","G2", "R1" and "R2" are mutually exclusive  while they are in turn subset of "G" and "R".
In priority ranking order, it would be G1 first, then G2 second, and lastly G  similarly, R1 first, R2 second, and R last.
So for example, IF "G2", "G" are output 'spotted'  column AA to show "G2"
IF "G1","G2" are seen  then column AA to show "G1"
IF only "G" is seen (no G1 or G2 as stricter conditions are not satisfied)  then column AA to show "G"
Else column AA to output "".
Similarly for R1, R2, and R. for avoidance of doubt, "G" and "R" are opposite  so they will not both appear at any one time.
May I know how to go about it (noting they are nonnumbers) please?
wishing you bliss & health in 2024 please. thanks
Hi! If my understanding of the problem is correct, you can use INDEX MATCH function to compare the values of cells AB1:AF1 with a list of values. The MIN function will help you to find the first match.
=INDEX({"G","G1","G2","R","R1","R2"}, MIN(IFERROR(MATCH(AB1:AF1,{"G","G1","G2","R","R1","R2"},0),99)))
You can replace the list of values with cell references:
=INDEX(Z1:Z6, MIN(IFERROR(MATCH(AB1:AF1,Z1:Z6,0),99)))
I hope it’ll be helpful. If this is not what you wanted, please describe the problem in more detail.
I have been using INDIRECT with a dynamic updated reference to other workbooks. When upgrading from Excel 2013 to the latest version this is not possible anymore. Is there an alternative way to achieve this without opening the other workbooks?
hi, I'm trying to indirect to another workbook and use the formula given =IFERROR(INDIRECT("'[" & A2 & "]" & $A$1 & "'!" & B1), ""), but it always gives me either REF or NAME, when i check the error, usually wrong in the A2 section/workbook name, while I'm already checked that the A2 is already correct. help?
Hi! I can't check your formula as I don't have your data and I don't know what is written in A1, A2, B1.
But you cannot get REF or NAME errors when using the IFERROR function.
Hi! I need to link specific cells (constant) from multiple spreadsheets into one spreadsheet and until now I couldn't find a better way to do this but to replace manually file's name (path) in the formula. Looks like INDIRECT function can be handy here, but again can't figure out a way how to do that.
Thanks in advance for advice or suggestion you may offer.
Hi! If I understand your task correctly, pay attention to the following paragraph of the article above: Creating an Excel dynamic reference to another workbook. I hope it’ll be helpful.
Thanks for right direction Alexander! Applied it to my spreadsheet and only then realized that it works only with another workbook open. Can't really have hundreds of workbooks open all the time. Do you know any other similar function, but which would update information from closed workbooks on SharePoint.
Hi! To update information from closed workbooks, maybe this guide will be helpful: External reference to a closed workbook.
Hi! Is it possible to use INDIRECT with a named area in another workbook?
In the explanation above all references to other workbooks are spelled out: =INDIRECT("'[" & $A$2 & "]" & $B$2 & "'!" & C2)
where the book name is in cell A2, the sheet name is in B2, and the cell address is in C2.
In my example C2 is not a cell address, but a named area.
To take this a step further: I created a named are in my main book which refers to a named area in a 2nd book, and I try to use INDIRECT to compose the name of the named area in my main book. I'm still getting a #REF error, even if I open de 2nd book.
Specifics:
In my main book I have a named area M_Band, referring to a named area M_Band in a 2nd book.
In my main book
=VLOOKUP(B1;M_Band;12;FALSE) works fine, but
=VLOOKUP(B1;INDIRECT(C2);12;FALSE) gives an error (cell C2 contains the text "M_Band")
Notes:
1. I always name areas that I use in VLOOKUPs to keep my formulas readable
2. If I use a named area from another book, I usually create a named reference for it, rather than using the '[Book_name.xlsx]Sheet_name'!Range notation. This way I can change it in one place (in the name manager) instead of all formulas where it is used.
Hi! In the INDIRECT formula, the text reference to the named range must contain the file name and worksheet name if the named range is in another file.
Hi! I have set up a Summary Sheet for open insurance cases and a Template to input Case Details for each open insurance case. For each new client (example below STRICKLAND), I copy the template sheet (example below TEMPLATE) and rename it to the new client name (STRICKLAND). I would like to type info in the case details sheet (STRICKLAND) and it fill in the summary sheet (THAT'S WHERE THE FORMULA IS). Here is the formula I'm using and receiving #REF error. (for example B2 is the Client Name)
=INDIRECT("'"&"'STRICKLAND'!"&"'Template'!B2")
Hi! I can't check your formula as I don't have your data. But there is an error in your formula  an extra "!". Pay attention to the correct template 
=INDIRECT("'[" & Book name & "]" & Sheet name & "'!" & Cell address)
Hello! I have an existing workbook created by another person and I'm adding a table into a sheet which uses list functions to select a product. I've added everything identical to the other products however the formula continues to return blanks
=IF(ISERROR(VLOOKUP(H11,INDIRECT(E11),18,0)),"",(VLOOKUP(H11,INDIRECT(E11),18,0)))
Any ideas why it will not recognize a new table to lookup? It is within the range of the list and embedded between other working tables. Thank you in advance!
Hi! I can't check a formula that contains unique references to your data, which I don't have. Your VLOOKUP function does not find the data. Also check which reference the INDIRECT returns.
When using INDIRECT to produce the second part of a cell range. (The last row changes and I want my range to reflect the start of the range as $C$2 and the end of the range be variable. $H$73 (where the 73 can change based on the value of the last row of data in the sheet  I use a max() function to get that number. I named the cell with the result, AMs. This function is in a different workbook on Sheet1) The two sheets in the workbook where I am doing the vlookup() from are Current and AM Only.
Here is the formula: =IF(H5="Unknown","TopRx",VLOOKUP(M5,'[Account Managers Office 06.07.23_Final.xlsx]Sheet1'!$C$2:INDIRECT("$H$" & AMs),6,TRUE))
In the calling workbook, H5="Local" so it moves to the VLOOKUP() function. M5 is the name of a sales rep found within the range in the other workbook on Sheet1
Independently, $C$2:INDIRECT("$H$" & AMs) returns "$H$72" as desired but using it in the vlookup() formula, I get #VALUE!
Hi
Is it possible to make the cell reference draggable? So in A2 I have
=Indirect("'PASTE  Latest "&$A$1& " Forecast'!E5")
I want to be able to drag this so A3 would become
=Indirect("'PASTE  Latest "&$A$1& " Forecast'!E6")?
The cell reference needs to be within the Indirect bracket to make it work, but I need it to be dynamic and I can't make it move. Any way around it?
Hi! Try using the CELL function to get the cell address.
=Indirect("'PASTE  Latest "&$A$1& " Forecast'!"&CELL("address",E5))
Hi There!
I am trying to use the formula =INDIRECT("'"&C3&"'!&F2"), (where in C3  is the sheet name = Match 3, and F2 is the cell in that sheet containing one of the team's name). However this formula is not returning the desired result (I want team's name that participated in match 3), rather it returns #REF!. Not sure if there is a checkbox that needs be enabled or something here, to disable the #REF! error.
Hi! If I understand your task correctly, the following formula should work for you:
=INDIRECT("'"&C3&"'!"&"F2")
I am looking to return a status text from a second sheet in specific cell
My main sheet has in column C row 3 the names of my sheets  this sheet is called "Test Sheet 123"
I have in the "Test sheet 123" in cell B2 a text "Closed"
From my main sheet I have in cell G2 tried the following formula
=INDIRECT(C3!B2) but that does not get the value "Closed"
=INDIRECT(C3&"!B2") but that does not get the value "Closed"
=INDIRECT("'" &C3 & "!B2") but that does not get the value "Closed"
Anyone that can help me ?
Hi! If I got you right, the formula below will help you with your task:
=INDIRECT("'" &C3 &"'!B2")