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