In this article, we will look at how we can use an indirect sheet name in Google Sheets. This helps to refer to multiple sheets, all the while keeping the base formula secure.
Let’s get started.
How to Use Indirect Sheet Name in Google Sheets
For our first example we work with a single spreadsheet that contains the following spreadsheets:
Let’s bring our focus to the Main worksheet. Our task is to extract the name of an employee of a given workplace (Sheet Name) and a specific Cell Number.
The function we are going to be using to indirectly refer to the sheet name is, you’ve guessed it, the INDIRECT function.
The INDIRECT function syntax:
To know more about how the INDIRECT function operates, please see our Dynamic Cell Reference in Google Sheets article.
Back to our example, let’s say we want to know the name of the employee who is in Office 2 and placed in cell B5. For that we input the data:
- Sheet Name: Office 2
- Cell Number: B5
Now on to our formula. We know that the format to pull data from another worksheet is:
We will incorporate this idea within INDIRECT, taking the name and cell references from Sheet Name and Cell Number.
Remembering that the INDIRECT function only takes the value as a string, our values in cell D3 and D6 points to Sheet Name and Cell Number respectively. The exclamation mark (!) is inputted as a string (within quotation marks(“”)) and is concatenated with the cell references with an ampersand (&).
And that is it! This is how you indirectly use sheet names in Google Sheets. But that also begs the question:
Why do we need to use indirect sheet names in Google Sheets?
More often than not, we seek to protect our cells from outside interference. Especially the cells that contain important formulas. In such cases, functions like INDIRECT come in handy to indirectly refer to cells and partial formulas as a string to get the job done.
Modifying our Main worksheet (Optional)
Let’s bring some optional changes to make our Main worksheet slightly more dynamic. The change we apply is a drop-down list for the Sheet Name.
Our new worksheet in action:
You can add other modifications to your worksheet as needed, the INDIRECT function will behave accordingly.
We hope that the method we have shown of how to use an indirect sheet name in Google Sheets comes in handy for your daily tasks. INDIRECT is a powerful function that allows its users to overcome many of the limitations of the other base functions found in Google Sheets, especially in terms of security.
Please feel free to leave any queries or advice you might have for us in the comments section below.
- Pull Data From Another Sheet Based on Criteria in Google Sheets (3 Ways)
- Reference Another Workbook in Google Sheets (Step-by-Step)
- Cell Reference From String in Google Sheets
- Reference Another Sheet in Google Sheets (4 Easy Ways)
- How to Query Cell Reference in Google Sheets
- Indirect Range in Google Sheets (3 Easy Ways)