INDIRECT to Switch Worksheets in Microsoft Excel

Warning: The INDIRECT function doesn't update data unless the workbook that you reference in also open. This often makes this function undesirable for use.

Let’s say you have a workbook containing multiple sheets of data. You need a quick way to see a summary of the data on any of these sheets. You don’t want to have a summary on every sheet. In this demonstration, we have many employees, and we want to summarize Total Sales and Average Sales for any employee.

Use the INDIRECT function, which helps us convert a value in a cell into becoming part of our formula.

First, insert a summary worksheet in the beginning of the document as shown. Also, on Cell C5, you could create a drop-down list with the employee names.

If you choose “Bill” in the drop-down, the string C5&"!C8:C13" from the INDIRECT formula converts into the string Bill!C8:C13.

In other words, when you choose “Bill” in the drop-down menu, the formula

=SUM(INDIRECT(C5&"!C8:C13"))

becomes the same as if it were written:

=SUM(Bill!C8:C13)

If you want to prevent the above formula from giving a nasty #REF! error when you delete the contents of cell C5, then you can error-proof the formula like this:

=IF(C5="","",SUM(INDIRECT(C5&"!C8:C13")))