Which function returns a reference to a cell or cell range that is a specified number of rows and columns?
Show
The Microsoft Excel INDIRECT function returns a reference to a range, and I use it to make dependent drop down lists in my Excel workbook, to show items based on another cell's value. You can also use INDIRECT to create a reference that won't change, if rows or columns are inserted in the worksheet, where you are using the SUM function. Author: Debra Dalgleish Video: INDIRECT Function ExamplesWatch this short Excel tutorial video, to see how to use the INDIRECT function on its own, or combined with other Excel functions. Written instructions are below the video. Download the sample INDIRECT workbook, to see the examples, and to follow along with the video. Video Timeline
Thanks to Dave Peterson, for his contributions to this page. How INDIRECT Function WorksThe INDIRECT function is useful when you want to return a value, based on a text string. For example, select a range name from a drop down list, and get the total amount for the selected range. In this screen shot, there is a drop down list in cell B2, where you can choose Actual or Budget. After you make a selection, the total for that type appears in cell B3. INDIRECT Function Syntax ArgumentsThe INDIRECT function's syntax has two arguments: INDIRECT(ref_text,a1)
Dependent Drop Down Lists
The short video below, shows the steps, and there are detailed written steps, and sample files, on the Dependent Drop Down Lists page. For example,
Data Validation RuleThe INDIRECT function is used in the data validation settings dialog box, like the formula example shown below
The video below shows how to set this up. Video: Dependent Drop Down ListsIn this short video, you'll see how to set up a main drop down list, with a dependent drop down list in the next column, that uses the INDIRECT function. There are written instructions, and a sample file to download, on the Dependent DropDown Lists Video page. Intro to INDIRECT Video ExamplesHere are the written steps for the INDIRECT examples that are in the Introduction to INDIRECT video, shown above. 1) Lock a Cell Reference 2) Refer to Different Sheet 3) Refer To Different Workbook 4) Refer to a Named Range Lock a Cell ReferenceIf you create a SUM formula, and cells are inserted later, at the top of the list, the SUM formula might not include the new amounts. Here's how you can use the INDIRECT function to avoid that problem. The INDIRECT function can "lock" a specific cell in a formula. Then, if rows or columns are inserted or deleted above or to the left of that cell, the reference does not change. Without INDIRECT, the reference would automatically adjust. To see how this works, follow these steps to create two formulas -- one with normal cell references, and one with an INDIRECT function combined with the SUM function.
Insert a RowTo see the difference between the formulas, insert a blank row above row 2, and enter 100 for January, in cells C2 and E2.
Refer To Different SheetAn INDIRECT formula can also refer to cells on other worksheets. In this example, you'll create a formula with the INDIRECT function, using references to a sheet name and cell name.
Note: If either cell A2 or B2 is empty, the formula will return an error. To prevent this, you can add an IF function: =IF(OR(A2="",B2=""),"",INDIRECT("'" & A2 & "'!" & B2)) Refer to Different WorkbookAn INDIRECT formula can refer to cells in other workbooks, but will return a #REF! error if that workbook is closed. In this example, you'll create a formula with the INDIRECT function, using references to a file name, sheet name and cell name. Set up a Data Workbook
Set up a Linked WorkbookNext, follow these steps, to create a workbook that will link to the data workbook. Or, get the download file, and go to the WkbkRef sheet.
Get a Sample FormulaTo see the syntax that you'll need to use in your INDIRECT formula, follow these steps:
Create an INDIRECT formulaNext, follow these steps to create an INDIRECT formula that uses the same syntax. The formula will include the single quote marks, square brackets and exclamation mark.
How the Formula Works
Test the Formula
Formula NotesNote1: If A2, A3 or A4 is empty, the formula will return an error. To prevent this, you can add an IF function: =IF(OR(A2="",A3="",A4=""),"",INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)) Note2: If the Test File.xlsx workbook is closed, the INDIRECT formula will return a #REF error. I haven't used the following files, but they may help you if you need to pull data from a closed workbook:
Refer to a Named RangeIn addition to cell references, you can refer to named ranges in an INDIRECT formula. In this example, the INDIRECT function is used to sum the selected named range.
Ref_Text Argument Examples
In most cases, a cell reference or combination of text string and cell reference works best with the INDIRECT function. Text string -- address is typed into the formula (not flexible)
Cell reference -- refers to a cell that contains a text string
Combined -- Text string and Cell reference
Get the Sample File
Symbols
Related PagesFunctions List INDIRECT - Compare Sheets VLOOKUP CHOOSE COUNT / COUNTIF INDEX / MATCH Which function returns a reference to a cell or cell range that is specified?The Excel ADDRESS function returns the address for a cell based on a given row and column number.
What function returns the row number of a cell reference?The Excel ROW function returns the row number for a reference. For example, ROW(C5) returns 5, since C5 is the fifth row in the spreadsheet. When no reference is provided, ROW returns the row number of the cell which contains the formula.
What function of Excel returns the number of rows in the specified range?The ROWS Function[1] is an Excel Lookup/Reference Function. The function is used to look up and provide the number of rows in each reference or array.
What is Excel offset function used for?The main purpose of OFFSET is to allow formulas to dynamically adjust to available data or to user input. The OFFSET function can be used to build a dynamic named range for charts or pivot tables, to ensure that source data is always up to date.
|