Excel Magic Trick 1501: INDIRECT Function: Pull Data from Different Sheet Based on Row Number
Вставка
- Опубліковано 12 вер 2024
- Download Excel File: excelisfun.net...
Entire page with all Excel Files for All Videos: people.highline...
In this video learn how to use the INDIRECT Function to pull Data from a Different Sheet Based on Row Number.
Thanks for explaining how the INDIRECT function works :-))
I am used to avoiding volatile functions wherever i can, so in the most cases i use construction with INDEX.
In this case it would be something like this.
=INDEX('Source Data'!$B:$B,B3)
Thanks for video!!!
So simple and beautiful!!! Thanks Bill Szysz!!!
I'm with you on avoiding volatile functions. I always strive for optimal spreadsheet performance
Totally with you on this one Bill. For those wishing to take it a bit further still I've put both the source and solution into tables to increase the versatility.
ua-cam.com/video/-97A_Qf_HDU/v-deo.html
Really, I should have showed a drop down with different sheet names to pull data from same cell on different sheets... Then it would be a good use for INDIRECT...
Thank you, Mike, you are in fact the number one contributor to the sharing of Excel knowledge in the world.
Thank you for this video. Saved me! Had NO idea how to do this! Thank You!
You are welcome!
It's taken me three years to finally understand how and when to use the indirect function. I've watched many of your and other's videos, but today was the day I ran into a situation at work that required it. Of course I came straight back to your channel. Thank you Grandmaster Mike!
You are welcome, Cary!!! What is the situation that you ran into today?
@@excelisfun I have a spreadsheet made by colleagues that reference many nonadjacent cells on multiple worksheets in funding calculations. I identified the cells and lined them up in a single column for easy reference. All I have to is wrap them in inderect, and I don't have sort through the sheets for 10 cells to add for new calculations.
@@bamakaze That is a great use!!! You are one awesome Excel Expert, Cary : )
Sir all your videos are great, there is no match for them , can not find this type of teaching anywhere else in UA-cam or even in paid classes. Thank you so much.
I can never get enough of the text functions. Thank you for the excel trick Mike!
You are welcome, N Sanch01!!!!
Great thanks Mike! I have seen other UA-cam videos on indirect function, but the way you explained it, it's simple and more precise. Thanks again. I have now better understanding of indirect function and it's utility.
That is my specialty for 12 years at UA-cam: try to make the complicated, less complicated. Glad you like it, Shantanu!!!
I have ALWAYS wondered how to do that, thank you so much. I learned INDIRECT from you when it came to looking up tables that were designated using the Name tool, but never thought of it in this manner.
Yes, INDIRECT and do many things, But For this example, as Bill Szysz says, it may be better to use INDEX, I should have showed a drop down with different sheet names to pull data from same cell on different sheets... Then it would be a good use for INDIRECT...
ExcelIsFun but this is good in case you have to build a cell name, such as B & 1 together, and then see what the value is. All good with INDIRECT.
Thanks Mike. That's very clear explanation for INDIRECT. I can just refer to this video if anyone ask me about INDIRECT. Can't wait for the next video.
Thanks, Jung! I am glad that the video is useful : )
In depth explanation of indirect ❤️😍
Haven't really used the INDIRECT function much, so this was great Mike!
Here is a great video about how to use Names and INDIRECT: ua-cam.com/video/fjgFQ-YaOZE/v-deo.html
Thanks Mike, I'll check it out!
Nice and simple explanation.. thanks, and ride on !!
Amazing thank you so much to the one who asked the question and thank you of course Mr. Mike for helping him to solve it a great trick thanks a lot and don't forget you are always the king
You are welcome! Thanks for the support, Ismail -- Just a Guy Having Fun With Excel : )
Hello Sir ExcellsFun, How are you? Happy New Year. I found this video and was extremely excited to learn the format that you shared. I do have a query if during the month and or week, I transfer money from checking to savings and or received refunds from purchases, can you share how the formula can be modified to do so? I really appreciate and look forward to seeing that video.
Sincerely
New Viewer
Thanks for the video. How do I reference in a master sheet the same cell number in multiple rows in different sheets using the drag down + sign. For example, I want to automatically add the cell C3s in sheets A, B and C.
I would go with the INDEX function but INDIRECT is also useful in some cases :)
Thumbs up anyway :)
Thank you for the support : ) I should have showed a drop down with different sheet names to pull data from same cell on different sheets... Then it would be a good use for INDIRECT..
This solved my problem.. thanks a lot!!!
another great tip for the INDIRECT function!
Thanks, Doug!!!
Thanks to this indirect tricks theses reviews allows us to be efficient :-)
For this example, as Bill Szysz says, it may be better to use INDEX, I should have showed a drop down with different sheet names to pull data from same cell on different sheets... Then it would be a good use for INDIRECT...
You are welcome, as always!
Mike, thank you for the video. Is there a way to accomplish this where INDIRECT not only brings the value but also the formatting? Such as bringing cell color, bold etc?
Not that I know of....
Thanks Mike and Bill Szysz. This is Great!!! :)
You are welcome! It is great to be on a good Team : )
HI Mike, I'm having trouble using the INDIRECT function and not getting the results I need. I currently have the following formula -
SUMIFS('Assets (All)'!$E$7:$E$75000,'Assets (All)'!$Y$7:$Y$75000,'Product Check'!$B23,'Assets (All)'!$B$7:$B$75000,'Product Check'!$AG$10))
However, I'd like the $75000 to be linked to the number of rows I actually have on the Assets(All) sheet rather than the 75000.
What way would you suggest. Any help appreciated.
Thanks
Hi
I wanted to access data from different workbooks but with them not open. Is there any alternative of Indirect (not using VBA) to access data from closed workbooks?
Thank you Mike!
You are welcome, Khan!!!
Very nice trick! Thanks 🙏🏼
You are welcome, Thao!!!
Great tips
Glad you like them! Thanks for your support : )
As Awesome and Amazing as Always.
Glad the video Helps. For this example, as Bill Szysz says, it may be better to use INDEX, I should have showed a drop down with different sheet names to pull data from same cell on different sheets... Then it would be a good use for INDIRECT...
OK, this one was easy. Thanks. I’m curious where you’re gonna take this next...
Coming soon : )
ExcelIsFun (Spoiler Alert) OK, I’m a guessin’: data validation, indirect, double quotes (!) and structured references.
Am I right? Did I leave something out?
OK, maybe I should cut down on those Excel Magestic Flick videos for awhile...
...LOL! Not gonna happen anytime soon! ;-)
ExcelIsFun Oh, BTW: since like literally a few days now the Power BI icon is poppin’ up like everywhere at work: we have quite some MS Surface Hubs in the meeting rooms and the app has recently been installed on there. So, if you don’t mind my sayin’: I wouldn’t mind if you were to shift your focus towards PowerBI (including PQ - don’t forget that!) and DAX: that would be really nice...
: )
Great Idea!
Glad it helps! For this example, as Bill Szysz says, it may be better to use INDEX, I should have showed a drop down with different sheet names to pull data from same cell on different sheets... Then it would be a good use for INDIRECT...
Greattt... Thanks for the revision
You are welcome, RRR!!!
Thanks Mike. I presume this is a start for INDIRECT. I use indirect also using names (in stead of formula's). Question/Remark: You can make (static) references to other workbooks. If you close the workbook the whole filename and path is visible. But unfortunately you can't make dynamic references with INDIRECT to other workbooks, correct? Is there another way?
Does this help: ua-cam.com/video/H40-xBVtl-Q/v-deo.html
Mike : 2011 long time ago, but this video deserves a remake: learned a lot! ctrl-tab and count with SUMPRODUCT, awsome! But it does NOT solve my problem. I want to make dynamic references to closed workbooks. So the name of the file is a cell.
I am not sure how to do that. BUT, the best place for back and forth daiolog to get Excel Solutions is: mrexcel.com/forum. I would guess that one of the 100 Excel Masters at this site has an idea : )
ExcelIsFun ok. I will also ask some MVP at the Amsterdam Excel summit. I will let you know..;)
Hi Mike, today at the Amsterdam Summit 2018 I asked Bob Ulmas (great Excel Expert btw): he said you can't use this with INDIRECT with closed files. Suggestions was to write a VBA code to open all the files, get the data with the formula's and then close the files again.... greetings, Bart
Thanks Mike.
You are welcome, WRH!!!
Thanks
You are welcome, ss v_94811!!!
Thanks for INDIRECT fun :)
You are welcome, Teammate!!!
Please create a A-Z Power BI course!!!
Okay. I will create a free class for everyone in the world. It will start in next few weeks and go for about 6 months. I need your help, and everyone else at UA-cam to help also: ==>> Always click the Thumbs Up and leave comments. And tell all your friends and colleagues to Subscribe to the excelisfun channel. That is a fair trade, right : ) : )
ExcelIsFun Yeah, Mike why not, i was thinking of a paid course but as you say, one of my new colleague asked me how did you learn excel and you bet i already have him addicted to your channel!!
Sign me up. lol
You are already signed up : )
I like to make the classes free here at UA-cam - so everyone in the world can have access for free. But if that is the case, i always need help from the Team with Thumbs Ups and comments to help support the free education : )
Great
Glad you like it! Thanks for your support : )
super Trick
Thanks, sandeep! Glad it is super : )