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

43

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:

=VLOOKUP(lookupValue,INDIRECT(“”&cell that contains name of month&”!range“),col_index_no,0)

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

44

Now I have prepared a master sheet.
45

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

=VLOOKUP(B4,INDIRECT(“”&$C$1&”!B2:C11″),2,0)

46

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.

So we learned how to reference different sheets using Indirect. How to pull data from different sheets in excel using VLOOKUP and INDIRECT functions. All these functions are available in all excel versions, including Excel 2016, Excel 2013 and Excel 2010.

Let me know your thoughts in the comments section.

Users are saying about us...

  1. hi

    I have created a quotes spread sheet for hire of equipment, I have 3 individual worksheets for 3 separate peoples quotes, which include date, time, customer, description, and booked date, the 3 main worksheets, are ‘currently on hire, off-hired, and sales, i want to create a drop down box at the end of each row of information, I need the dropdown box to remove the data from the individual worksheets and move it to the main worksheets chosen in the dropdown box.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube