•  076 08/21/2018

The =INDIRECT function permits referencing information in a workbook using strings of text. This can be a very handy tool when you want to create dynamic references in formulas without changing the formulas themselves. It is also a terrific way to reference data on different worksheets.

To provide an example of how this function can be used, assume that you want to reference information on a different worksheet with tab name Sheet 1 in cell C6. On any other tab in the workbook you could input the following formula to retreive this information:

=INDIRECT("'Sheet 1'!C6")

But this is a very static approach. The =INDIRECT function can be made far more useful with dynamic references to text. For example, if all worksheets have the same name with the exception of a number (i.e. Sheet 1, Sheet 2, Sheet 3, etc.), then the worksheet name can be a cell reference:

=INDIRECT("'Sheet"&[REFERENCE TO CELL WITH SHEET NUMBER]&"'!C6")

Or as another alternative, you can input a function that will count up from one as you drag the formula down:

=INDIRECT("'"&"Sheet"&ROW(A1)&"'!C6")

Note: The ROW() function returns the row number. So =ROW(A1) returns the value 1. As you paste this formula down it will return a value that increases by 1 with each new row.

This is the formula used in the image below in the array D38:D42 with the [REFERENCE TO CELL WITH SHEET NUMBER] linking to B38:B42. (The only difference being that the sheet name is Sheet (1) vs Sheet 1.)

The biggest challenge is when all of the worksheets have unique names. For a solution to this please scroll to the bottom of this post. Related Link: List All Worksheets in a Workbook on One Tab.

•  075 07/01/2018

This video explains how to use SUMIF and the INDEX + MATCH function combination in Excel to dynamically reference columns in a financial model. This has been particularly useful to me when I am organizing a company's data by location or product category.

Excel Trick: This formula relies on one input in the INDEX function. If you look to the formula below you will notice that the "row_num" input in the INDEX function is the value zero (highlighted red for emphasis). With this input the INDEX function will return the entire column.

=SUMIF( ... ,INDEX(\$B\$13:\$G\$13,0,MATCH(\$C9,\$B\$13:\$G\$13,0)))

Note: Ellipsis used above to emphasis INDEX + MATCH portion of the formula. Please see video for the inputs that replace the ellipsis.

If you are unfamiliar with the SUMIF function I would recommend watching this video first. A tutorial on the INDEX + MATCH function combination can be found in the Excel for Models video series (subscriber content).

•  074 07/01/2018

This video provides a quick introduction to the =SUMIF() function in Microsoft Excel. The video also provides a quick demonstration of what is likely the most common use of =SUMIF() in a financial model: organizing monthly information into quarters and annual periods (skip to 1:15 for this example).

Models are:

A) really boring
B) pretty sweet
C) super important
D) somewhat easy
E) kind of hard
F) fun
G) all of the above

*Answers a, b, c, d, e, f and g are all correct. 