How Do You Dynamically Reference Another Sheet in Google Sheets?
Perhaps you're working on a complex sheet that contains many sheets within one worksheet. You might be wondering how to dynamically reference another sheet in Google Sheets so that you can pull all of the data into one screen. The good news is, there is a great solution for this!
So, how do you dynamically reference another sheet in Google Sheets? The best option is to use the INDIRECT formula. This is great if you have multiple sheets that are identical but have different data. You can pull all of this information dynamically into one sheet using the INDIRECT function.
Continue reading to learn how to use this formula and examples of why you might want to use a formula like this! Another great way to pull data from another sheet is by using the INDEX MATCH formula. Learn how to use INDEX MATCH here!
How to Use the INDIRECT Formula
Similar to VLOOKUP, the INDIRECT formula allows you to find data from one sheet and dynamically displaying in another. However, there is one very important catch...
In order for INDIRECT to work, your spreadsheets must be set up the exact same way.
Therefore, this formula applies best to spreadsheets where you are collection the same data, only in different sheets. This might be a sales spreadsheet where you have a different sheet for every month of the year and you're recording the same data every month. In the case, you could use INDIRECT to pull this monthly data into one spreadsheet for your yearly summary.
For the purposes of this article, let's use a bakery & ice cream shop as an example. Let's say that we are keeping a revenue and profit spreadsheet for our business. We have a tab/sheet for each month of the year, and we want to record the year-to-date revenue and profit on a single sheet.
We have each monthly tab set up the exact same way, like this:
At the top of the sheet, in yellow and green, I am using a simple SUM of columns C and D so that we can get the totals of each column.
We will use the INDIRECT formula to dynamically reference the monthly sheets and bring these two totals in for each month.
First, let's break down the formula:
=INDIRECT(reference&"string")The reference portion of the formula is pointing to the reference point on the current sheet that matches the sheet names. In the example of our our spreadsheet, we will list out the months which matches the sheet names.
The "string" portion of the formula indicates the cell that all sheets have in common where the desired data can be found.
The ampersand (&) is functioning to concatenate the reference and string.
In other words, the indirect formula is working the same as pointing directly to the other sheet. For example,
=INDIRECT(A2&"!G1")will give you the same result as
=January!G1That being said, you might ask, why use INDIRECT when you can simply reference the cell? The answer is simple, instead of having to go to every single sheet and referencing the cell, you can write one INDIRECT formula and copy it down so it will dynamically apply to every sheet.
Let's see the real-life example. Once you have the first formula in, you can simply grab the square on the lower right corner of the cell and drag it down the column for the data to be populated. Then repeat for the profit column.
How to SUM the INDIRECT Formula
You might have noticed in the formula above that the rows which do not have sheet names yet are returning an REF! error which means the sum formula (in bright green) does not work.
No worries, we can fix this too! It's very easy. Simply append your current INDIRECT formula with an IFERROR formula, which looks like this:
=IFERROR("no error","error")What this is saying is, if there is no error, return the first value and if there is an error, return the second value. In our case, if there is an error, we want it to simply return 0 as a number so that our SUM formula will work correctly.
Your new formula should look like this. You can again copy the formula down the column and your sum will work correctly.
=IFERROR(INDIRECT(A2&"!G1"),0)
You can also use this INDIRECT formula on Microsoft Excel. In this case, excel might be showing you little green errors in your cell (even though there is no actual error). Here are instructions for how to remove these errors!
Why Would I use INDIRECT When I Could Just Do a VLOOKUP?
I love using VLOOKUP as it saves so much time and I can quickly pull in data from other sheets. However, it does have its limitations. For example, you have to have the same reference point on every single sheet. If we were to use VLOOKUP on our example spreadsheet, we would not be able to simply bring in the revenue without doing a lot of alterations to the sheet.
VLOOKUP has its place and in some scenarios is a better option than INDIRECT, but when you are simply trying to pull in a simple value, this is a much better option!
