Leila is the QUEEN of Excel formulas. Like if you agree. I'm addicted to Excel and sometimes I think I know a lot but every time I'm blown away when I watch her videos
I really appreciate the fast pace of your videos and that they are targeted to users with Excel experience. I feel like I'm getting a lot out of the 10 minutes spent on your videos instead of other videos showing baby steps that take loads of time to explain something simple. Loving your channel, thanks!
@@LeilaGharani I was asked to try and create an inventory system for spare parts. That was easy. My challenge is to create a system that could macro quantity of parts and certain criteria of the parts requested another worksheet. Something like Amazon cart. I have a general idea of how to go about it. But would this be a cool video to create? Can’t find anything like it anywhere? (I want to say I’ve watched nearly every video and you are my hero and have carried me from my very basic job as a machine operator to a salary paid Operation Performance Lead helping so many get organized, developing sustainable systems and presentations that actually sell new and innovative ideas.)
Thanks Leila! I had no idea about the Indirect function, but after watching your video I have some idea. It messes with my head, which means to me that it’s worth learning. Thanks for this introduction.
This is the first time I found one of your tutorials hard to follow and understand. No big deal, I was able grasp the indirect function by looking at other TH-cam videos. You're still my favorite Excel teacher by far. Thanks for all you do.
yes even i found it difficult and so i have worked around the net and found a very easy solution and i have updated it in the comment you can check up ..will be happy if it helps you.
Indirect saved me from frustration a few years ago. But this video has some interesting things that set me thinking about wild stuff to do. Thanks, Leila
Hi Leila , Excel is like a passion for me , and I have watched tones of videos, but yours are really outstanding , Honestly , you have done an amazing Job , and I visited your website too, and it looks so professional , well done , and looking forward to learning more from you David Redouane
I couldn't do your way, but for me this formula worked =indirect(concatenate(cell where the sheet name is going to be),"[name of column you want to get results]")) Thanks for your videos! They help me a lot!
Hi Leila,thanks a lot for this great video.indirect is one of the most useful function of excel and you really explained it very well.ExcellsFun MIke and you are my favourites for excel learning.thanks again.
Very informative, and very useful for the excel users/addicts/lovers. I am one of your great fan of watching your videos. You are doing a great job for the excel users and making it interesting to learn more and more. Hope you will continue your good work in the coming days too!!!!!!!!!!!!!
Thanks Gopala. Happy to hear that. I will do my best to put as many tutorials out there as I can :) Let me know if you'd like to cover any specific topics and I'll add to my list.
Thank you again! :-) This was so helpful. I used "indirect" to have my formula (inside a proper Excel table) look in the cell below the current row/cell and compare it to the current cell while avoiding #ref errors if I choose to overwrite or delete half of the raw data.
Thanks. the part I wasn't getting was how to stop the ' from ruining my formula, never considered putting it in quotes to make sure it was handled as text itself. I feel like I learned something important.
Hi Laila, I am very interested in your explanation. This is very similar to the case I experienced where I have more tabs. Suppose the tab consists of data from 2016 to 2024. So what is the formula for summing up several tabs (multiple sheets) in sequence that we want with the criteria as above? Suppose we want to sum several tabs from the initial selection of the 2016 tab and the final selection of the 2018 tab? Or suppose we want to sum several tabs from the initial selection of the 2020 tab and the final selection of the 2023 tab? Note: The initial selection of the tab in a cell and the final selection in another cell are in the form of a dropdown list. Thank you for your attention!!
For clarity, it would be more helpful and easier to follow if you use cell reference instead of named ranges in your examples. For advanced students that would be fine.
Bit of a newbie, is this the only way to pull from other sheet tabs with a helper cell? You mentioned Choose at 11:26, can this be used? Do you have an overview of that function? Am I just looking too deep, am I missing the basics here, is there an easier solution for my formula (=VLOOKUP((A1,'Sheet2'!$A:$G,5,FALSE)) can I not have Sheet2 in a helper cell on my main sheet and have excel use it to know where to look without other bedded functions?
Been following your videos for a very long time now n they are amazing and very informative, can you please make one video on how to use consolidate function to collate data in one sheet.
i always watching your video which help me understand excel formula well now I got knew quarry ,that's why I request u to make VBA classes video step by step ,I also practice for it
Honorable gracious Madam! I am impressed of you. Your teaching style is commandable. Kindly let me know how to extract data from other workshhet or workbook by using index match function please?
Thanks Azhar. I have two videos on Index & Match: th-cam.com/video/F264FpBDX28/w-d-xo.html and more advanced: th-cam.com/video/ontXHp9cwOQ/w-d-xo.html. You can then combine the indirect with those formulas instead of Vlookup. Is that what you're looking for? I'll add it to my list of future videos.
Hi Leila, it would have been better if u said that sheets 2016 and 2017 are referenced with names Data_Py and Data_Current. But no issues. Thank you very much at your tutorial.
Hi Leila , Thank you for providing Knowledge transfer to worldwide . Would you please share the tutorial of Index and Match function using VBA from one sheet 1 to sheet 2 .
Leila thank you for all the inside into the huge realm of excel. I have workbook that I need help much like the one you have on your tutorial is there anyway to get your guidance to complete it?
Wanted to know if we can add countifs formula between 2 different workbook with multiple criteria. Even if we close the excel data should reflect..is it possible
Thanks for your tutorial video !!!. It is wonderful & you explain very clearly. Just want to know is there a way to referencing value in a closed Excel workbook using INDIRECT?
Very interesting video, it's an eye opener for us. I wonder if you could make a video or give any tips on how to put together multiple cells from different tables into a single Excel sheet chronologically organized. For example: Table 1 has the following data: EE number / End date / Start date / Company 00000123 / 31.10.2023 / 13.02.2023 / Mokso 00000123 / 31.12.9999 / 01.11.2023 / Tecny Table 2 has the following data: EE number / End date / Start date / Ann. Salary 00000123 / 24.10.2023 / 13.02.2023 / 60000 00000123 / 17.03.2024 / 25.10.2023 / 62000 00000123 / 31.07.2024 / 18.03.2024 / 62500 00000123 / 31.12.9999 / 01.08.2024 / 65000 Table 3 has the following data: EE number / End date / Start date / Hire Date 00000123 / 31.12.9999 / 13.02.2023 / 13.02.2023 These have to be merged into one sheet as below, creating the rows separately, with a front row for the month (period), highlighting the values that have been changed in the new row. This may look simple for a single EE, now think that this has to work for 2000 employees. Month / Employee Number / End date of record / Begin date of record / Company Name / Ann. Salary / Hire date Feb'23 / 00000123 / 24.03.2023 / 13.02.2023 / Mokso / 60000 / 13.02.2023 Mar'23 / 00000123 / 31.05.2023 / 25.03.2023 / Mokso / 62000 / 13.02.2023 Apr'23 / 00000123 / 31.05.2023 / 25.03.2023 / Mokso / 62000 / 13.02.2023 May'23 / 00000123 / 31.05.2023 / 25.03.2023 / Mokso / 62000 / 13.02.2023 Jun'23 / 00000123 / 17.06.2023 / 01.06.2023 / Tecny / 62000 / 13.02.2023 Jun'23 / 00000123 / 31.07.2023 / 18.06.2023 / Tecny / 62500 / 13.02.2023 Jul'23 / 00000123 / 31.07.2023 / 18.06.2023 / Tecny / 62500 / 13.02.2023 Aug'23 / 00000123 / 31.12.9999 / 01.08.2023 / Tecny / 65000 / 13.02.2023 I hope you take the challenge!
Thank you very much for the video. 🙌I have a bit different scenario and I am not sure which Function should I used. Hoping you could help me or direct me to one of your videos. I have two tables in two separate sheets in one excel file. One table is a recipe table where I have all the ingredients, measurements, and another column to reference the price of the product from the second table in a different sheet. I am consistently updating the second table and sorting it A-Z. When doing so the retrieved values are changing and all my pricing is going out of order. I tried to use the lock"$" but it does not seem to work. Could you suggest a function or a method to keep the referenced value from moving and at the same time staying the same value? VLookUp would not work as the names of the ingredients are not always the exact match to the ingredients table. Thank you, Andrey🙏
Hi Leila - I am using INDIRECT to fetch a cell values from another workbook/s. It works fine while the other workbook is open, but turns to #REF when the workbook is closed. Pls suggest a workaround to this to keep the values in my workbook updated real time referencing the other workbooks
Explained in very detail, impressive... just wondering if you have to rate yourself on scale of 1 to 10 on excel skills where do u keep yourself ...maybe 15? or 20? :)
Hi i need help with a formula , i have a dashboard selection for products , the product have different version in tabs with different cost , so I select 2 dropdown menus and I need the formula to validate 2 selection and then go to the intersection in the database to get the price
Hi Leila, I have just became a big fan of your videos. Thanks a lot. In this example it's pretty simple if we use : =IF($C$14=2016;SUMIF(Data_2016!$B$2:$B$16;Report1!B15;Data_2016!$D$2:$D$16);SUMIF(Data_2017!$B$2:$B$16;Report1!B15;Data_2017!$D$2:$D$16)) why do we have to use indirect ? in other words,can you say a situation where without "Indirect" we can't get easily the result?
Hi Leila, this a great example. however, I am trying to use both INDIRECT(SUBSTITUTE(P11,"","")) function for listing valDoue depending on selection from previous cell. Do you have an example how to do that?
Grab the file I used in the video from here 👉 pages.xelplus.com/indirect-function-file
Leila is the QUEEN of Excel formulas. Like if you agree. I'm addicted to Excel and sometimes I think I know a lot but every time I'm blown away when I watch her videos
I really appreciate the fast pace of your videos and that they are targeted to users with Excel experience. I feel like I'm getting a lot out of the 10 minutes spent on your videos instead of other videos showing baby steps that take loads of time to explain something simple. Loving your channel, thanks!
I'm really glad you like the videos!
@@LeilaGharani
I was asked to try and create an inventory system for spare parts. That was easy. My challenge is to create a system that could macro quantity of parts and certain criteria of the parts requested another worksheet. Something like Amazon cart. I have a general idea of how to go about it. But would this be a cool video to create? Can’t find anything like it anywhere?
(I want to say I’ve watched nearly every video and you are my hero and have carried me from my very basic job as a machine operator to a salary paid Operation Performance Lead helping so many get organized, developing sustainable systems and presentations that actually sell new and innovative ideas.)
You cannot imagine how helpful are your tutorials for me. Thanks so much! May GOD bless you anf give back to you more than you freely give.
Thanks Leila! I had no idea about the Indirect function, but after watching your video I have some idea. It messes with my head, which means to me that it’s worth learning. Thanks for this introduction.
This is the first time I found one of your tutorials hard to follow and understand. No big deal, I was able grasp the indirect function by looking at other TH-cam videos. You're still my favorite Excel teacher by far. Thanks for all you do.
Thanks for the feedback! I'll try to improve on it.
yes even i found it difficult and so i have worked around the net and found a very easy solution and i have updated it in the comment you can check up ..will be happy if it helps you.
I agree
Dear Leila... I really need to say: "You are simple the best"... Thank you!!!
Thank you! 😃
You explained in such way that audience is already well known about function. There is a need to elaborate in simple way.
I use excel since 1997, u are the best teacher ever
Indirect saved me from frustration a few years ago. But this video has some interesting things that set me thinking about wild stuff to do. Thanks, Leila
I think you are now my favorite person in the world.
Thanks for the INDIRECT to different Sheets, Leila!!
You're welcome Mike. I'm a big fan of your Indirect videos :)
I've been struggling to understand this for weeks. Thanks so much for the info!!
Thanks. Your way of teaching solved every bit of my confusions regarding INDIRECT function
Leila, I met with your channel; this is a gif for me; you're so right. Thanks for these tips. More, please
Thank you Angel! Will do!
Hi Leila ,
Excel is like a passion for me , and I have watched tones of videos, but yours are really outstanding , Honestly , you have done an amazing Job , and I visited your website too, and it looks so professional , well done , and looking forward to learning more from you
David Redouane
I couldn't do your way, but for me this formula worked =indirect(concatenate(cell where the sheet name is going to be),"[name of column you want to get results]"))
Thanks for your videos! They help me a lot!
You must be a happy person. You know - being familiar with the very entrails of the program! God bless you!
Thank you! There is still so much learn... but that also makes me happy :)
THE BEST tutorials online - Thanks very much!
You're very welcome Ben. I'm glad you like the videos!
Leila, you are a gift to the world indeed❤️
It's a fantastic function..I used.it with Name Manager
...linked around 700 sheets and made simple dashboard..
Great. I'm glad you could apply it right away.
Hi Leila,thanks a lot for this great video.indirect is one of the most useful function of excel and you really explained it very well.ExcellsFun MIke and you are my favourites for excel learning.thanks again.
I'm very flattered and very glad to hear you liked the video. Mike is the best! I've learnt a lot from him.
Very informative, and very useful for the excel users/addicts/lovers. I am one of your great fan of watching your videos. You are doing a great job for the excel users and making it interesting to learn more and more. Hope you will continue your good work in the coming days too!!!!!!!!!!!!!
Thanks Gopala. Happy to hear that. I will do my best to put as many tutorials out there as I can :) Let me know if you'd like to cover any specific topics and I'll add to my list.
you're the best ever done this
👍👍👍👌👌👌👏👏👏
Thank you so much 😀
Subbed!
I'm an advanced Excel user (well, at least on the VBA side), but didn't know about this, so awesome stuff!
I'm glad you found this! It can come in really handy. Thanks for the sub :)
Rocket science is a prerequisite for this course.
You are right😂
Great video, thanks. Would have been easier to follow if you used standard excel references for cells and sheets. Thanks again
Very useful and informative makes my work a lot easier
Thank you again! :-) This was so helpful. I used "indirect" to have my formula (inside a proper Excel table) look in the cell below the current row/cell and compare it to the current cell while avoiding #ref errors if I choose to overwrite or delete half of the raw data.
You're very welcome.
This video was super helpful ! Thanks
Nice one, Leila. I would imagine my Boss (very old one) would scream to me in agony when seeing this kind of formula in my works.
😁
I'm studying this for my spreadsheet in design..Nice...
Thanks. the part I wasn't getting was how to stop the ' from ruining my formula, never considered putting it in quotes to make sure it was handled as text itself. I feel like I learned something important.
excellent examples and teaching!
Thank you! 😃
Hi Laila, I am very interested in your explanation. This is very similar to the case I experienced where I have more tabs. Suppose the tab consists of data from 2016 to 2024. So what is the formula for summing up several tabs (multiple sheets) in sequence that we want with the criteria as above?
Suppose we want to sum several tabs from the initial selection of the 2016 tab and the final selection of the 2018 tab?
Or suppose we want to sum several tabs from the initial selection of the 2020 tab and the final selection of the 2023 tab?
Note: The initial selection of the tab in a cell and the final selection in another cell are in the form of a dropdown list.
Thank you for your attention!!
For clarity, it would be more helpful and easier to follow if you use cell reference instead of named ranges in your examples. For advanced students that would be fine.
true :)
Excellent explanation :)
LIKE!
Thank you Excel y Mas :) & nice to see you here!
Thank you .. you are very wonderful
To say thanks is not enough, you are awesome, I really enjoyed, regards
You're very welcome. Glad you liked it :)
Bit of a newbie, is this the only way to pull from other sheet tabs with a helper cell? You mentioned Choose at 11:26, can this be used? Do you have an overview of that function?
Am I just looking too deep, am I missing the basics here, is there an easier solution for my formula (=VLOOKUP((A1,'Sheet2'!$A:$G,5,FALSE)) can I not have Sheet2 in a helper cell on my main sheet and have excel use it to know where to look without other bedded functions?
Thank you very much, dear teacher.
You are incredible useful
Awesome. You are very good Leila. Thanks.
Hey Leila, thank you for explaining this function on name of the range basis.
You're very welcome!
Thanks
Thank you!
Fascinating explaination
you're helping me a lot
Really wondering why there isn't a million view!
Thanks for this Leila.. :D It helped
You're welcome 😊
oSM Leila, i ♥ Love ♥ the Way you Explain every term....
Glad you like it Ankur :)
I came, I learned. Thanks Leila
This is the most complicated video ii have ever watched. I ran into more difficulties rather than simplifications
excellent tutorial ... expecting some more advanced tutorials on excel
thanks.
Always great !
Been following your videos for a very long time now n they are amazing and very informative, can you please make one video on how to use consolidate function to collate data in one sheet.
Nice content. Thanks
Super!. i like your explanation of indirect function.
Great! You're welcome Vikas.
por fin me sirvió esta fórmula!!!!Thank u
Clever way to sum various tabs
Hi Leila, You have very well explained the function...
You're welcome Sachin.
Great video! Do you have a video explaining the choose function?
Great! Excellent explanation
Glad you like it.
i always watching your video which help me understand excel formula well now I got knew quarry ,that's why I request u to make VBA classes video step by step ,I also practice for it
Hi Vikas - glad you like the videos. VBA is on the list :)
Thank you Leila!
Can we not use =k15 in place of indirect? We any how have the text we wanted in it
Honorable gracious Madam! I am impressed of you. Your teaching style is commandable. Kindly let me know how to extract data from other workshhet or workbook by using index match function please?
Thanks Azhar. I have two videos on Index & Match: th-cam.com/video/F264FpBDX28/w-d-xo.html and more advanced: th-cam.com/video/ontXHp9cwOQ/w-d-xo.html. You can then combine the indirect with those formulas instead of Vlookup. Is that what you're looking for? I'll add it to my list of future videos.
Hi, can you make a video on how indirect is used to refer to larger
tables in other sheet and rows/columns of that table using "&" ?
Thank you very much Leila.
You're very welcome.
Hi Leila, it would have been better if u said that sheets 2016 and 2017 are referenced with names Data_Py and Data_Current. But no issues.
Thank you very much at your tutorial.
superb, 👍👏
Glad you like it.
Hi Leila , Thank you for providing Knowledge transfer to worldwide . Would you please share the tutorial of Index and Match function using VBA from one sheet 1 to sheet 2 .
Leila thank you for all the inside into the huge realm of excel. I have workbook that I need help much like the one you have on your tutorial is there anyway to get your guidance to complete it?
Hello Leila ma’am
I am one of your great fan watching your videos. It’s very helpful and easy to understand.
But i have a query, can you help me ?
Great tutorials. Love the new intro and outro!
Glad you like them Greg :)
Thank you for correcting it, useful topic
Thank you again!
Wanted to know if we can add countifs formula between 2 different workbook with multiple criteria. Even if we close the excel data should reflect..is it possible
Great video Leila ! I admire your videos for its excellent way of explaining the concepts ! Thank you so much !
How do you use Indirect Function to reference another workbook with varying sheet name and the cell value needs to match certain criteria?
That is super slick.
Thanks for your tutorial video !!!. It is wonderful & you explain very clearly.
Just want to know is there a way to referencing value in a closed Excel workbook using INDIRECT?
Very interesting video, it's an eye opener for us. I wonder if you could make a video or give any tips on how to put together multiple cells from different tables into a single Excel sheet chronologically organized. For example:
Table 1 has the following data:
EE number / End date / Start date / Company
00000123 / 31.10.2023 / 13.02.2023 / Mokso
00000123 / 31.12.9999 / 01.11.2023 / Tecny
Table 2 has the following data:
EE number / End date / Start date / Ann. Salary
00000123 / 24.10.2023 / 13.02.2023 / 60000
00000123 / 17.03.2024 / 25.10.2023 / 62000
00000123 / 31.07.2024 / 18.03.2024 / 62500
00000123 / 31.12.9999 / 01.08.2024 / 65000
Table 3 has the following data:
EE number / End date / Start date / Hire Date
00000123 / 31.12.9999 / 13.02.2023 / 13.02.2023
These have to be merged into one sheet as below, creating the rows separately, with a front row for the month (period), highlighting the values that have been changed in the new row. This may look simple for a single EE, now think that this has to work for 2000 employees.
Month / Employee Number / End date of record / Begin date of record / Company Name / Ann. Salary / Hire date
Feb'23 / 00000123 / 24.03.2023 / 13.02.2023 / Mokso / 60000 / 13.02.2023
Mar'23 / 00000123 / 31.05.2023 / 25.03.2023 / Mokso / 62000 / 13.02.2023
Apr'23 / 00000123 / 31.05.2023 / 25.03.2023 / Mokso / 62000 / 13.02.2023
May'23 / 00000123 / 31.05.2023 / 25.03.2023 / Mokso / 62000 / 13.02.2023
Jun'23 / 00000123 / 17.06.2023 / 01.06.2023 / Tecny / 62000 / 13.02.2023
Jun'23 / 00000123 / 31.07.2023 / 18.06.2023 / Tecny / 62500 / 13.02.2023
Jul'23 / 00000123 / 31.07.2023 / 18.06.2023 / Tecny / 62500 / 13.02.2023
Aug'23 / 00000123 / 31.12.9999 / 01.08.2023 / Tecny / 65000 / 13.02.2023
I hope you take the challenge!
finally I got what I really wanted
:-)
Thank you very much for the video. 🙌I have a bit different scenario and I am not sure which Function should I used. Hoping you could help me or direct me to one of your videos. I have two tables in two separate sheets in one excel file.
One table is a recipe table where I have all the ingredients, measurements, and another column to reference the price of the product from the second table in a different sheet. I am consistently updating the second table and sorting it A-Z.
When doing so the retrieved values are changing and all my pricing is going out of order. I tried to use the lock"$" but it does not seem to work.
Could you suggest a function or a method to keep the referenced value from moving and at the same time staying the same value?
VLookUp would not work as the names of the ingredients are not always the exact match to the ingredients table.
Thank you, Andrey🙏
Hi dear Leila, what is the best combination of AVERAGEIFS in case of using multiple rows and columns criteria together??, please help and advice,
THANKS a lot. Beautiful presentation. One help please. Like relative cell reference, can we do relative sheets reference?
do you offer online course?
Hi Leila - I am using INDIRECT to fetch a cell values from another workbook/s. It works fine while the other workbook is open, but turns to #REF when the workbook is closed. Pls suggest a workaround to this to keep the values in my workbook updated real time referencing the other workbooks
Explained in very detail, impressive... just wondering if you have to rate yourself on scale of 1 to 10 on excel skills where do u keep yourself ...maybe 15? or 20? :)
Which is a better formula to make Excels lean and efficient. Index Match or Indirect Address?
Indirect is much easier. Index match a lot more arguments so It get confusing sometimes
Can I use this indirect function to send a number to another worksheet that will be used as a vlookup to another table.
Hi i need help with a formula , i have a dashboard selection for products , the product have different version in tabs with different cost , so I select 2 dropdown menus and I need the formula to validate 2 selection and then go to the intersection in the database to get the price
Nice video. How to do it with an image in another excel file ?
Hi Leila,
I have just became a big fan of your videos. Thanks a lot.
In this example it's pretty simple if we use : =IF($C$14=2016;SUMIF(Data_2016!$B$2:$B$16;Report1!B15;Data_2016!$D$2:$D$16);SUMIF(Data_2017!$B$2:$B$16;Report1!B15;Data_2017!$D$2:$D$16))
why do we have to use indirect ? in other words,can you say a situation where without "Indirect" we can't get easily the result?
Laila, great videos! can I send you the excel attachment to identify errors, which is not reflecting the correct cell data with vlookup function.
Is it possible to use that formula without tablet sumifs
thanks a lot for this very informative and helpful video. best regards!
Hi Leila, this a great example. however, I am trying to use both INDIRECT(SUBSTITUTE(P11,"","")) function for listing valDoue depending on selection from previous cell. Do you have an example how to do that?
As expected.... Good Job....
Thank you!
Thanks for showing different examples. Can we use indirect function with file path ?