Excel Magic Trick 913: Select From Drop Down and Pull Data From Different Sheet
Вставка
- Опубліковано 3 жов 2024
- Download Excel Start File: people.highlin...
Download workbook: people.highline...
See how to:
1. Select From Drop Down and Pull Data From Different Sheets using Data Validation List, the INDIRECT Function, and the join symbol ampersand &
2. Learn about syntax for Sheet References
3. INDIRECT Function
4. Data Validation Drop Down List
Woo Hoo! I am glad that Excel is fun for you! You are welcome for the trick!
Just a guy having fun with Excel!
No matter about the skill! As long as we are having fun with Excel and getting our jobs done efficiently and effectively, it is all good and we all love Excel!
--excelsifun (just a guy having fun with Excel)
You have an unparalleled way of teaching simple to complex excel functions. Have always enjoyed your tutorials. They have helped me ...a lot! Thanks you.
I’ve been trying to get a workbook to do this at work for over a week now and your video has finally cracked my code. Thanks 😁
I am glad that this helps. For much more about Data Validation Drop Down Lists, see:
Excel Magic Trick 548: Data Validation Drop-Down List In A Cell Same Sheet or Different Sheet
Excel Magic Trick 549: Dynamic Data Validation List Drop-Down OFFSET or Table Feature?
Excel Magic Trick 550: Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Down
Super trick! The INDIRECT and Data Validation work perfectly together.
Thanks for every trick.
I have been working on a complicated workbook for some time and this is EXACTLY the tutorial I needed. Thanks for great work and providing clear instructions!
An older video that STILL works GREAT! Thanks for the help! Your clear, concise, and to the point instructions allowed me to update my spreadsheet quickly and easily!
You are welcome!
exceptional that someone takes his time to help others. Simple and well instructed
I am glad that the videos help!
I have learned a lot just by watching your videos so, just wanted to say thanks for all your great work.. thank u so muchhhh!!!! :))))
You are welcome soooo much!
Thank you Thank you Thank you Thank you!!! Your lesson is awesome. I was able to follow it and successfully make an entire sheet that changes by one drop down menu...
That's such a great trick mike. I've got some complex workbooks that I have had to grab data manually for in the past to get data onto a summary page. This will help to no end. Excel sure is fun!!
Really I have been learing a lot , you are like a good teacher who explained us as we are like a child.Thank you very much Mr.Dear.
Cool! I am glad that you like it!
Cool! I am glad that the video helped!!
No dictionary where I can pick a right word from to praise your unbelievable tutorial
Yes but they way you put the words together in this comment are even better than picking the right word from the dictionary : )
Very clever! This method works like VLOOKUP in that you are pulling data, but you're doing it from multiple sheets instead of a table of data. I would take the list of sheet names table further and format it into a table. If you wanted to add names to that list you could and it would automatically show up in your dropdown list. Obviously, you would have to change the reference for data validation. Great video!
You are welcome!
You are welcome!
You are welcome!
I do not understand English, but your video has help me well thank you, :p I use google translate to ask you thank you ;)
You are welcome!
You have to change the settings on your computer in control panel in Region and Language.
First off thank you for all your advice and effort, its greatly appreciated. I've read through this section but have not quite found / understood what I need for my "issue".
I am trying to create a pay sheet for my staff. We have various staff on different pay grades. We have a separate excel sheet with all our staff names, numbers, paygrade, hourly overtime rate and various other details in rows. e.g. A2=name, B2=number, B3=rate, etc.
I would like to, using a drop down list, select the staff member by name and then in the subsequent columns have the required information (number, rate, overtime rate) inserted automatically in the subsequent columns in the pay sheet. e.g. in C11 we select the staff member by name. Once selected we require C11, C12, C13 and C14 in that sheet to be filled with the corresponding information of that staff member.
The rest of the paysheet is fine, it calculates the remuneration with no problems.
Could you also include the formula for getting the data from a separately saved workbook (we have the staff list stored in the cloud).
Thanks in advance!
Thanks. I actually posted there and haven't heard... but this morning I found the solution on Ozgrid using a relatively complicated SUMPRODUCT formula.
You are welcome!
You are welcome!
Cool! Ozgrid is a really great site.
You just saved me a lot of work. Thanks Mike
You are a genius and inspiring.
Thank you, definitely deserve a subscribe. The detail of your explanation is just amazing. Thank you.
You are welcome!!!! Love your batman icon : )
Your videos are SOOO good. We appreciate them
Simply the best excel tutorials on the net - Thanks :-)
very easy to understand, figured it out in just a minute.
Bro....you are doing a great favour to mankind....cheers... :) :)
This is my go-to-stop for excel advice. Thank you for the videos!
You are welcome! : )
Thank you. This is actually saves me reducing alot of Macro function.
Great idea!!!
george from greece sent to you another big thank you!!!
love your "you gotta be kidding; me" and of course your famous " you're not gonna believe this" -))))
Thanks for noticing!
This is brilliant, exactly what I having been looking for ! Thank you so much
Great trick. in this example the extracted data on all the sheets are in same cells. Need to do same select sheet name form drop down list, and extract same data but may be on different cells
Dude, for real, Excellent work! You cover all the (mystery) topics, explain it perfectly, structure it so it flows flawless and before this gets to creepy, I'm calling 'No Homo." In addition, if you had a 'donate' button, this would be one of the few that I would donate too! Just sayin' Oh Yeah, Thanks alot too!
Cool! That is why I post: to make the world a better place! If you want to donate, then check it out here:
ua-cam.com/users/ExcelIsFunabout
(Donate Paypal Button)
Thanks Mike. You should teach them how to pull the sheet names automatically next time. Bob Umlas has this trick in his book "Excel outside the box". I'm sure you have a copy :).
Excellent. Couldnt find anything elsewhere!!!
"Jazaka Allaho Khairan "Thank you very much ...you are a good man...How can I thank you
Your video really really help me a lot. Thank you very much
You are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )
That's awesome! Crystal clear.
I could do this for workbook names also, couldn't I?
This is excellent. It's going to help me loads! Thanks very much.
I'm trying to work out how I can use your video trick to pull up a how row of data on a different sheet.
For example, if I make my "Sheet 5" with a drop down list that has "room 1", "room 2", "room 3" and "room 4" in Cell A1, how can I make it pull the data from "sheet 1" Rows B1:J1 down to B15:J15 purely because the fact A1:15 says "room 1"
What I would then do is after printing the data, change sheet 5 Cell A1 to say "room 2" and the sheet pull data B16:J16 down to B20:20 again, because Cell A16:A20 says "room 2"... and so on.
Big respect
Gary
Damn... you're like the Excel God!!! I love Excel and everything you can do with it. I'm nowhere near your skill level though.
Exactly the Function that I was looking for. Thanks
mate you just made my day.... and life much easy 😇 👌👌👌👌 Thank you
Thanks! Precisely what I was looking for!
First Of All i would like to say very very thankful to you because I was learn lot of formula form you videos. I am a big fan for your channel. And I have a question regarding this video.
Is it works for different excel sheet.......? I was tried to does before watching this video but its not working.
not what i am looking for but i've learned a lot and hopefully can use this in the future :)
Your Excel side is very friendly and very helpful. services are to excellent great AAA. Thanks
ABDUL REHMAN MUGHAL
KARACHI.
You are simply amazing!!!
I appreciate your efforts on Excel video.. Is there any way to capture change of value of stock price in a cell. Like old price and new price
another amazing trick thank you so much . you are the best
Great tutorial! Worked great! TY!
Awesome just what I was looking for
So cool😍 thanks for sharing this amazing trick
No problem 👍
Is it possible to have a formula that updates the master sheet (in your case "Report") whenever a new, duplicate sheet is added and will take the sum of a certain cell across all sheets? (trying to eliminate manually updating the sum function every time a new sheet is added. I would not be asking if it was only one formula that needed updating. Thanks for any responses. Great tutorials!!
i have been looking for this for some time new. great
Good trick
Thank you Mr. EXCEL
You instructions are super clear however the more I've been learning excel the more I've become perplexed why it is so complex. It's not that I'm unable to pick it up. You just think someone would've made things more simple by now.
This tutorial is really helpful and it works for the majority of the worksheet contents - other than where the cell data is yes or no from a drop down box.Is there a way for me to pull this type of data from different worksheets into my report?
Really good video, thanks
You Sir are a Jet!
Thank you for brilliant job!
Is it possile to do something similar as above, but instead you would be pulling a full table in from different spreadsheets? (Say these tables were all formatted the same)One other question, once one of the tables was pulled in, is it possible to edit the information and then save that back to the original spreadsheet?Thanks
Hi, thanks for the video, I have learned the way to extract data from different worksheet using indirect function. May I ask is there any way to use the same method to extract data from multiple workbooks which are closed ? I had tried the indirect function, and it work well only when sending workbook is open, it return #ref when it is close.
This was very close to what I needed. I think.. The application that I'm interested in is basically the opposite. Rather than pulling data from different sheets, I was wanting to take a small list of info and send it to different sheets. Any way this can be done?
Again your videos are SO FREAKING AMAZING! " Sorry gotta say it again :)
awesome trick.... thanks
Very useful!
I am not sure I follow. For back and forth dialog to get Excel solutions, try THE best Excel question site:
mrexcel. com/forum
i'm 3 years late from when you made this, but I have a question! this is perfect and has taught me everything, but I need your help where I have 2 categories. example: i have cuisine and dish types (e.g. american, entree) - and i'm creating a sheet to show min, average, max per serving for whatever 2 categories are selected. I did everything you said here for the one category, but how do I get another category in the same field? make sense?
Thank you so much!
Thank you, great explanation!
Hey Mike.
Been looking for that solution for some time,
I'll admit, I didn't go straight to excelisfun,
just googled it, total waste of my time.
Again, your solution was exactly what I've been searching for.
Cleverly done mr. Girvin.
One question though:
I need the cell reference to change as I move the formula around:
=INDIRECT("'"&$A5&"'!e33")
A5 is the reference to sheet name,
e33 is the cell reference.
I couldn't bring excel to accept E33 as reference instead of text.
(I tried the awesome "&&" trick, but no luck..)
What do you say.
Thanks rocking Mike.
Is this method possible to be used on Excel workbooks inside a folder and it will be NoT open.
I want to retrieve data exactly like you show but want to link from many other workbooks..not sheets.
If you could let me know it would be of so much help.
Very nice tutorial
How about if I want to make a string instead of numbers? Example, in B3 instead of numbers the output is the list of names
2020 quarantine learning some excel
Good use of your time : )
how do i do this if i want the drop down to be names and the data attached to the individuals?
Good Stuff! Possible to have multiple drop down? Example: you have multiple stores with revenue and expenses on each sheet. "drop down" Jan 15. "drop down" Smith street.
Can you help me how do i write sheet range with this formula, suppose sheet 1 to 3 or 5 to 6. It will help me a lot.Thanks
i watched the video till the end, i am using excel 2016, i watched the whole video but i was wondering the last part, when you select at the drop down, it will pull in data, hoq do you do it in excel 2016? i tried the same formula but it did not work
Thank you so much for doing this video. I hate the fact that Google made it so hard to find this but one more question, now that I've linked my sheets how do I make them interactive. For example instead of simply showing me info from tab "day 3" how can I type in information from the master tab and have it also update info from "day 3" tab. Hopefully I worded my question right and hope it makes sense.
Thanks
Awesome 👌.
Hi, how to filter the data using the drop down list in another spreedsheet. Like the output of dropdown list should act like input to filter in another sheet . For example, if i select fruit from dropdownlist, only rows with fruit should get filtered in another sheet.
Awesome video....
How do you manage to bring total from each sheet if the totals are referencing difference rows? Thanks
how do I make the selection populate multiple cells depending on the selection from the drop down list selection?
e.g. if i select "5x5" from the drop down list then i have 5 cells say "x5"
if I select "8x5" from the list then I have 8 cells say "x5"
Is it possible to use the INDIRECT formula and pull from a different workbook when the workbook is closed? For some reason the indirect formula only works for me when the workbook I am linking to is open. When I close the file it gives me "REF!". How can I select data using the indirect formula when the workbook I am pulling from is closed?
I have been wondering how to do those dropdown lists for ages
Thanks very useful
You are welcome!