Generic Formula for VLOOKUP from Multiple Sheets: =VLOOKUP( lookupValue ,INDIRECT(""& cell that contains name of month &"! range "), col_index_no ,0)įor this example, I have my attendance in “Jan”, “Feb”, and “Mar” sheets in the same range A2:C11. Let's just see the generic formula first. Now you want to show absent or present based on the selected month in Cell C1. We can and will.įor the month, you have created a drop down in cell C1. But it doesn’t mean we can’t pull data from another sheet in excel. It would be simple if your data was on the same sheet, but it is not. In a master sheet, you want to put a VLOOKUP to check if that student was present or absent that month. Each month’s attendance is in a different sheet of that month’s name. You have prepared attendance of students in a workbook. This is the way we can edit in the dropdown list and change the list selection. Instead of Month and Week_Days cell, we will use Fruits_Name and Vegetables_Name in Data Validation and click OKĪs you can see the new list is added here.
Now select the cell under Sub_category as shown in snapshot below. Press Ctrl + F3 to open Name manager and delete the already inserted list and add new lists i.e. We just need to edit our Name Manager list. Instead, I want Fruits_Name and vegetables_Name. Now select the cell under Sub_Category and just write the formula in Data validation and click OK. 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”Īs we can see a drop down list is created which asks the user to choose from the given option. Then Click Data validation under Data bar.
The same we will do for Week_Days and it will show likeĬlick Close and now select the cell where we need to add dropdown list. Here Name will be Month and in Refers to option enter the list under Month as shown below. 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.Ĭlick New to create. Hereįirst, we need to create a dropdown list for the Main Category and then we will proceed to Sub_Category.
Let's understand how to use the function using an example. If we select the fruits from the first list, then preceding list will show fruit names, not the weekday names.Īll of these might be confusing to understand. For example, if we select the week from the first list, now other options, will be left as Monday, Tuesday …, Sunday. Multiple dropdown list means when one list is connected to another list. This is a data validation tool which allow to restrict users. So we restrict other users to allow only from the mentioned values in the list. In this article, we will learn How To Create Multiple Dropdown List Without Repetition Using Named Ranges in Excel.Īs an admin of Excel sheet, you don't want anyone editing the sheet, anywhere.