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

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.

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.

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.

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

37 COMMENTS

sebastian November 15, 2023 At 12:49 am

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.

- Prashanth KV November 15, 2023 At 8:20 am
  
  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.
  
Jeffrey Smith March 14, 2022 At 6:17 am

I have followed every step of this without issue.

However, when I use the Master Tab and select the desired item on the drop-down, it is only pulling up the correct named range for one of them.

When I select a different name on the drop-down, it populates the wrong data.

- Prashanth March 16, 2022 At 2:12 pm
  
  Hi, Jeffrey Smith,
  
  Please check the named ranges.
  
  Also, you can share the URL here, which I won’t publish for me to check.
  
  - Jeffrey Smith March 17, 2022 At 4:02 pm
    
    I have redone the named ranges a few times, and it does the same thing. Hoping you can find my error.
    — sample sheet URL removed by admin —
    
    - Prashanth March 18, 2022 At 10:50 am
      
      Hi, Jeffrey Smith,
      
      Sorry! The issue was with my formula.
      
      I haven’t specified the is_sorted optional argument in my formula.
      
      VLOOKUP(search_key, range, index, [is_sorted])
      
      We must specify it to FALSE or 0 (zero) and I have edited my post.
      
      So use the below Vlookup formula
      
      =VLOOKUP(A1,'Sheet Names'!A1:B14,2,0)
      
      You can optionally use IFNA with INDIRECT since your datasets in each tab contain #N/A!
      
      =ifna(INDIRECT(A1&B1))
      
Luke October 13, 2021 At 2:04 pm

Hi,

Can you please make a video of this?

I keep getting an error with the indirect function.

I want to be able to edit or add information on the master sheet instead of having to open each sheet when I want to add data.

- Prashanth October 14, 2021 At 2:14 pm
  
  Hi, Luke,
  
  If you share the sheet (sample), I may be able to sort out the error.
  
  The formula won’t permit you to add information to the master sheet. That’s a drawback.
  
jon October 14, 2019 At 7:46 am

Shared again.

Thank you.

- Prashanth October 14, 2019 At 1:34 pm
  
  Hi, Jon,
  
  The issue is due to mixed content in the column. For example, column 6 (TRAIN column) contains numbers in some tabs, text in some other tabs. The query will treat the majority values in such columns as the column type. So you will see the wrong outputs in Query filtering.
  
  You can solve this issue by converting the value type which I have detailed here.
  
  How to Solve the Mixed Data Type Issue in Query in Google Sheets.
  
  Best,
  
jon October 11, 2019 At 6:37 am

Hi,

Already shared in view mode.

Also if I delete data in column train for the month of June to Dec. Data in the Search_Train tab will go blank.

Thank you.

- Prashanth October 11, 2019 At 11:04 am
  
  Hi, Jon,
  
  I’ve accidentally deleted that mail. Share again, please.
  
  Best
  
jon October 10, 2019 At 2:34 pm

Hi,

I already made one for my project.

I encountered an error when I entered a date in the month of June, the “search_Train” tab will go blank.
I cannot figure out the error.

Could you please look into the error.

I appreciate your help and thank you very much.

Jon

- Prashanth October 10, 2019 At 6:31 pm
  
  Hi, Jon,
  
  Happy to help you if you can share your Sheet in View mode.
  
  Best,
  
jon October 9, 2019 At 10:10 am

Thank you very much, Mr. Prashanth.

jon September 19, 2019 At 12:14 pm

Hi Prashanth,

Can I search like, I have 31 sheets as in for 31 working days, (sheet named day1 up to day31)

On sheet day1, data is Name / Transactions / Amount.

How can I search Mr. A’s transactions from Day 1 to Day 31?

Thank you.

- Prashanth September 19, 2019 At 1:01 pm
  
  Hi, Jon,
  
  I couldn’t access your Sheet! Sent a request.
  
- Prashanth September 20, 2019 At 1:20 pm
  
  Hi, Jon,
  
  Name the range (Data menu > Named ranges) in each tab like Day_1, Day_2 …
  
  Then use that in Query.
  
  =Query(ArrayFormula({Day_1;Day_2}),"Select * where Col6='"&A2&"'",1)
  
  You can find this formula in cell B2 in the Master tab of this Sheet. I have only added two named ranges.
  
  You can expand it like;
  
  {Day_1;Day_2,Day_3;Day_4}
  
  If you are adding tabs each day, then you can include that future tabs too in the formula. In that case, you may find the following tutorial useful.
  
  How to Include Future Sheets in Formulas in Sheets
  
  Best,
  
Ian September 9, 2019 At 9:03 pm

Attached is a link to a mockup of the sheet I am working on! Thank you for your help!

— Link removed after copying —

- Prashanth September 10, 2019 At 10:19 am
  
  Hi, Ian,
  
  See my attempt on the Master tab.
  
  Practice Burden Calculator
  
Ian September 7, 2019 At 2:08 am

Hi Prashanth,

This is great info! I am trying to add data validation drop down lists to each of the “company” tabs and they don’t seem to transition well to the master sheet.

