Excelling with Excel #4 – Indirect References

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