In this tutorial I will explain the secret of making a drop-down menu in Google Sheets, that can help you to view any sheets’ content without leaving the current sheet. Seems absurd, right? But we can do it with the help of three functions. We can create a drop-down menu in Google Sheets to view content from any sheets with ease. Before that first let me clarify 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. Here I will tell you how to view or access full content of any sheets in your current sheet.
For example, you have a Google Spreadsheet file with 15+ sheets. You certainly find it very tough 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.
No need to switch between sheets to view its content. Anyone can easily create such a sheet. But if you do not like this method, you can always consider creating a table of content with clickable links that within Google Sheets.
The Below Image Itself will Explain You What We Meant by a Drop-down Menu in Google Sheets to Manage or View Multiple Sheets’ Content.
See the above image. You can see the active sheet’s name as “Master”. There is a drop down menu on this sheet with different sheet names. On this image, the active sheet name in the drop down menu is “Company B”. On the right side of the drop-down menu, you can see some data. It’s the data pulled from the sheet “Company B”. When you select any other sheet name from the drop-down, accordingly the data will change.
Requisites to Create the Drop-Down Menu
I am using below three functions in this unique Google Spreadsheet tutorial. If you are not familiar using them, I request you to go through our relevant tutorials first.
Steps to Create a Drop-down Menu in Google Sheets to View Content from Any Sheets
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 “Master” sheet and that is the one where we use our drop-down menu. The second Sheet is “Sheet Names” That also we use to create the drop down menu. It contain some data for the drop-down list.
Other 5 sheets from “Company A” to “Company E” are the sheets of which the content we can view from our “Master” Sheet. So let us begin with the content in “Sheet Names” tab.
The “Sheet Names” sheet contains details to create the drop-down menu in “Master” Sheet. See the content 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 told above, there are five sheets from “Company A” to “Company E” with some kind of data. In each sheet you should name data ranges as below. I have given range name as “Company1” to “Company5” respectively. I mean the name of the data range in “Company A” is “Company1” and “Company C” is “Company3”.
The above is the sample data in sheet “Company A” and I’ve assigned name to this data range as “Company1”. See the image carefully. There are similar data in other sheets. You can just copy paste the same data in all other four sheets. Then made only changes to “cheque_amt_in_usd” column in each sheets. So that we can later identify the content from the drop down result. Similarly in each sheet, assign range names. If you have done all this come back to “Master” sheet. There in cell “A2” set the data validation as below.
Now your drop-down menu is ready. There are few more steps left. In cell B2 apply the below Vlookup formula.
This is to pull range name from “Sheet Names” corresponding to the drop-down menu selection.
Then the final step. In cell C3 use the below Google Sheets popular INDIRECT function and voila!
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. Also I can share the working spreadsheet up on request on comments. Enjoy!