Use Excel INDIRECT to dynamically refer to worksheets

Sometimes you want to make a reference to certain worksheets dynamically using the Excel indirect function. For example if you have data in the same format split over multiple worksheets and you want to select data from different sheets dynamically.

In this case, you can use the INDIRECT() function, which is available in both Excel and Google Spreadsheets. I’ll show you how to use it by running through a quick example.

INDIRECT():Returns the reference specified by a text string. References are immediately evaluated to display their contents.

Using Indirect() to dynamically refer to a worksheet

In the following example we want to look at the revenues of a specific product by dynamically referring to the data sheet of each product.
We have a sheet Revenues in which we want to see the revenue for a specific product. Next, we have two sheets with sales and revenue data for two product: Product1 and Product2.

Normally you can only reference to one worksheet

We want sheet Revenues to show the revenues of one specific product and to be able to update the numbers dynamically when we change cell D1.
Unfortunately we can’t use a reference to D1 to specify the sheet we want in our VLOOKUP() function like this:

We can’t use a variable reference to D1 in our VLOOKUP()

For this to work, we have to use the INDIRECT() function. It allows us to use the value of cell D1 for creating a dynamic VLOOKUP referring to ranges on multiple sheets.

Using sheet names as variables with Indirect()

Now you can change cell D1 to “Product2″ and the revenue numbers will dynamically update and get the numbers from the second worksheet.

Indirect() in Excel

So to recap, you can use INDIRECT() to refer to multiple worksheets variably like this:
=INDIRECT("'"&D1&"'!"&"A3:D6")

Indirect() in Google Spreadsheets

You can use INDIRECT() in Google Spreadsheets in a similar way:
=INDIRECT(CONCATENATE(D1,"!A3:D6")