I also have multiple charts on each tab and it only seems to pull one charts from each of the “company” Tabs.

If you have any recommendations or wouldn’t mind taking a look at my sheet it would be greatly appreciated! Thanks.

- Prashanth September 7, 2019 At 10:57 am
  
  Hi, Ian,
  
  No issue! you can share (copy of) your Sheet with me. I just prefer a Sheet copy with some mockup data.
  
  Best,
  
Shawn August 25, 2019 At 4:51 pm

Hi,

Thank you for this! I’m trying to do the same thing for a grade book I use for my class (instead of pulling and viewing data, I’d like to edit and add data) but I can barely pull and view the data. I see N/A as soon as I do the Vlookup function. What do you recommend? Thanks.

Bowski March 7, 2019 At 7:28 pm

Hi Prasanth,

I tried to do this on the LibreOffice but when it comes to the indirect function it is showing #ref.
I tried to download your sample and opened on libre office and same error pops out. I’m not sure if there is any indirect function we can use on LibreOffice.

- Prashanth March 8, 2019 At 11:09 am
  
  Hi, Bowski,
  
  I am not familiar with LibreOffice. The Indirect function is different in Excel and Google Sheets too.
  
  Comparison: Indirect in Excel vs. Indirect in Google Sheets
  
  So there may or maynot be some changes in the use of this function in LibreOffice. Please check their support page HERE.
  
Ben T. Lador October 2, 2018 At 12:16 pm

Hello,

I got an error upon applying the “Indirect function” saying that my named range is not a valid cell/range reference, but I follow all the steps in the tutorial. Is there anything I have missed?

- Prashanth October 2, 2018 At 3:03 pm
  
  Hi, Ben T. Lador,
  
  I can’t say what’s the actual issue.
  
  Better please do check my example sheet. I have already shared that with a previous commenter. Please go through the comments and follow the demo sheet link.
  
Horse September 19, 2018 At 10:40 am

Hi, I’m a newbie to excel and google sheet, I got an interesting idea after reading your post:

We can just PULL and VIEW data from other sheets in the MASTER sheet,
but we can’t EDIT or ADD data in the MASTER sheet.
By expanding the range of every name range, I mean, like changing “CompanyA!A1:E10” to “CompanyA!A1:E999”, we get the blank cells of the original sheet in MASTER, if we can make changes in the MASTER sheet, that would be awesome.

Is this idea vagarious or there is some formula we can do this?

Thank you for your sharing!

- Prashanth September 19, 2018 At 11:36 am
  
  Hi,
  
  That’s doable.
  
  Please do the changes as follows.
  
  1. CHANGES IN THE TAB ‘Sheet Names’
  
  In the tab “Sheet Names” in cell B2 enter the below formula.
  
  ="A1:"&left(address(1,counta('Company A'!A1:1),4))
  
  In cell B3, copy the same formula but change the sheet name, i.e. ‘Company A’ to ‘Company B’
  
  Repeat this for other sheets. That means instead of named range, we are going to use cell reference.
  
  2. CHANGES IN THE TAB ‘Master’
  
  Replace the Indirect formula in Cell C2 with the below one.
  
  =query({indirect(A2&B2)},"Select * where Col1 is not null")
  
  Hope this helps.
  
  - Obed Gaytan November 12, 2020 At 2:12 am
    
    I attempted to do this on my sheet, but I couldn’t get it to work properly. It would be nice to be able to use this to pull information without having to create Named ranges for every sheet.
    
    Is it also possible to also edit information in the master sheet pulled from the other sheets?
    
    - Prashanth November 13, 2020 At 2:39 pm
      
      Hi, Obed Gaytan,
      
      We can pull information from other sheets without creating named ranges. If you share your sheet (just a demo sheet), I would try to help you out.
      Regarding your second question, we can’t edit the pulled data. If we edit, the formula would break.
      
Nicholas A Stockbridge September 18, 2018 At 6:35 am

I got it to start pulling but the sheet is only pulling Monday for Monday and Sunday
And pulling Sunday for Tuesday-Saturday. I dont get what is wrong :/ Ive double checked all names Ive retyped names.

- Prashanth September 18, 2018 At 10:22 am
  
  Hi, Nicholas,
  
  Can I have a view of your sheet?
  
Ben February 22, 2018 At 10:59 pm

The formulas contain a parcing error when I duplicate your efforts. Would you please share this with me? THanks!

- Ben February 22, 2018 At 11:08 pm
  
  Just Kidding, I found the reference error. Is there a way to make the formatting of the data range transfer from the source to the ‘master’ page?
  
  - Prashanth February 23, 2018 At 8:56 am
    
    Hi Ben,
    As committed please see the link.
    
    https://docs.google.com/spreadsheets/d/1ouEoAoFwwaNaNqpbQ1St8lYE8mNRTestHBoqJyYw-uk/edit?usp=sharing
    
    To get the edit access, please make a copy from File menu.
    
    Prashanth 🙂
    
- Prashanth February 22, 2018 At 11:33 pm
  
  Hi Ben,
  Let me check, If available I’ll post it soon.
  Thanks.