Select From Drop Down And Pull Data From Different Sheet in Microsoft Excel

In this article, we will learn How to use the ... function in Excel.

What is a drop down list in Data Validation?

Data Validation is an Excel 2016 feature whose purpose is to restrict what users can input into a cell. It is essential to create drop-down lists or combo boxes that contain predefined options that limit user errors and allow for more consistent data entry.

Let's say you are a teacher. You have prepared attendance of students in a workbook. Each month’s attendance is in a different sheet of that month’s name.

In a master sheet, you want to put a VLOOKUP to check if that student was present or absent that month. It would be simple if your data was on the same sheet, but it is not. It is in different sheets. But it doesn’t mean we can’t pull data from another sheet in excel. We can and will.

For the month, you have created a drop down in cell C1. It contains a list of months. Now you want to show absent or present based on the selected month in Cell C1. Let's just see the generic formula first.

Generic Formula for VLOOKUP from Multiple Sheets:

For this example, I have my attendance in “Jan”, “Feb”, and “Mar” sheets in the same range A2:C11.

Now I have prepared a master sheet.

In cell C4, put this formula and drag it down.

Now, whenever you change the month’s name in cell C1, excel will pull value from another sheet (from that month's sheet, if it exists).

Explanation

We used  Excel Indirect function to fetch value from another sheet.

INDIRECT changes the text into reference.We used INDIRECT for referencing other sheets in excel.

For example, if you write INDIRECT(“sheet2:A2”) in a1 on sheet1. It will pull the value from sheet2!A2 in sheet1:A1. If you write =VLOOKUP(“abc”,INDIRECT(“sheet2!A2:B100”),2,0)  any sheet,
VLOOKUP will look for “abc” in range A2:B100 on sheet2.

INDIRECT(""&$C$1&"!B2:C11") : Here we want sheet name to change, that is why we have written it like this. If Cell C1 contains “Jan”, it will translate to INDIRECT(“Jan!B2:C11”) which will then translate to Jan!B2:C11 for VLOOKUP table array. If C1 has Feb it will translate to INDIRECT(“Feb!B2:C11”) and so on.

Afterwards, VLOOKUP did his job.

VLOOKUP(B4,INDIRECT(""&$C$1&"!B2:C11"),2,0): now since Indirect gave the table array, VLOOKUP simply pulls data from that range easily.

In this article, we are going to learn how to edit dropdown list in excel. To do this, we will use the Name manager and Data Validation. 

Let’s understand this by taking an example.

We have some lists here as shown below.

First, we need to create a dropdown list for the Main Category and then we will proceed to Sub_Category.

Select Formula>Name Manager in Defined names OR use shortcut Ctrl + F3 to open Name manager where we will keep lists of the array with their names so that we can call them by there name whenever required.

Click New to create. Here Name will be Month and in Refers to option enter the list under Month as shown below.

The same we will do for Week_Days and it will show like

Then Click Data validation under Data bar. Choose list option is Allow and select the cells for main category names which in this case is at B2 and C2 cell “Month” and “Week_Days”

As we can see a drop down list is created which asks the user to choose from the given option.

Now select the cell under Sub_Category and just write the formula in Data validation and click OK.

Formula:

The result is displayed like this

If I don’t want Month and Week_Days. Instead, I want Fruits_Name and vegetables_Name. We just need to edit our Name Manager list.

Press Ctrl + F3 to open Name manager and delete the already inserted list and add new lists i.e. Fruits_Name and Vegetables_Name.

Now select the cell under Sub_category as shown in snapshot below.

Instead of Month and Week_Days cell, we will use Fruits_Name and Vegetables_Name in Data Validation and click OK

As you can see the new list is added here.

This is the way we can edit in the dropdown list and change the list selection.

Hope this article about how to  Select From Drop Down And Pull Data From Different Sheets in Microsoft Excel is explanatory. Find more articles on drop down lists and related Excel tools here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.

Related Articles:

Data Validation in Excel : Restricts users to input value manually using this tool in Excel.

Way to use Vlookup function in Data Validation : allow values from the vlookup table in Excel.

Restrict Dates using Data Validation : allow dates in the cell which lays within Excel date format in Excel.

How to give the error messages in Data Validation : customize your data validation cell with error messages in Excel.

Create Drop Down Lists in Excel using Data Validation : restricts users to input data manually in Excel.

Popular Articles :

How to use the IF Function in Excel : The IF statement in Excel checks the condition and returns a specific value if the condition is TRUE or returns another specific value if FALSE.

How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

How to Use SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.

How to use the COUNTIF Function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.