Thank you for stepping through the Indirect formula. I've seen it a dozen times on as many sites, but I have not seen one explanation of each component of the formula. Much appreciated! Thank you!
I don't often leave comments but this was so well done that I felt compelled. Kudos for clear concise explanations but my favorite part was the multiple examples that demonstrate real world challenges and the slightly different approaches you might need to take to solve them. Take a bow sir!
There must be a good reason why I easily understand what you explain than many other people who offer different tutorials. The reason is simply, you can explain things so well. THANKS INDEED.
Thank you, Thank you, Thank you, Thank you, Thank you so much. You have no idea how useful this is to me in my daily work. Thank you ever so much. Kindly continue to contribute such excellent tutorials with excellent examples. God Bless You.
@11:13+, you said A1 or C1 or (R1?) style reference. I'm not sure what that means but OK. It obviously is a column, OR row, OR all of the table thing. It feels like I am missing the point by just a small detail.
@@Computergaga I'm having trouble understanding and making use of the following formula =if(not(isblank(B31)), indirect("Income!D"&B31&":Q"&B31)," "). I'm a beginner, can you help please?
Hi Alan.. another excellent tutorial. I've never really done much with INDIRECT(). Like many people, I went along for many years thinking I knew EXCEL well. Your great videos show me all the things I've been missing. I'll definitely incorporate INDIRECT() into future projects, although I understand that as a volatile function, using INDIRECT() sparingly is advised. I particularly like its use for dependent drop downs.. very clever. Thanks for helping me build a more solid foundation of EXCEL skills which I can use in my business and life. Thumbs up!
Thank you Wayne. Yes it is a very helpful function. Quite right in that it is a volatile function. So should be aware of such things when working with large workbooks as it can slow calculation speed.
I was really not that versed in using =INDIRECT(function) and to be frank I was avoiding to use it. But after well expounded topic in this tutorial it makes a lot of sense what is used for and especially when it comes using it with named ranges. Thank you for explaining in minute detail and to the point for all of us. 👍
Thank you very much. Very interesting, practical, inspiring, revealing and thought provoking. You are doing a great job for the excel lovers/addicts/users. Please continue your good job for the benefit of Excel users with some more tricks/tips/time saving tools. Yours affectionate.
Hello Mr. Alan; Superb video, thank you, I liked your explanation and your lesson of INDIRECT function: you helped me to retain this function and especially liaisin with another sheet with a name composed as "South Africa" thanks again
Great examples Alan! This is the best demonstration of indirect function, so easy to understand, clear and how it can be used in so many ways which I hadn't known before. Now I know why indirect() is useful which I failed to gather from other channels. To add to the complexity of the last example, it would be great to show how to handle an office that consists of two words like Milton Keynes. :)
I'm truly not sure if I have enough intelligence for this type of thing. So I am trying to learn it. Your clarity is greatly appreciated, especially by someone like me. Priceless ❤️. One question please. I've replayed this several times but I can't hear what you said because you spoke rather swiftly there. It's rather unclear, muffled. @ 7:15, you said, " (___) ranges. What word did you say there?
Thanks for the feedback. I did try to use "R10C4" and it works but only for that column and not for the next/expanding columns. So ur right it does make sense to use COUNTA. Thanks again.
Thank you for this educative video. i have tried using it but got some problem extracting rate from difference rows and columns with the rate label horizontally and months vertically using the R1C1 style. Really need your explanation on that and i have tried combining the address function to do that but just can't get right.
Sorry to hear this. In the video, I use R10 for row 10 and the COUNTA function on row 10 to return how many columns there are. This creates R10C4, but in a dynamic way because of the COUNTA function. So, if more data is added to row 10, it is picked up as a new column.
I stumbled upon this video and tried to figure out why it is not working in my own workbook & table finally realising that you have set specific cells already in your workbook. Really I almost wasted my 60 mins figuring out the !REF error I was getting continuously only to realise that one has to fix ranges before using INDIRECT function. Without it, it is not working. Also I feel DSUM & DAVERAGE are better alternatives than INDIRECT for the first you covered in this video.
Is it possible to use an indirect function with relative cell references? I.e., if I want to do a SUMIFS function across multiple tabs with the function removing from left to right to cover a range of years (e.g., the sum range would have only the row locked in order to allow for the formula to move across variable sum ranges per year)
Need some help please (not entirely sure how to articulate what I'm aiming to do but here it goes). I have a spreadsheet with ~750 sheets, each named Sheet1, Sheet2, Sheet3, etc., through to Sheet750. In each sheet, I have data points relating to individual properties, that are all in the same/uniform format (or ranges). For example, in each sheet, cell A5 contains the property's Street Address, A12 contains Email address. What I need to do, is create a single table in a "Summary Sheet" that pulls the data points from each separate sheet, into that one summary table. So, for example, Column C in the summary table is Street Address. I want to be able to pull cell A5 from Sheet1, in the first row, cell A5 from Sheet2 in the next row, cell A5 from Sheet3 in the next row, and so on through to the 750th row, which would be cell A5 from Sheet750. It seems as though I need to use the INDIRECT function, but I can't quite figure out how to structure the function/formula to do this. Can you help? Thanks.
Hi Derek, On the Summary sheet. Enter 1 into a cell, 2 into the cell below. Select the both and copy down to 750. Excel will pick up the series to number 1 to 750. You can do this in any column, and hide it if you want. Then use this formula. I have start my numbering from cell A1. =INDIRECT("Sheet"&A1&"!$A$5") Copy this formula down and it will pick up each property address.
Wawww. Superb explanation. Sir, I need one help. I have tried INDIRECT function with DROP DOWN for very big lists. But, when I open the drop down list it is showing the elements in bottom most portion in the begining. How to modify this drop down to make it show the top elements? Thank you once again.
Hi Great Video (as they all are) but please can you explain why in INDIRECT to reference other worksheets when you wrote the second part of the Indriect after the & you put " in then not again until after the range of cells?? Sorry if I am being thick but I though " " were to refer to text. Thanks
Hi Debbie, you are right the " do refer to text. INDIRECT takes this text which we build and converts it to a reference to something - sheet reference, named range, table name etc.
I want add two more value under Canada, that means Dynamic Name Range and want to get effect on Office. I have create Dynamic Name Range using OFFSET(), but getting #REF error when I apply it with INDIRECT(). Please....
Hi Alan , thanks a lot for your great tutorial ! But I still not understand the part of 7:25 , the formula =sum(indirect("'"&B3&"'!C4:C11")) , epecially this part "'"&B3&"' , hope you can teach me again
So today i got to look at INDIRECT + R1C1 + COUNTA. That was really good. Never seen all that being used at one go. Very interesting if u need to fetch the LAST VALUE for anything. Thank u very much for sharing.
Watched the first 3 mins - INDIRECT with NAMED ranges. Pretty kool. Thank u. Instead of typing in Caridiff or Liverpool etc u could also use Data Validation ??
Hello Sir, A B C 1.20 40 30 2.(10) 50 [60 3. 20 30 20 4. 50 10 10 5. 60 10 80 6. 30 (90) 50] 7. 20 20 70 In above eg. A2 has minimum value & B6 has maximum value, so from this range 2-6 I want sum of column C2-C6 (60+20+10+80+50). so from the 1st column minimum value cell to 2nd column maximum value cell, I want sum of in-between cells of column C. I have 1000s sets each set of 7 of 3rows with 3 columns to execute in this manner. I m providing one more eg. so that u have better understanding. A B C 1. 50 30 30 2. 60 20 40 3. (30) 10 [50 4. 90 80 60 5. 70 (90) 30] 6. 50 70 20 7. 40 40 10 from min value of column A3(30) to max value B5(90), sum all cells of column C, C3 to C5 that is [50+60+30]. Please help with this question. Thank You.
Great video. However, when I tried to name the ranges in the "INDIRECT and VLOOKUP" worksheet, excel jumps to the names used in "With Named Ranges" worksheet. I've discovered you need to make initial Named Ranges limited to worksheet not workbook first. I couldn't change in Name Manager>Edit as it is greyed out.
Yes by default named ranges apply to the Workbook. This is normally a good and useful thing. So we need to ensure the named ranges have a unique name. If the named range applies to just the worksheet. You may need to be on that sheet in order to edit it.
Thank you for making video. This function doesn't look very useful. By the time you have keyed everything in and checked the formulae you could have just used the sum function. There is a use for this function but it looks minimal at best.
Using INDIRECT enables us to create dynamic references from what users may enter into cells, going beyond SUM capability. So a user could change what sheet, or range a formula sums from a simple drop down for example.
I'd really like to use the INDIRECT function to make dependent drop down lists but the selected cell references I need to use (customer name) in the first drop down list has spaces. I can't make a named range for the subsequent drop down lists with spaces. I'm very new to Excel. Do I have any other options?
@@Computergaga WOW, thank you so much. Just watched it. HaHa, you wouldn't believe the solution I came up with between the time I posted my initial comment to you and coming back here to see you had replied with a link to a very excellent solution. Thanks again, I greatly appreciate you replying to me!
@@Computergaga I do not mean to exhaust your kindness in regards to responding to me but I have what I think is a simple question for you to answer if you have time. I am using =LOOKUP(2,1/(A:A""),A:A) to get the value of the last cell in column A on the sheet. It works great as long as the formula is on the same sheet. Can this formula be used to refer to a column on a sheet that it is not entered on? Any recommendation or videos you have that I can utilize for this purpose? Thank you again, very amazing, thoughtful content!
Solomon Kinyanjui 1 second ago Hi Alan? 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?
I'm not aware of there being a problem with this Solomon. As long as the name of the dynamic named range and the items chosen from the list match up, INDIRECT will work.
hi, COMPUTERGAGA. Ii have an excel with various sheets. In each sheet, I have a same text in a cell and this text has different references. can I pull all that data from different sheets and put it in a column by entering that text
This is a masterpiece, Sir!..... Agreed you have taught the world of this most useful function called , INDIRECT, I learnt in a very simple way, you voice is clear and audible plus has a very good command on the language plus the enthusiasm throughout the video was overwhelming.. Sir! Thanks a ton.... From India
Thanks a lot for your explanations of all the ways of the Indirect function.. it is really useful and helpful. Actually, before you published this video I have an issue when making a drop-down list by using an Indirect Function. I followed all the steps you gave, but unfortunately it still not working with me and it always ends up with a message saying( The Source currently evaluates to an error, Do you want to continue?) Could you please help me with that because I really need to apply it ASAP? Thanks a lot,
That error does not necessarily indicate a problem. You get that message when the cell that INDIRECT is looking at is empty. Try continuing and populating that cell. I can't advise further without knowing more.
An analyst at work used this a few years back for a spreadsheet I use daily, I always wondered over it, now finally taking the time to understand it. Thank you for this vid!!!
Hello Sir Computergaga, How are you? Happy New Year. I found this video and was extremely excited to learn the format(s) 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
Cool video. Can INDIRECT be used to pass a string reference to a ROW function to display the row (I mean absolute row nmbr) number of some value? In example if I had "computergaga" in cell "C8", how would the function look like, which would return 8 in some other cell, lets say "F120". ???
F120 would use a lookup formula for the value "computergaga". Either INDEX or VLOOKUP or others. Wrap it in ROW to return the row when it is found. INDIRECT converts a reference from the string, it cannot look for a cell value.
Hola como hago para conocer un precio si tengo 2 dropdown menus , para un productos que tiene varios tipos de licenciamiento como tabs, entonces la formula tiene que revisar el producto y la licencia para luego darme el valor exacto
brother it is showing reference but why?????? my formula is correct according to your guide lines for calculation of indirect formula where this is =sum(indirect(my cel no#))----the answer is showing REF. Can u solve for me or mail me brother or reply me for the solution plzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
There is a mismatch between the cell value being used by INDIRECT and what it is referencing (sheet name, named range etc). Excel is telling you there is nothing by that name.
In this formula =INDIRECT("Sheet"&A1&"!$A$5") , What if I need cell A5 as variable "A5 , A6, A7" from different sheets . What will the formula look like . Thanks in advance
If you need to find the value becasue it might be A5, A6 or A7 you will need a way of finding it. What that is will depend on your scenarion. Maybe VLOOKUP or maybe INDEX.
Thank a lot. This was a great video, as per my understanding, in 2nd example of indirect, we have different sheets, however, data should be same column, it' ok. However, if i have different sheets & data in different columns , then , how would apply indirect function? Is it possible through indirect? could you assist me? Thank you so much, taught us indirect function in excel. It would be great appreciated, if you upload more examples based on indirect with index, match & another functions. I hope so, you will provide us in future. Thanks, Sanjay.
Definitely possible with INDIRECT but it would need some help. It can refer to the sheet as in my video but then would need assistance from functions such as MATCH to find which column to sum. We would use MATCH to find the column using its header. I'll try and produce a video on this soon.
