We all know the struggle of navigating between multiple sheets in Google Sheets to view content. It can be quite cumbersome and time-consuming. But what if I told you there’s a simple solution to this problem? With just a drop-down menu, you can access the full content of any sheets without leaving your current sheet. In this tutorial, I’ll show you how to create a master sheet with a drop-down menu to instantly view the contents of any sheets.
Example: Drop-down Menu to View Content from Multiple Sheets in Google Sheets
Take a look at the image below. You’ll see the name of the current sheet is “Master.” In cell A2, there’s a drop-down menu containing all the sheet names in the workbook. The active sheet name in the drop-down menu is “Company B,” and you can see the data pulled from that sheet on the right-hand side. When you select another sheet name from the drop-down, the data will update accordingly. It’s that simple!
To achieve this, we’ll be using two functions: Vlookup and Indirect. Don’t worry, I’ll explain them in their simplest form. But before we dive into the steps, make sure you’re familiar with Named Ranges in Google Sheets. They’ll come in handy.
Steps: Drop-down Menu in Google Sheets to View Content from Any Sheets
I’ll break down the steps into three sub-titles for clarity.
Sample Data Preparation and Named Ranges
In this example, we have seven sheets. For simplicity, I’ve limited the number of sheets, but you can have as many as you need. The first sheet is the “Master” sheet, where we’ll create our drop-down menu. The second sheet is named “Sheet Names,” and it acts as a helper tab for creating the drop-down menu. It contains data for the drop-down list.
The other sheets, from “Company A” to “Company E,” are the sheets whose content we want to view from the “Master” sheet. In the “Sheet Names” tab, you’ll find the details to create the drop-down menu. Column A includes all the sheet names in the file, except the first two sheets used for creating the drop-down. Column B contains the named ranges in each sheet.
For example, the data range in “Company A” has the name “Company1,” “Company B” has “Company2,” and so on. Assign range names to each sheet, and in cell A2 of the “Master” sheet, set the data validation.
Create Drop-down Menu to Populate Data upon Selection
Now let’s create the drop-down menu. Go to Data > Data validation and apply the settings as shown in the screenshot below. Your drop-down menu is ready!
The Formula Part
We’re almost there! In cell B2, enter the following Vlookup formula: =vlookup(A2,'Sheet Names'!A2:B6,2,0). This formula pulls the range name from the “Sheet Names” sheet corresponding to the drop-down menu selection.
Finally, in cell C3, use the following Google Sheets popular INDIRECT function: =indirect(A2&B2). And voila! You’ve completed all the steps.
Now, when you select a sheet name from the drop-down menu, you’ll see the content change on the right. It’s that easy! If you have any doubts, feel free to comment.
Resources
- Google Sheets: How to Get an All Selection Option in a Drop-down
- Create a Drop-Down to Filter Data From Rows and Columns
- How to Combine Multiple Sheets in Importrange and Control Via Drop-Down
- Relative Reference in Drop-Down Menu in Google Sheets
- Consolidate Only the Last Row in Multiple Sheets in Google Sheets
- Formula to Combine Rows and Get Latest Values in Google Sheets
Now that you know how to create a drop-down menu in Google Sheets to easily view content from any sheets, you can save time and work more efficiently. Say goodbye to constantly switching between sheet tabs. Happy spreadsheeting!
