Excel INDIRECT Function: Lookup Values in Different Sheets / Excel Tabs
HTML-код
- Опубликовано: 30 сен 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.c...
Learn how to use INDIRECT to manage data across different tabs and make your Excel tasks simpler. Whether you're a beginner or looking to enhance your skills, this tutorial has you covered.
⬇️ Download the workbook here: pages.xelplus....
✅ What You'll Learn:
▪️ The basics of INDIRECT function: Understand what INDIRECT does and how it simplifies Excel formulas.
▪️ Practical examples: See how INDIRECT works with SUMIFS function for dynamic data management.
▪️ Tips for dealing with different data tabs: Discover how to use INDIRECT when your data is spread across multiple tabs.
▪️ Error handling: Learn how to avoid common mistakes and understand error messages.
▪️ Advanced usage: Explore more complex scenarios where INDIRECT can be a game-changer.
▪️ Performance impact: Understand the limitations of INDIRECT in larger spreadsheets.
INDIRECT can be very useful in Excel Dashboards when you need a dynamic cell reference instead of hard-coding the reference in a formula. For example, let's say you have a drop down where the user can select for which year the revenue should be shown. Depending on the selection the formula with a SUMIF or SUMIFS function should sum up different ranges of data. Instead of writing a long formula with different conditions for each year that could be chosen, you can use INDIRECT.
Get the full Excel Dashboard course here: www.xelplus.co...
Indirect can be a confusing function. It takes a little bit time to get the hang of it. What Indirect does, is it returns an address. So for example, if you type in =indirect(A1) and inside A1, you have written A10 - then your formula returns what is inside A10. Why would you need this? Watch the full video and download the workbook to practice along.
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creato...
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.co...
🎥 RESOURCES I recommend: www.xelplus.co...
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#Excel
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
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.
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 have made things too much complex to understand
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.
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 :)
Thanks for the INDIRECT to different Sheets, Leila!!
You're welcome Mike. I'm a big fan of your Indirect videos :)
This is the most complicated video😂
I thought of the same 😂
Me too 😂
Excellent explanation :)
LIKE!
Thank you Excel y Mas :) & nice to see you here!
Great video, thanks. Would have been easier to follow if you used standard excel references for cells and sheets. Thanks again
Rocket science is a prerequisite for this course.
You are right😂
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
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
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,
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, 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 "&" ?
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
VERY NICE MAM
THANKS
OM SAI RAM
Hi Leila you are greate. I need to start from the beginig but you didn't numberd your videos. So which video come first? Would you please put numbers to them!! Otherwise I am ipressed with your lecture and I am a subscriber.
Thanks
Thanks. Appreciate it :) There is no real beginning though that's why the lack of numbering. Each video covers a separate topic. Inside my courses (for example the dashboard course) you will find a lecture numbering because the videos build on each other as we create two dashboards from scratch. In RUclips I have created playlists that organize videos based on a topic....
@@LeilaGharani hi leila, I too agree with this. I am trying to follow your videos but I am not able to keep a series. It would be helpful for me as well if there was a numbering system to move from easy to more advanced. Thanks for your videos. This is really helpful.
I came, I learned. Thanks Leila
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!
Does anyone (Leila :D ) know if it possible to somehow wrap this with an IFNA() ?
Example: {=VLOOKUP(A1, '\\SBS2011\RedirectedFolders\SOME_NAME\My Documents\SOME_FOLDER\PO_SHEETS\[FILE_LOOKUP.xlsx]Sheet1'!$A$1:$D$500, {2,3,4}, FALSE)}
Thanks
i found a very easy way to solve this =SUMIF(INDIRECT(A2&"!B:B"),B2,INDIRECT(A2&"!D:D")).
I have placed the report in this way -
Year Category Total
2017 Game 227020
The SUMIF asks for range which is dependent on the Year (drop down )which is chosen. So using indirect function we are choosing the sheet which requires to be referred for the range.And the range is in column B:B in both the sheets.The criteria is category which can be any of the three which we choose through a drop down.And the sum range is there in column D:D in both the sheets.
I have also added another level
Year Division Region Revenue
2016 Utility South America 33681
Here I have used SUMIFS
=SUMIFS(INDIRECT(A6&"!D:D"),INDIRECT(A6&"!B:B"),B6,INDIRECT(A6&"!C:C"),C6)
How do you use Indirect Function to reference another workbook with varying sheet name and the cell value needs to match certain criteria?
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?
I doubt this will get to you but I'm hoping against the odds. Is there a way to use the indirect function to create dynamic drop down lists. In my position, every month, I receive a spreadsheet from a dozen different HR locations with two different tables. Instead of opening each spreadsheet and extracting the information I need I want to create a drop down list. The first drop down would either select the spreadsheet I want or the first table and the second drop down would either be the first table or second table (not really sure how it would work). Each spreadsheet is uniquely named based on the region it is coming from and each table is also uniquely named. The headers of the tables are all named the same. I don't think this is possible because I can't make a simple drop down from a different worksheet.
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 ?
Hi Leila, You have very well explained the function...
You're welcome Sachin.
please put video for SUBSTITUTE function
THE BEST tutorials online - Thanks very much!
You're very welcome Ben. I'm glad you like the videos!
Thank you for this. I was able to get to the correct spreadsheet I need, but I need a second lookup for the column in the resulting sheet. I've used both vlookup and match, and I do get the correct column. Is it possible to combine the two forumlae? So far, I haven't been successful. But I'm thrilled to get as far as I have.
Instead of vlookup we can give the table name in the text string.Anyway thank you madam 😊
Confusing
Great video as always! I had one question, why doesn't the 1st argument of the indirect function pick up a named range? I tried to input a named range in the function instead of the cell, it did not work.
Probably that's the function signature. Not all functions allow array input.
Thank you .. you are very wonderful
Need a video from different files too instead of sheets..
Kindly help us same!
Thanks a lot.
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
Dear Leila, thank you for your great tutorial, I have a question. Is there any way to use date in indirect function as date instead of number? Whenever I use indirect formula combine with date, it indicates the date as number like 43922 but my sheet name is 01.04.2020 and I can not create a correlation. What I mean is when I wrote a formula in a cell =INDIRECT("A94") I get a result as 01.04.2020 Thats ok but when I wrote the formula as ="'"&INDIRECT("A94")&"'" I get the result as '43922' instead of '01.03.2020'. Is there any way to get the result as '01.04.2020'. Thank you in advance.
Use text formula at the beginning of the whole formula: =TEXT(FORMULA, “DD.MM.YYY)
Can I use this indirect function to send a number to another worksheet that will be used as a vlookup to another table.
Can we not use =k15 in place of indirect? We any how have the text we wanted in it
Integration between excel file to google sheet changes in any one file update any where between google sheet and excel file
similarly how can we get the values if range is used as input instead of cell reference
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🙏
Totally lost m eon this one. Of course it is 7 years old and likely outdated.
Hi Leila? I have tried to use indirect function with named range in the data validation it is working perfectly but if i substitute the named ranged with dynamic named ranged (i.e. using offset function) in the data validation it is not working. Would mind to explain why?
Hi Solomon - there are special cell references and formulas that the data validation doesn't like to directly process. They work indirectly in data validation through name manager only....
@@LeilaGharani I pasted the offset function in the name manager and tried to reference to a cell which is having a dynamic name range in the data validation e.g. =indirect(G2) it didn' t work but when I removed offset function and I replaced with a named range it worked.
@@LeilaGharani Thank you.
This video is for advanced excel users. It's too difficult for people trying to find out what indirect does. Recommend for advanced users only.
Not necessarily
I think there is a lot going on where it gets hard to stay focused when you are unfamiliar with the indirect function. I had to look at some other videos that were not so much less advanced but more direct to somewhat understand this one.
it is for advanced users. there's several different concepts going on here. i watched the video at least 8 times starting from 6:51
It the TEXT() function too difficult for you? If it isn't, then this one should be ok as well!
Otherwise I wouldn't pick without knowledge.
How about refering to different excel file?anyone can help?
Hi Leila When there was a text "hello there" in A6, the indirect(A6) was #Ref. But when you used Sumifs with indirect(K15) it accepted as vaild "2016 data_py[Revenue]" in it. Is it because a formula used for indirect(K15)? I am very curious to know the logic behind it. Thank you
Hi Nathan - Indirect is bit of a tricky formula. The way I made sense of it, is to view the cell reference as the address provider. So for the case "Hello there", indirect is looking for a cell reference called Hello there. If we type A1 inside A6, it will go to cell A1. If the cell reference is not A1 style, it will look inside name manager. Since Hello there is not a range reference, it results in an error. For the second case, the data_py[revenue] is in fact a range reference. It is the table name and the table column, so the message inside K15 could correctly be translated to a range. Hope this makes sense....
Thank you Leila for the clarification. Much appreciated.
You're very welcome :)
I'm studying this for my spreadsheet in design..Nice...
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.
Clever way to sum various tabs
Is it possible to use that formula without tablet sumifs
This is helpful but annoying at the same time.
Nice video. How to do it with an image in another excel file ?
Please give me solution. Like sync sheet excel add in
Hi Leila, If I were trying to adapt these two formulas to INDIRECT, but only for the sheetname1 references, what would you suggest? thank you in advance.
=MATCH(B2&" "&B3&" "&B4&" "&B5,'sheetname1'!A5:LMA5,0)
=INDEX('sheetname1'!$A$7:$AP$33,ROW()-ROW($A$9),$K$6+COLUMN()-COLUMN($A$9))&""
Why we need to concatenate with revenue and division with the Vlookup function, Please do clarify this doubt ?? I am waiting for your answer for this.
I wouldn’t expect her to answer as she posted this 5 years ago. It’s concatenated so the Vlookup formula references the correct Table Header
Its like pointers in programming isnt it
Imagine she got even 1% of our earnings for all she teaches the world how to do our job?
I just got a job with a workbook with INDIRECT - and i'm like "WHAT?"
Wouldn't it be easier to just name the tables sales2016/sales2017, and put that into the indirect function? Don't really need all that over complicated vlookup stuff. For example: sumifs(indirect("sales" & c14 & "[Revenue]"), indirect("sales" & c14 & "[Division]"), b15)
So INDIRECT() is exactly used to convert type from TEXT to CELL REFERENCE, likewise TEXT(), DATEVALUE(), and VALUE() do other type conversions, right?
Hi dear Leila, what is the best combination of AVERAGEIFS in case of using multiple rows and columns criteria together??, please help and advice,
This video was great, but I wanted to use "Region" instead of "Division" in the formula, unfortunately I couldn't do it.
Ma'am, You should try in hollywood!
Hi I need a help with one formula to solve my ranking problem following various conditions. Ranking basis country wise.country 1 gets inportance over country 2. Rank basis Delivery charge offered by country 1 supplier than country 2 supplier. It there is tie between supplier than ranking basis lowest delay charge cost. If there is tie at delay charge than rank basis no of years in business.
You could name individual cells? I had no idea damn 😂
Can you make a little more confusing.
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
I have a question so data in different sheets is the exact quantity in 2016 and 2017, if the data quantity and location maybe was a bit different between those sheets would this formula work? what's the solution in that case especially it's a realistic example that the data quantity wouldn't match for different years
Leila I did Learn long time ago as of 2011 my best teacher was ex-boyfriend,I did watched him very closely what he was doing: Trust me I knew all about what was on, and what was removed.
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? :)
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 RUclips 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
Thanks. Your way of teaching solved every bit of my confusions regarding INDIRECT function
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?
It's me, Control T. I'm trying to figure things out. DGET/indirect together? No response necessary. Just curious.
not so helpful very confusing
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 .
How to Display Images Dynamically based on Specific Cell Contents IN GOOGLE SHEET
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!
Hello Leila,
Could you help me regarding this issue? I tried using the indirect formula for different workbooks. I have multiple file with only the month name different. Like jan report. Feb report. The indirect formula referencing to the text name of the file works. But i need the file to be open. Can you work around that and manage to fetch the data when the file is closed?
Pls make another video on indirect function with more examples. It is difficult formula.
Laila, great videos! can I send you the excel attachment to identify errors, which is not reflecting the correct cell data with vlookup function.
How do you prevent 'negative result ' that comes from a formula? You should only allow a positive number.
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.
You are incredible useful
I just did not understand the note at @11:03
Mam, i want vlookup cell address instead of values not only single sheet "multiple worksheets at once "
Hi. I use Mac's Numbers. It doesn't have a name manager. Do you have a work-around for this.
I've been struggling to understand this for weeks. Thanks so much for the info!!
Suppose I Have about 50 sheets with different names each having unique item number under a particular date within it.
Queries:
1) I want all those names to appear in the summary sheet without having to type those manually one by one.
2) I want the data on those multiple sheets to sync with the summary sheet as well .And I want them to be arranged datewise and itemwise in the summary sheet as well.
if you are working on hundred sheets, better not to name the ranges. Just use the original range name (Column:column, or ro:row), as lon as your sheets are of same format
This is the most complicated video ii have ever watched. I ran into more difficulties rather than simplifications
You explained in such way that audience is already well known about function. There is a need to elaborate in simple way.
THANKS a lot. Beautiful presentation. One help please. Like relative cell reference, can we do relative sheets reference?
A little thing annoys me with using this function is excel always ask for save when close file even though I didn't change anything after open.
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?
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?
Hi can you help me I want if value is anywhere in sheet1 and when I put the same value in sheet2 any cell I get that value in front of that....