The ExcelINDIRECTfunction returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value that can be used as a valid reference.
Now, let's look at using the INDIRECTfunction to reference a cell in another sheet. Cell A1 of Sheet2 contains the number 100, and we want to create an INDIRECT reference to that cell in the current sheet.
Use the INDIRECTfunction in Excel to convert a text string into a valid range reference. For example, use SUM and INDIRECT. Explanation: the formula above reduces to =SUM (INDIRECT ("D3:D6")). The INDIRECTfunction converts the text string "D3:D6" into a valid range reference. In other words, =SUM (INDIRECT ("D3:D6")) reduces to =SUM (D3:D6).
In this guide, I will show you how to use the INDIRECT() function for cell referencing and troubleshoot common problems, including when to avoid using the function. If you are unfamiliar with some basics, I recommend you take our Introduction to Excel course to build a solid foundation.
From the backstage view, the INDIRECTfunction turned the text string in Cell B2 (A2) into a cell range and created an indirect reference to it. Not a problem! In Cell B2, write the cell range A2:A5. Again write the INDIRECTfunction just like above. Hit Enter.
INDIRECTfunction in Excel helps users reference cells, ranges or arrays, and even worksheets and workbooks indirectly. The function takes the cell reference as the text string and returns the value in the given cell reference. Thus, it returns the cell reference specified by the text string.
This ExcelINDIRECT tutorial explains the function's syntax, basic uses and provides a number of formula examples that demonstrate how to use INDIRECT in Excel.
Here’s a simple step-by-step guide to mastering the INDIRECTfunction in Excel. In this section, we’ll walk you through how to use the INDIRECTfunction to make your cell references dynamic. This will ultimately make your Excel workbooks more versatile and easier to manage.
