Use the INDIRECT function to create a reference in Excel

12/10/2019 2 minutes to read Applies to: Microsoft Office Excel 2003, Microsoft Office Excel 2007, Excel 2010 In this article SUMMARY In Microsoft Excel, the INDIRECT worksheet function returns the contents of the specified reference and displays its content. You can use the INDIRECT worksheet function to create references linked to other workbooks. You can reference each attribute of the reference (workbook name, spreadsheet name, and cell reference) individually by using the INDIRECT function to create a user-defined dynamic reference with cell references. spreadsheet as entries. MORE INFORMATION Notes The INDIRECT function returns only the result of a reference to an open file. If a workbook whose indirect function refers indirectly is closed, the function returns a #REF! “. To create a reference to a workbook using three different cell entries as references for the workbook, spreadsheet, and cell, follow the steps in the following examples. Example 1 Start Excel. In Workbook1, Sheet1, cell a1 type this is a test. In Microsoft Office Excel 2003 and in earlier versions of Excel, click New on the File menu, click Workbook, and then click OK. In Microsoft Office Excel 2007, click the Microsoft Office Button, click New, and then click Create. In Microsoft Office Excel 2010, click the File menu, click New, and then click Create. In book2, Sheet1, cell a1, type Workbook1. In book2, Sheet1, cell a2, type Sheet1. In book2, Sheet1, cell a3, type a1. Save both workbooks. In Excel 2003 and in earlier versions of Excel, type the following formula in book2, Sheet1, cell B1: = INDIRECT ( ” ‘[” &a1& “. xls] ” & a2 & “‘! ” & A3) In Excel 2007, type the following formula: = INDIRECT ( ” ‘[” &a1& “. xlsx] ” & a2 & “‘! ” & A3) The formula returns “This is a test”. Example 2 In Excel 2003 and in earlier versions of Excel, you can replace the formula in Example 1 with several indirect statements, as in the following formula: = INDIRECT ( ” ‘[” &INDIRECT (“a1”) & “. xls] “& INDIRECT (” a2 “) & ” ”! “& INDIRECT (” A3 “)) In Excel 2007 and Excel 2010, type the following formula: = INDIRECT (” ‘[” &INDIRECT (“a1”) & “. xlsx] “& INDIRECT (” a2 “) & ” ”! “& INDIRECT (” A3 “) Notes The difference between the way Excel refers to cells. Example 1 references cells a1, a2, and a3 without quotation marks, while Example 2 references cells with quotation marks around the references. The INDIRECT function refers to cells without using quotation marks. This function evaluates the result of the cell reference. For example, if cell a1 contains the text “B1” and B1 contains the word “TEST”, the formula = INDIRECT (a1) returns the result “TEST”. However, referencing a cell with quotation marks returns the result of the contents of the cell. In the example in the previous sentence, the formula returns the text string “B1” instead of the contents of cell B1.


Leave a Reply

Your email address will not be published. Required fields are marked *