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

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

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.

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

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.

  1. Vlookup in Google Sheets

2. Indirect function in Google Sheets

3. Named Ranges in Google Sheets

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.

sheet tabs for 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.

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 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”.

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

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.

data validation for drop-down

Now your drop-down menu is ready. There are few more steps left. In cell B2 apply the below Vlookup formula.

=vlookup(A2,‘Sheet Names’!A2:B6,2)

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!

=indirect(A2&B2)

Conclusion

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!

LEAVE A REPLY

Please enter your comment!
Please enter your name here