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
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
becomes the same as if it were written:
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
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.