The Excel INDIRECT formula is a very flexible and powerful formula that allows you to make your formula references dynamic. In its simplest form, the Excel INDIRECT formula allows you to put the reference for one cell inside another, and then obtain the data from the first cell by referencing the formula. So, if your formula reads =INDIRECT(A1) and the value of A1 is “B1”, then the formula will return the value of B1. In effect, the INDIRECT formula converts any string of text into a cell reference. Creating dynamic cell references is where this formula is most useful.
How to Use the Excel INDIRECT Formula
To see how the Excel INDIRECT formula works with cell references in practice, consider the example of a workbook with multiple tabs, with different (class) names, where each tab looks like this (with scores filled in for each question):
If you wanted to create a master summary table (as in the example below), where each class is listed out on the left-hand side of the table and a summary of their scores is presented on the right, you could use the INDIRECT formula to accomplish this fairly easily.
You can use the Excel INDIRECT formula to turn each of the class names in the master summary table into a tab and cell reference. This will allow you to pull in each of the values from the separate worksheets without a lot of cutting and pasting. To pull in the data for the first question, simply insert the formula, =INDIRECT(A2&”! B2″). The INDIRECT formula will then return the value in A2 (which is “Class1”) and merge that with the concatenation “! B2” (we’re using cell B2 because that is the cell that contains the score value for Question1). This results in the cell reference “Class1! B2”. So, Excel will pull the value from cell B2 on the Class1 sheet into the master summary table. Simply copy the formula down to the remaining rows to pull in the values for the remaining sheets. As you can see, using the Excel INDIRECT formula is a quick and easy way to create dynamic cell references that allow you to pull in data from multiple sheets without having to directly specify the sheet names in the formula.
Learn even more free tips and tricks when you signup for our free email newsletter. You’ll receive free Excel tips and tricks directly in your inbox and also receive exclusive discounts to our upcoming training classes.