• 076 08/21/2018

    Note: Template available for download.

    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.

    Download Template

    Reference Data on a Specific Excel Tab or Worksheet.

     

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

    Download template in videoLINK

     

 



  • 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.