what function returns a reference to a cell
Excel INDIRECT Function
The INDIRECT office returns a reference to a range. You lot can use this part to create a reference that won't change if row or columns are inserted in the worksheet. Or, use information technology to create a reference from letters and numbers in other cells.
INDIRECT Role Introduction
Watch this short video, to see how to use the INDIRECT function. Written instructions are below the video. Download the sample INDIRECT workbook, to see the examples.
Thank you to Dave Peterson, for his contributions to this page.
How INDIRECT Function Works
The INDIRECT part is useful when y'all want to return a value, based on a text string. For example, select a range name from a drop downward list, and get the total amount for the selected range. In this screen shot, choose Actual or Budget in cell B2, and the total appears in cell B3.
INDIRECT Office Arguments
The INDIRECT function has ii arguments: INDIRECT(ref_text,a1)
- ref_text: A cell reference or a text string (or both), that create a range reference to a cell, range of cells or named range.
- a1: (optional) Does the reference use A1 reference style?
- TRUE, or omitted -- ref_text is must be "A1" style
- False -- ref_text must be "R1C1" style
Examples
In near cases, a jail cell reference or combination of text string and prison cell reference works all-time with the INDIRECT function. The Budget/Actual case to a higher place shows this technique.
If you lot apply a elementary text string, the range is "hard coded" in the formula, which has express usefulness, except for locking a cell reference.
Text cord -- address is typed into the formula (not flexible)
- A1 style: =INDIRECT("A4") or =INDIRECT("Demo!B3") or =INDIRECT("Upkeep")
- R1C1 style: =INDIRECT("R4C1",Faux)
Cell reference -- refers to a prison cell that contains a text string
- A1 mode: =INDIRECT(C6)
- R1C1 style: =INDIRECT(C8, Imitation)
Combined -- Text string and Cell reference
- A1 style: =SUM(INDIRECT("A4:A" & C12))
Refer to Different Sheet or Workbook
Run across the examples below, for referring to a different worksheet, or a different workbook.
NOTE: If the INDIRECT formula refers to a different workbook, that workbook must exist open, or the formula volition return a #REF! error.
Lock a Cell Reference
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 suit.
To come across how this works, follow these steps to create ii formulas -- i with normal prison cell references, and one with an INDIRECT function.
- In cells C2:C7, type a list of numbers
- Copy the listing into E2:E7
- In prison cell C8, blazon a SUM formula: =SUM(C2:C7)
- In cell E8, blazon this formula:=SUM(INDIRECT("E2"):E7)
Insert a Row
To see the difference between the formulas, insert a bare row above row 2, and enter 100 for Jan, in cells C2 and E2.
- The total amount changes in column E, because the start cell is locked at E2.
- The total amount does Not change in column C. The showtime cell shifts downwards to cell C3.
Refer To Different Canvas
An INDIRECT formula tin also refer to cells on other worksheets. In this instance, you'll create a formula with the INDIRECT function, using references to a sail proper name and cell proper name.
- On a worksheet named Data Canvas, enter numbers in cells A1:A10
- On a different sail, in cell A2, enter the sheet name: Data Sheet
- In cell B2, type a cell name from the range of numbers, e.1000.: A2
- In cell C2, type the post-obit formula:
=INDIRECT("'" & A2 & "'!" & B2) - The first role of the string is a single quote within a set of double quotes:
" ' " (spaces were added for clarity)
- Later the reference to A2, which contains the canvas proper name, is a single quote and exclamation marking, within a gear up of double quotes:
" ' ! " (spaces were added for clarity) - The string ends with a reference to cell B2, which contains the cell address.
- Unmarried quotes are included in the cord to prevent errors if the sheet proper name contains space characters.
- Press the Enter primal, and the formula returns the number in cell A2 on the Data Sheet worksheet.
- Alter the number in prison cell C2, and the result in D2 will change.
Note: If either cell A2 or B2 is empty, the formula will return an mistake. To forestall this, you tin add an IF function:
=IF(OR(A2="",B2=""),"",INDIRECT("'" & A2 & "'!" & B2))
Refer to Different Workbook
An INDIRECT formula tin refer to cells in other workbooks, just will return a #REF! error if that workbook is closed. In this instance, yous'll create a formula with the INDIRECT function, using references to a file name, canvass name and cell proper noun.
Prepare a Data Workbook
- Create a new Excel file named TestFile.xlsx
- Change the starting time sail name to Test Data
- On the Test Data sheet, enter numbers in cells A1:A10
- Save the workbook
Set a Linked Workbook
Adjacent, 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.
- Create another new Excel file, named LinkedFile.xlsx
- On the commencement canvass, in cell A2, blazon the data file proper noun, with or without file extension: Test File
- In jail cell A3, enter the sheet name: Test Data
- In jail cell A4, type A7 -- we'll pull a number from that cell in the information workbook
Get a Sample Formula
To run across the syntax that you lot'll need to use in your INDIRECT formula, follow these steps:
- In the linked file, select cell A7, and type an equal sign: =
- Switch to Test File workbook, click on prison cell A7, and press Enter
- In the formula bar, you'll see the reference that was created:
='[Exam File.xlx due south]Examination Information'!$A$7 - To keep that formula visible, type an apostrophe in front of the equal sign
Create an INDIRECT formula
Side by side, follow these steps to create an INDIRECT formula that uses the aforementioned syntax. The formula will include the unmarried quote marks, foursquare brackets and exclamation mark.
- In cell A6, type the post-obit formula:
=INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4)
How the Formula Works
- The first office of the string is a single quote and square subclass, within a set of double quotes:
" ' [ " (I added spaces here, and so it'south easier to read). Single quotes are included in the string to prevent errors if the sheet name or file proper noun comprise infinite characters, like there are in this example - The & (ampersand) characters join all the parts of the text string together.
- A2 is the cell that has the data file name
- Side by side, there are characters to finish the data workbook name -- a square bracket, inside a set of double quotes: " ] " (spaces added for clarity)
- A3 is the cell with the sheet proper noun
- Next, in that location are characters to terminate the information sheet name -- a single quote and exclamation mark, inside a set of double quotes: " ' ! " (spaces added for clarity)
- A4 is the prison cell with the data jail cell address.
Examination the Formula
- Alter the cell address in jail cell A4, and the issue in A6 will change.
- After the INDIRECT formula is working, you lot can delete the sample link in cell A5
Formula Notes
Note1: If A2, A3 or A4 is empty, the formula volition return an fault. To foreclose this, you lot tin add an IF function:
=IF(OR(A2="",A3="",A4=""),"",INDIRECT("'[" & A2 & "]" & A3 & "'!" & A4))
Note2: If the Test File.xlsx workbook is airtight, the INDIRECT formula will return a #REF error. I oasis't used the following files, simply they may help you if y'all need to pull data from a closed workbook:
- Harlan Grove wrote a UDF called PULL that will retrieve the value from a closed workbook. You can discover the function at Harlan's download site:
- https://www.4shared.com/file/L_eA8s4G/pull.html
- Laurent Longre has an add-in (morefunc.xll) at:
- https://www.ozgrid.com/Services/ExternalFree.htm#MoreFunc
- Information technology includes =indirect.ext() that may help you lot.
Refer to a Named Range
In addition to cell references, you lot can refer to named ranges in an INDIRECT formula. In this example, the INDIRECT role is used to sum the selected named range.
- In cells A1:B5, type headings and numbers, every bit shown at right.
- Proper noun cells A2:A5 equally East, and cells B2:B5 every bit West. At that place are naming instructions here:
Names -- Naming Ranges
- In jail cell D2, blazon the name of one of the ranges, e.g. East
- In cell E2, type the formula: =SUM(INDIRECT(D2))
- Press the Enter key, and the formula returns the sum of numbers in the East range.
- Modify cell D2 to West, and the formula returns the sum of numbers in the Westward range.
Dependent Drop Down Lists
The INDIRECT office can exist used with Data Validation, to create dependent drop down lists. For case, select the Fruit category in column A, and the data validation list in column B shows a list of Fruit options.
There are instructions for this technique on the Data Validation - Dependent Lists page.
Get the Sample File
Go the sample INDIRECT workbook The zipped file is in xlsx format, and does non contain any macros.
Related Pages
Functions List
INDIRECT - Compare Sheets
VLOOKUP
CHOOSE
COUNT / COUNTIF
INDEX / MATCH
Source: https://www.contextures.com/xlfunctions05.html
0 Response to "what function returns a reference to a cell"
Post a Comment