Windows  |  Free Downloads

 

 

 

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")))

OfficeArtilces.com debuted on May 26, 2005.

MrExcel.com provides examples of Formulas, Functions and Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Formulas, Functions and Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Access®, Excel®, FrontPage®, Outlook®, PowerPoint®, Word® are registered trademarks of the Microsoft Corporation.
MrExcel® TM is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2014 by MrExcel Consulting | All rights reserved

privacy policy