HomeGoogle DocsSpreadsheetA Drop-down Menu in Google Sheets to View Content from Any Sheets

A Drop-down Menu in Google Sheets to View Content from Any Sheets

Published on

We can view content from any sheet without leaving the current sheet using a drop-down menu in Google Sheets.

We can do it with the help of a few functions and data validation.

Let’s create a drop-down menu first to view the content from any Sheets easily.

Before that, first, let me clarify a few things about this Google Spreadsheet tab management tutorial.

Yep! I consider this a type of tab management in Google Spreadsheets.

In this tutorial, I am not talking about how to view multiple google sheets side by side.

Also, I am not talking about consolidating data from multiple sheets into one single sheet, either.

Here I will tell you how to view or access the full content of any sheets in your current sheet.

For example, you have a Google Spreadsheet file with 15+ sheets.

You may find it a litter uncomfortable to navigate between sheets to view the content.

I have a solution to this. We can create a master sheet with only a drop-down menu as below and pull any sheets contents on this sheet to instantly view it.

So, no need for you to switch between sheet tabs to view their content. 

Anyone can easily create such a master sheet.

But if you do not like this method, you can always consider creating a table of contents with clickable links within Google Sheets.

Example to Drop-down Menu to View Content from Multiple Sheets in Google Sheets

drop-down menu with sheet names to view content of other sheets

Please see the above image.

You can see that the name of the current sheet is “Master.”

There is a drop-down menu in cell A2 in this sheet which contains all the sheet names in that workbook (file).

As per the above example, the active sheet name in the drop-down menu is “Company B.”

You can see some data on the right-hand side of the drop-down menu.

It’s the data pulled from the sheet “Company B.”

When you select any other sheet name from the drop-down, you will get the data from that sheet.

I am using two functions in their simplest form in this Google Sheets tutorial.

They are Vlookup and Indirect. Other than these functions, you should know the use of Named Ranges in Google Sheets.

Steps: Drop-down Menu in Google Sheets to View Content from Any Sheets

I have explained the steps under three sub-titles below.

Sample Data Preparation and Named Ranges

We have seven different sheets here.

For our example purpose, I’ve just limited the number of sheets to seven, but you can have as many sheets as you wish.

See the below image. The first one is the “Master” sheet, and in that one, we will create our drop-down menu.

The second Sheet is named “Sheet Names,” a helper tab for creating the drop-down menu.

It contains some data for the drop-down list.

sheet tabs for drop-down list

The other five sheets from “Company A” to “Company E” are the sheets of which the content we can view from our “Master” Sheet.

Let us begin with the content in the “Sheet Names” tab.

The “Sheet Names” sheet contains details to create the drop-down menu in the “Master” Sheet. See the content of it below.

content for drop-down list menu

Column “A” includes all the sheet names in the file except the first two sheets, which we use to create the drop-down.

And the second column contains the name of the “named ranges” in each sheet.

I’ve already mentioned that there are five sheets from “Company A” to “Company E.” In each sheet, I have given the range name “Company1” to “Company5,” respectively.

I mean, the name of the data range in “Company A” is “Company1” and “Company B” is “Company2,” and so on.

sample data to pull when selecting the sheet name from the drop-down

The above is the sample data in “Company A,” and I’ve assigned the name to this data range as “Company1” from the menu Data > Named Ranges.

There are similar data in other sheets. In each sheet, assign range names.

Then in cell “A2” in the “Master” Sheet, set the data validation as below.

Create Drop-down Menu to Populate Data up on Selection

Go to Data > Data validation and apply the settings as per the screenshot below.

data validation for drop-down

Now your drop-down menu is ready.

The Formula Part

There are a few more steps left. In Cell B2 apply the below Vlookup formula.

=vlookup(A2,'Sheet Names'!A2:B6,2,0)

It is to pull the range name from “Sheet Names” corresponding to the drop-down menu selection.

Then to the final step. In cell C3, use the below Google Sheets popular INDIRECT function, and voila!

=indirect(A2&B2)

You have finished all the steps!

Now select the sheet name from the drop-down menu to see the content changes on the right. Any doubt, please feel free to comment.

Resources

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

37 COMMENTS

  1. I have followed your instructions, but I’m encountering an error: “Function INDIRECT parameter 1 value is ‘Week 1 Nov 15-16Company5’. It is not a valid cell/range reference.”

    It works well until I attempt to change names in the “Sheet Names” tab.

    • Hi Sebastian,

      In the “Sheet Names” tab, replace “Week 1 Nov 15-16” with “Week 1 Nov 15-16!”.

      Note the leading exclamation mark. Once you’ve made this change, select the new name from the drop-down. That should resolve the issue.

LEAVE A REPLY

Please enter your comment!
Please enter your name here