Excellent explanation and walkthrough. Thank you so much for teaching this lesson to people like me who are trying to understand the complexities of Excel. :)
I know this is a few years old but let's hope for the best. I used this formula and it was successful up to a point. I have 48 items in my range but it only finds 46, if i add one it will find the 47th one but it won't find the 48th and 49th. in your example it would be like your range is France through Venezuela, but it only showed you France through Spain, then when you added Italy it would show you France through UK.
I discovered the problem. there were two blank rows above the first bit of data, (A1 and A2 had column headers, A3&4 were blank, and A5 was the first data in the range). i used =$A$5:INDEX($A:$A,COUNTA($A:$A)).
Good technique!👏 However, everytime, we can not be able to use this formula. For example, if we erase a data inside of range, index formula will give us false result because of counting of rows. And, For the second example, if we insert a column for this table, vlookup will give us false result. But, Index():index() formula is so great and extraordinary function and so useful. Thank you
Computergaga dear sir. i have some worksheet it's really needy to my daily work. if you send your email address I'll share the file. actually I don't no how my superior did the worksheet he not ready to teach me. pls show me how he did the worksheet.
Works well, thank you! However, when I try to use the named reference as an INDIRECT function it doesn't seem to work. I have a drop-down list in Column A and then a dependent drop-down list in Column B. Column B is an INDIRECT function of the value in Column A, which references the name of the range with the INDEX function, but this evaluates to an error. Please help!
Great tutorial so far! Really decent explained! BUT, got one question, what in a case my range isn't whole column (not $A:$A), but like 5-10 rows (A1:A10..) within one column and how to add some of them but to be shown (auto updated) in data validation list??
How could you go about if you needed a City range, based on the country. B2: France countries, B3: Germany countries, etc, and if the A:Column moves, the b:Columb accommodates to it. Thanks in advance!
There are a few different techniques depending on what you need the list of cities for. You could use something similar to what I show in this video - ruclips.net/video/R5lmT-7m8aQ/видео.html Here I get VLOOKUP to look in a different range depending on a cell selection much like your question on city range dependent upon country selection. You just may not need the VLOOKUP. You can also check out this situation where I have a dependent drop down list. The second list is dependent upon selection from the first - ruclips.net/video/yychXPx2gUY/видео.html
At the 7:00 mark you said that you are after the row number and the column number, What I dont understand is why did you use CountA($A:$A),column, first instead of ($1:$1) row. Can i use them interchangeably or did i go wrong somewhere?
Hi Kamran, yes I believe this works with INDIRECT and drop downs. You can also making a range dynamic by formatting it as a table and then naming that range.
I was trying to use a range defined that way together with sum function, but it returned #VALUE. Basically the syntax is =SUM(MyRange), where MyRange is the range defined in the Name Manager. This works if the range is static, but it doesn't work if the range is defined with the INDEX function. Is there a way to make this work, or maybe to use another function to define the range?
The INDEX function should not be a problem here. The #VALUE error is normally an indication of incorrect formatting especially with sum. Typically there is text in the range to be summed. When you said that it works without the named range, that confused me somewhat.
@@Computergaga The named range actually works, but if it is defined as a static range, tried it both ways. If it is defined as static range, SUM function works, if it is defined as a dynamic - it doesn't. This makes me think, that the problem is not in the values of the cells. Works fine for validation list though. Edit: I tried this on a blank sheet, it works just fine. Something is wrong with my other document, I'll try to sort it out. Thanks for help anyway
Excellent explanation and walkthrough. Thank you so much for teaching this lesson to people like me who are trying to understand the complexities of Excel. :)
Your welcome, thanks.
Great Tutorial! Clear, concise and beautifully executed. Couldn't ask for more and exactly what I needed. Thank you Alan.
You're welcome. Thank you very much David.
I know this is a few years old but let's hope for the best. I used this formula and it was successful up to a point. I have 48 items in my range but it only finds 46, if i add one it will find the 47th one but it won't find the 48th and 49th. in your example it would be like your range is France through Venezuela, but it only showed you France through Spain, then when you added Italy it would show you France through UK.
I discovered the problem. there were two blank rows above the first bit of data, (A1 and A2 had column headers, A3&4 were blank, and A5 was the first data in the range). i used =$A$5:INDEX($A:$A,COUNTA($A:$A)).
Well done Charles.
Thanks Alan, great tutorial as always very clear and helpful
I heard named ranges with index are faster than offset. Only I recommend doing $A$1:$A$1000 as it's faster than counting the whole column
Yes OFFSET is a volatile function and can slow a workbook down. As long as the range you use is big enough you can use what you wish.
Good technique!👏 However, everytime, we can not be able to use this formula. For example, if we erase a data inside of range, index formula will give us false result because of counting of rows.
And,
For the second example, if we insert a column for this table, vlookup will give us false result. But,
Index():index() formula is so great and extraordinary function and so useful.
Thank you
Thank you, Emre. Yes, INDEX():INDEX() is awesome. For the first approach, instead of counting the rows, the ROWS fucntion could be used.
Concise - thanks
You're welcome! Thank you.
Many thanks. Much appreciate your work
You're welcome, thank you.
thanks for helping me. god bless u sir.
Your welcome Abu
Computergaga dear sir. i have some worksheet it's really needy to my daily work. if you send your email address I'll share the file. actually I don't no how my superior did the worksheet he not ready to teach me. pls show me how he did the worksheet.
That's a lot of consultancy Abu.
Dr. Rashid Baloch thank u so much. well how would I send the file to you sir.
hi, a question.
why would you not use an excel table, since it expands and contracts automatically without formulas?
it crossed my mind too
Thanks, Awesome Video!!!
works, thanks!
You're very welcome, Leonardo.
nice and awesome please indirect function advance tutorial
Thanks. Coming soon Jacky
You solved my problem. Thank you. :)
You're very welcome Sam.
thumbs up! Very helpful
Thank you Stoyan.
Works well, thank you! However, when I try to use the named reference as an INDIRECT function it doesn't seem to work. I have a drop-down list in Column A and then a dependent drop-down list in Column B. Column B is an INDIRECT function of the value in Column A, which references the name of the range with the INDEX function, but this evaluates to an error. Please help!
Great tutorial so far! Really decent explained! BUT, got one question, what in a case my range isn't whole column (not $A:$A), but like 5-10 rows (A1:A10..) within one column and how to add some of them but to be shown (auto updated) in data validation list??
You can use the same technique and use that range instead of A:A.
@@Computergaga Well, I tried exactly in your example and it didnt work. I added new row but no updates on data validation.
Is the Dynamic Named range specific to a Tab? Or how do we make it specific to one tab? What if we have Dynamic Ranges for multiple Tabs?
How could you go about if you needed a City range, based on the country. B2: France countries, B3: Germany countries, etc, and if the A:Column moves, the b:Columb accommodates to it. Thanks in advance!
There are a few different techniques depending on what you need the list of cities for. You could use something similar to what I show in this video - ruclips.net/video/R5lmT-7m8aQ/видео.html
Here I get VLOOKUP to look in a different range depending on a cell selection much like your question on city range dependent upon country selection. You just may not need the VLOOKUP.
You can also check out this situation where I have a dependent drop down list. The second list is dependent upon selection from the first - ruclips.net/video/yychXPx2gUY/видео.html
At the 7:00 mark you said that you are after the row number and the column number, What I dont understand is why did you use CountA($A:$A),column, first instead of ($1:$1) row. Can i use them interchangeably or did i go wrong somewhere?
I used CountA($A:$A) first to retrieve the last row number in column A. You cannot use them interchangeably.
If I want to name all of column A, is there a reason to avoid making the range equal to $A$1:$A$1048576?
So if I add additional column it will expand automatically without errors?
Sure. The second is dynamic in both directions. Formatting your data as a table is another way.
Dear alan
Please advice did this method worked with droupdown and indirect function i try but I didn't get any results
Dear alan
Please advice if this methods worked with droupdown and indirect functions
Best regards
Kamran pouryavari
Hi Kamran, yes I believe this works with INDIRECT and drop downs. You can also making a range dynamic by formatting it as a table and then naming that range.
Hey, it doesnt work in excel 2016. Data validation "the source must be a delimited list, or a reference to a single row or column"
Check the video again to try and spot the problem. The video was done in 2016 but will work in any version.
Would the number of columns of the new dynamic range not be 5, instead of 3?
This is true. Does not affect the VLOOKUP in the example, but you are correct.
@@Computergaga Thanks for replying! Great video.
I was trying to use a range defined that way together with sum function, but it returned #VALUE. Basically the syntax is =SUM(MyRange), where MyRange is the range defined in the Name Manager. This works if the range is static, but it doesn't work if the range is defined with the INDEX function. Is there a way to make this work, or maybe to use another function to define the range?
The INDEX function should not be a problem here. The #VALUE error is normally an indication of incorrect formatting especially with sum. Typically there is text in the range to be summed. When you said that it works without the named range, that confused me somewhat.
@@Computergaga The named range actually works, but if it is defined as a static range, tried it both ways. If it is defined as static range, SUM function works, if it is defined as a dynamic - it doesn't. This makes me think, that the problem is not in the values of the cells. Works fine for validation list though.
Edit: I tried this on a blank sheet, it works just fine. Something is wrong with my other document, I'll try to sort it out. Thanks for help anyway
When I use this in SUM & If array, I get N/A error. Why?
I would need to see more detail. SUM can absolutely be used on a range returned by INDEX.
What about offset,index and counta function is that work?
You can use OFFSET instead of INDEX if you wish.
@@Computergaga Sir, OFFSET and COUNTA function is the best combination for dynamic range.
@@mohammedsalwan382 👍