By Calvin Piston, Dean of Institutional Effectiveness, John Brown University
You can use the INDIRECT function when you want to change the reference to a cell within a formula without changing the formula itself. I have used this function for several projects, including tracking student enrollment.
Whenever a cell on another sheet of an Excel file is referenced, the formula bar displays the location. I wanted to be able to use this idea in a formula where the sheet names are in the first row of a summary sheet.
As an example, we have an online program for which I wanted to track students by terms enrolled. My goal was to produce a chart like the one below, where I could speed-fill new columns from previous column formulae as a new term (and thus a new sheet) was added. The column headings for the terms match the sheet names and so the sheet names must be part of the cell formula.
Unfortunately, typing a formula that references another sheet in such a way that the speed-fill works across rows cannot be done directly.
The term sheets in the example are a list of student ID numbers for students enrolled in that term; the list of IDs is in column A of each sheet. As an example, to check directly if the student in line 146 was enrolled in 2014, I use the COUNTIF function in a formula, selecting Column A from sheet “Fall 2014” and the student’s ID (A146):
= COUNTIF('Fall 2014'!A:A,A146) into cell P146, resulting in a 0 or 1.
Since the desired outcome is to have the cell value reflect the number of terms the student has been enrolled, the previous terms need to be counted as well. To do this, I count the number of non-zeroes in the cells reflecting previous terms (the first term in the file, Fall 2011, is in column G) and since I want the sum only if the student has been enrolled in the term in question, I add a conditional If-then statement. So, the formula becomes:
=IF(COUNTIF('Fall 2014'!A:A,A146)>0,COUNTIF('Fall 2014'!A:A,A146)+COUNTIF($G146:O146,">0"),0)
This formula can be used to speed-fill the remainder of column P (Fall 2014).
However, each time a new term is added to the file, a new formula is needed. The solution is using the INDIRECT function. This function uses indirect addressing to refer to a cell name by a text string, allowing the formula to include a reference to the needed sheet. For example, to reference sheet “Fall 2014,” “INDIRECT(p$1)” is the correct function to use.
To create the entire reference, “ 'Fall 2014'!a:a ” as a text string, use the CONCATENATE function (or simply an & between text strings.) That is, we concatenate the three strings ', P$1, '!a:a. The first and third stings need to be enclosed within double quotes.
The formula now becomes:
=IF(COUNTIF(INDIRECT("'"&P$1&"'!A:A"),$A146)>0,COUNTIF(INDIRECT("'"&P$1&"'!A:A"),$A146)+COUNTIF($G146:O146,">0"),0)
Now when a new term is added, the term name is used for both the name of the new sheet and as the heading of a new column. The remainder of the new column can be speed-filled from the previous column.
Once this is in place, I can use the sheet names in the first row, and then speed-fill and/or paste formulas across the sheet that makes proper reference to the other sheets.