Intro
Master the art of referencing other sheets in Google Sheets with these 5 indirect referencing techniques. Learn how to use formulas, named ranges, and more to link data across sheets without direct references. Discover the benefits of indirect referencing, including improved flexibility and reduced errors, and take your spreadsheet skills to the next level.
When working with multiple sheets in a spreadsheet, it's often necessary to reference data from one sheet to another. While direct references can be straightforward, there are situations where indirect references are more suitable or even necessary. Indirect references allow you to dynamically refer to a cell or range based on a cell value, making your spreadsheet more flexible and easier to maintain. Here are five ways to indirectly reference another sheet:
Using the INDIRECT Function
One of the most common methods for indirect referencing is by using the INDIRECT function. This function converts a text string into a cell reference, allowing you to dynamically change the cell or range being referenced.
Example:
=INDIRECT(A1 & "!B2")
In this example, the INDIRECT function looks at the value in cell A1 (which could be "Sheet1") and combines it with the text string "!B2" to create a reference to cell B2 on the sheet named in A1.
Indirect References with Sheet Names Containing Spaces
When your sheet names contain spaces, referencing them requires additional steps. You need to enclose the sheet name in single quotes.
Example:
=INDIRECT("'" & A1 & "'!B2")
Here, if A1 contains "My Sheet", the formula correctly references cell B2 on "My Sheet".
Using INDEX/MATCH for Dynamic Sheet References
The INDEX/MATCH function combination offers another powerful way to indirectly reference cells or ranges. This method is particularly useful when you need to refer to a range based on a lookup value.
Example:
=INDEX(MySheet!A:B, MATCH(A2, MySheet!A:A, 0), 2)
While this example uses a direct sheet reference, you can incorporate INDIRECT or other dynamic methods to indirectly specify the sheet.
Indirect References with Multiple Criteria
Sometimes, you need to reference a range based on multiple criteria, which can be achieved by combining functions like INDEX, MATCH, and INDIRECT.
Example:
=INDEX(INDIRECT("'" & A1 & "'!A:B"), MATCH(1, (INDIRECT("'" & A1 & "'!A:A")=A2) * (INDIRECT("'" & A1 & "'!B:B")=B2), 0), 2)
This formula references a range in a sheet named in cell A1, matching both values in A2 and B2 in columns A and B, respectively.
Using OFFSET for Dynamic Range References
The OFFSET function allows you to reference a range that starts at a specified number of rows and columns from a base cell.
Example:
=OFFSET(INDIRECT("'" & A1 & "'!A1"), 0, 1, 10, 1)
Here, the OFFSET function starts at a cell referenced by INDIRECT (based on the sheet name in A1), moves 0 rows and 1 column to the right, and then selects a range of 10 rows and 1 column.
Spreadsheet References Image Gallery
By mastering these methods, you can create more dynamic and flexible spreadsheets that indirectly reference other sheets, making your data management and analysis more efficient.