Select from Drop-Down and Pull Data from Different Worksheet in Excel | VLOOKUP Drop-down

In this video I demonstrate how to extract data from a specific worksheet based on that worksheet name being selected in a drop-down list. To achieve this you need to understand the syntax used when referencing cells on another worksheet. You also need to know about the INDIRECT function.

Table of Contents:

00:00 - Introduction
00:20 - How to create a drop-down list of worksheet names
01:22 - Understanding the syntax for referring to cells on another worksheet
01:49 - Understanding the INDIRECT function
02:51 - How to refer to worksheet names that contain spaces
04:48 - Perform a VLOOKUP across worksheets
08:11 - Use the COLUMN function to automate the col_index_num
------------------------

[jmat]

This is very helpful. You are a gentleman, and a scholar. Your name is beast-mode as well.

[nassibaliouane]

Clear, precise, exactly what I needed. Thanks!

[yousefalazzaz]

Perfect and professional presentation. Thanks for the way you deliver msg 👍🏼

[wayneedmondson]

Hi Chester. Great use of INDIRECT and VLOOKUP! Thanks for demonstrating this technique. Thumbs up!! PS - here is another approach that would work: =SWITCH(B1, B1, INDIRECT("'"&B1&"'!B4:D10")) assuming you have O365.

[IvanCortinas_ES]

Drop-down list + INDIRECT = Great advance!
Unfortunately the INDIRECT function is already unknown for the general public!
Thank you for this great tutorial!

[meliwellness]

Thank you for the tutorial.. it was exactly what I was looking for.. I used the VLOOKUP function and did the trick I needed.. thx again

[chanelleloots]

Thank you!! This helped me so much. You are amazing!!

[alihanafiah]

thank you very much sir for your tutorial..

[teoxengineer]

Thank you for this indirect (amazing&magic function) tutorial.
I want to learn that how can we refer a closed or opened workbook/s values by usaging Indirect function?

[MyKyblik]

Hello, thank you for the video. It is very helpful! Would we do the same steps if we have data not in a different sheets but different excel files? Thank you so much!

[andrewspencer]

Hi Chester. Really usefull to know this, as it will help massively with what we are trying to achieve.. Not an excel whiz, but for some reason when I change my drop down option, the values don't change. However if I click on the cell with the formula and press enter it changes the value to the relevant drop down name. I've followed several times now, but to no avail. What have I missed? 🤦‍♂️

[komplitrandom]

This great, can you do a tutorial where the drop-down list can pull data from different WorkBooks.

[ashchick]

Hi Chester love this, what if I wanted to create another drop down list under your first one to bring in another row .? Say my first drop down is the sheet location but the second drop down relates to Monday, Tuesday, Wednesday etc and when I click on Monday I get data from column 1 but when I click Tuesday I get data from column 2 and so on until I get to Sunday ?

[farazishaque]

very helpful video. Can you help me with one of my task? I want to calculate salary for a sampling department. I want to know which stitcher has stitched how many pieces in a 15day period and ultimately what will be his salary during that period. I haven't been able to find any specific formula for that.

[TeachTech]

Hello. Can you please create a video focusing on creating a dropdown list that pulls out a formula, not a value, from another sheet. Thanks.

[karanjaiswal]

After pulling the data I want a summary sheet of each dropdown iteration, how to do that ?

[peace.love.creation]

Thank you so much. In the INDIRECT example, is there any possibility to use this formula or another for multiple rows and columns at the same time? You manually changed column and row in order to copy across in 4:33 min. How do you do it in a faster way for multiple columns and rows instead of changing the row manually? Please advise.

[scottbrown]

I’m working on a daily scheduling and report. Was wondering about a drop down box to pull up the week of a year. 1st week, 2nd week and so on. Would this be the most effective way? We fulfill orders, so many parts per day. Watching production weekly seems to be the most efficient. I’ve built a order sheet, it then loads a scheduling sheet and shop manager can then assign work to individual workers and the end of day is a production report and that is all captured on an end of day sheet, that is mailed out to management. Where I’am stalling is on how I can collate the weeks some how, say the account manager takes an order for a 150 units on the 12th week, he can add that in and when shop manager pulled up the 12th week from drop down it loads those 150 units and any other units added by other account managers? .. forgive my babbling, I’ve never been trained on Excel and am learning as I go.

[Journeymane]

Hello sir, how can we schedule one item per day data entry from one excel file to another blank excel file? The source excel file has single column entries only, in Google excel sheet.

[joshuarussell]

i have data on multiple sheets, i want a drop down list that pulls from these sheets according to the criteria i select from the drop down. in this example your drop down is the sheet names, i want my drop down to be able to pull off data within the sheet. like "advertising" in your example. thoughts?