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.
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:
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.
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")
As always, let me know in the comments whether this approach worked or if you have any questions!
