theReactor

July 28th, 2010

Excelling with Excel #4 – Indirect References

By Todd | Comments (2)
Hi guys. It's Greg the orange cat again with a (very short) Microsoft Excel tip.

Tip#4: Indirect References

Much of Excel's usefulness comes from an ability to reference cells in formulas.  In this case, the cells themselves function as variables. Indirect referencing allows you to go one step further, allowing you to put a variable within a variable.

Examples

Imagine you have a HUGE Excel workbook with many sheets (1 summary sheet, ~50 data sheets) The summary sheet references data from the many data sheets.

How the summary sheet works

In this example, indirect functions are employed to reference data from (cells within) different data sheets. For instance: k Cell C3 on the summary sheet references the value of cell K27 on sheet 3. k An indirect function pulls together values of the following cells: C2 ("3", to refer to sheet "3") C1 ("27", to refer to row "27") B3 ("K", to refer to column "K")

Another (less colorful) example of indirect reference use:

http://www.excelfunctions.net/Excel-Indirect-Function.html

Summary

Indirect references allow for dynamic cell referencing, which is surprisingly powerful.  Expertise with indirect references can dramatically reduce the amount of time required to use / create an otherwise very complex Excel workbook.

If you have any questions about the content of this post, please leave a comment or contact my young professional ChE friend, Todd Krueger.

- Greg the Orange Cat
Enhanced by Zemanta

2 Responses to “Excelling with Excel #4 – Indirect References”

  1. Brian Daly says:

    Also I'd recommend mentioning the use of DGET and it's uses and limitations as well. Oh yeah and don't forget every engineer's Excel friend HLOOKUP and VLOOKUP.

  2. Neal says:

    using indirect: get error: too few arguments

    =ABS(FORECAST(INDIRECT("D2","D2:D230","D231:D459")))

Leave a Reply