Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders-signup/ File Reference for this video: 0219 Dynamic Array in Table
I wish i had the talent to solve these on my own. Thanks, this has opened up a lot of possibilities for me. Big fan of dynamic arrays and tables and now i can use both simultaneously
I didn't know that some my work was way ahead of you, but then other methods I didn't know they were possible. This proves the saying "No matter how much you know something there will always be new things to learn about!". Thanks for sharing, really informative! Keep them coming.
Ingenious ways to think about this problem! And as always extremely well structured. Thanks a lot. I find method #2 particularly interesting - even if Excel has to sweat a little...
Good stuff, Mark! On the last example I go one step further and actually call the header row “headers” (as a defined name). Then it looks even more like a table ;-)
I love your videos, Mark. Thank you so much for your work! Re. the index function, what I find a simpler alternative (although still not efficient, of course) is INDEX(SORT([Name]),ROW()-ROW(Data[[#Headers],[Sorted 2]])) or INDEX(SORT([Name]),ROW()-ROW(Data[#Headers])) - although the latter throws an inconsistent formula error. Have a great Christmas break! 🙂
Yes, referring to the header row works, but I'm not a fan of that approach - the header row is not part of the data, so it gives us an "Excel mindset" rather than a "Data mindset". Which I know sounds like a random reason... but it's still my reason. 😁
Very useful maintenance techniques for "hybrid", tables+DA (+ranges) worksheets. Thanks a lot! Tried to feed another table (newData) with the sorted column using your approach. Not sure... =INDEX(SORT(Data[Name]),ROWS(INDEX(newData,1,):newData[@])) btw, i'd use =INDEX(SORT(Data[Name]),ROW(newData[@])-ROW(newData[#Headers]))
I've been able to use your formula with a table called newData and it worked. So I'm not sure what the issue is. I didn't use the ROW method because a header is not part of the data - so conceptually it doesn't feel right. But in the real world, I think it would be faster. So if speed were an issue, I would probably use that method.
Love it Mark. Challenge: I use method 1 to create a mirrored table from an AddIn download. It has a 3 tiered hierarchy which I sort unique filter into horizontal lists with named ranges for dependent validations. Is there a way to do this dynamically from the table without creating filtered lists?
Data Validation Lists do not calculate, they only reference. So you need to reference to another object which will calculate sort/unique etc. You can use a Dynamic Array into a cell, then use the spill range of that cell in the DV. Or you can use a Dynamic Array into a named range and use the named range in the DV. Either way, you have to use something else to trigger the calculation, you can't do it directly in the DV.
One thing I want to mentionne here is that if you don't use CHOOSECOLS formula to select the column you want sum, then Excel will automatically sum all the colums containing numbers in the Dynamic array formula 😁😁! That can be helpfull sometimes.
Your stuff is obviously advanced user "stuff". I like it, but an occasional "why" we are doing something would be nice. I figured it out, but you didn't exactly specify why the spill error occurred in column F. Had you explained the formula was spilling 3 values per row/caluclation onto itself, it would have explained "why" we are using textjoin. Not trying to be critical here, but I would get more out of some of your videos if you don't gloss over some things that me seem obvious to you. I like watching your advanced usage, but if I can't follow it, I will stop watching.
Thanks for the feedback. I've made a deliberate decision to "Demonstrate" on RUclips and "Teach" on courses. On RUclips videos need to be self contained because I have no idea if somebody will have watched a previous video or will watch the next video. Your "why" question, could be different to another persons "why" question. So, if I were to provide the possible "why" for each element, this video would have been well over an hour and would take over 45 mins before it even gets to the point of what the video is about. So it would be a long boring video which nobody would watch (including you 😁). In our training program, we can spend the time building up the basic knowledge over several hours. So that when we approach more advanced topics we can cover them easily minutes because it is the application of everything which came before. With courses, because everybody has followed the course, everybody has the same level of knowledge. If there are questions, participants can ask question and get support for applying it into their scenario. Also, when we re-record the courses we can look at the questions and understand which areas to develop further. Therefore we can answer individual "why" questions and in depth. So, while I appreciate you want to know "why". I can't guarantee that we can provide that here on RUclips. And, if that's not for you... that's fine. I appreciate you watching when you do.
Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-signup/
File Reference for this video: 0219 Dynamic Array in Table
I wish i had the talent to solve these on my own. Thanks, this has opened up a lot of possibilities for me. Big fan of dynamic arrays and tables and now i can use both simultaneously
Thankfully, we don't need to solve these on your own... there is a world of people all sharing ideas. And we all get to learn from each other. 😁
I love this channel. Well-organized presentation with clear examples and clever solutions.👍
Thank you - that's really kind of you to say. 😁
I didn't know that some my work was way ahead of you, but then other methods I didn't know they were possible. This proves the saying "No matter how much you know something there will always be new things to learn about!".
Thanks for sharing, really informative! Keep them coming.
Yep, there are always new things to learn. 👍
Brilliant explanation, Mark. Thank you very much for sharing the different options. It is a very interesting topic.
Thanks Ivan - hopefully there are some interesting techniques in there that you can apply at some point.
Wow this is incredible
That row/ index: trick is amazing
Thanks - I love INDEX it's so powerful. That method is useful in lots of places, so I'm sure you can use it in other scenarios.
Wow incredible techniques Mark. Superb.❤
Ingenious ways to think about this problem! And as always extremely well structured. Thanks a lot. I find method #2 particularly interesting - even if Excel has to sweat a little...
Thanks 😁.
Method #2 is good, but if your workbook becomes slow, that it is the bit I would look to change.
Brillant explanation, Mark. Thank you very much.
Thank you. I'm glad it was useful.
Very useful tutorial, thanks!💯
Glad it was helpful!
Good stuff, Mark!
On the last example I go one step further and actually call the header row “headers” (as a defined name).
Then it looks even more like a table ;-)
Nice idea. I like it. 👍
Very nice thanks Mark
Thank You, I'm Glad you enjoyed it.
I love your videos, Mark. Thank you so much for your work!
Re. the index function, what I find a simpler alternative (although still not efficient, of course) is INDEX(SORT([Name]),ROW()-ROW(Data[[#Headers],[Sorted 2]])) or INDEX(SORT([Name]),ROW()-ROW(Data[#Headers])) - although the latter throws an inconsistent formula error.
Have a great Christmas break! 🙂
Yes, referring to the header row works, but I'm not a fan of that approach - the header row is not part of the data, so it gives us an "Excel mindset" rather than a "Data mindset".
Which I know sounds like a random reason... but it's still my reason. 😁
@@ExcelOffTheGrid Yes, I'd seen your reply elsewhere, and it makes sense. Thank you 🙂
Excellent....🎉 thanks for sharing
My pleasure 😊
@Mark you are a Genius.
Wow thanks 😁
Very nice video of advanced skills. Thankyou. I have subscribed.
Very nice!
Thanks Jerry 😁
big hugs, love you ❤
Wow! Thanks. 😁
Very useful maintenance techniques for "hybrid", tables+DA (+ranges) worksheets. Thanks a lot!
Tried to feed another table (newData) with the sorted column using your approach. Not sure...
=INDEX(SORT(Data[Name]),ROWS(INDEX(newData,1,):newData[@]))
btw, i'd use
=INDEX(SORT(Data[Name]),ROW(newData[@])-ROW(newData[#Headers]))
I've been able to use your formula with a table called newData and it worked. So I'm not sure what the issue is.
I didn't use the ROW method because a header is not part of the data - so conceptually it doesn't feel right. But in the real world, I think it would be faster. So if speed were an issue, I would probably use that method.
Love it Mark. Challenge: I use method 1 to create a mirrored table from an AddIn download. It has a 3 tiered hierarchy which I sort unique filter into horizontal lists with named ranges for dependent validations. Is there a way to do this dynamically from the table without creating filtered lists?
Data Validation Lists do not calculate, they only reference. So you need to reference to another object which will calculate sort/unique etc.
You can use a Dynamic Array into a cell, then use the spill range of that cell in the DV. Or you can use a Dynamic Array into a named range and use the named range in the DV.
Either way, you have to use something else to trigger the calculation, you can't do it directly in the DV.
Thanks so much for your response. So kind. Was hoping there some crazy excel indirect magic I didn't know about. Absolutely dig your channel mate.
Thanks so much for your response. So kind. Was hoping there some crazy excel indirect magic I didn't know about. Absolutely dig your channel mate.
One thing I want to mentionne here is that if you don't use CHOOSECOLS formula to select the column you want sum, then Excel will automatically sum all the colums containing numbers in the Dynamic array formula 😁😁! That can be helpfull sometimes.
Yes that will work. We can use any function that returns a single value.
Cool but how can I use it in regular work in office?
Well... that's up to you and if it's relevant for the work you do.
Wouldn't =sort(@[Names]) work?
Any python tutorials in excel? I updated excel and now it has a preview of python in excel
Not from me at the moment, but maybe at a future point.
Your stuff is obviously advanced user "stuff". I like it, but an occasional "why" we are doing something would be nice. I figured it out, but you didn't exactly specify why the spill error occurred in column F. Had you explained the formula was spilling 3 values per row/caluclation onto itself, it would have explained "why" we are using textjoin. Not trying to be critical here, but I would get more out of some of your videos if you don't gloss over some things that me seem obvious to you. I like watching your advanced usage, but if I can't follow it, I will stop watching.
Thanks for the feedback.
I've made a deliberate decision to "Demonstrate" on RUclips and "Teach" on courses.
On RUclips videos need to be self contained because I have no idea if somebody will have watched a previous video or will watch the next video.
Your "why" question, could be different to another persons "why" question. So, if I were to provide the possible "why" for each element, this video would have been well over an hour and would take over 45 mins before it even gets to the point of what the video is about. So it would be a long boring video which nobody would watch (including you 😁).
In our training program, we can spend the time building up the basic knowledge over several hours. So that when we approach more advanced topics we can cover them easily minutes because it is the application of everything which came before.
With courses, because everybody has followed the course, everybody has the same level of knowledge. If there are questions, participants can ask question and get support for applying it into their scenario. Also, when we re-record the courses we can look at the questions and understand which areas to develop further. Therefore we can answer individual "why" questions and in depth.
So, while I appreciate you want to know "why". I can't guarantee that we can provide that here on RUclips. And, if that's not for you... that's fine. I appreciate you watching when you do.