Indirect Sheet Name in Google Sheets (Easy Steps)

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:

Main Worksheet for indirect sheet name in google sheets

Main Worksheet

Office 1 Worksheet

Office 1 Worksheet

Office 2 Worksheet

Office 2 Worksheet

Office 3 Worksheet

Office 3 Worksheet

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:

INDIRECT(cell_reference_as_string, [is_A1_notation])

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

inputting sheet name and cell number

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.


using INDIRECT function for indirect sheet name in Google Sheets

Formula Breakdown:

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:

using INDIRECT for indirect sheet name in google sheets animated

You can add other modifications to your worksheet as needed, the INDIRECT function will behave accordingly.

Read More: Google Sheets: Use Cell Value in a Formula (2 Ways)

Final Words

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.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply