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:
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
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() in Google Spreadsheets
You can use
INDIRECT() in Google Spreadsheets in a similar way:
As always, let me know in the comments whether this approach worked or if you have any questions!