• 08/21/2018
    Referencing Data on a Specific Worksheet with =INDIRECT

    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.