Using dynamic arrays in a Table : 4 methods | Excel Off The Grid

[@ExcelOffTheGrid]

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

[@bilalsheikh9127]

Wow incredible techniques Mark. Superb.❤

[@RichardJones73]

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

[@ExcelOffTheGrid]

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. 😁

[@barkingkate]

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! 🙂

[@ExcelOffTheGrid]

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. 😁

[@barkingkate]

@@ExcelOffTheGrid Yes, I'd seen your reply elsewhere, and it makes sense. Thank you 🙂

[@roboats5685]

I love this channel. Well-organized presentation with clear examples and clever solutions.👍

[@ExcelOffTheGrid]

Thank you - that's really kind of you to say. 😁

[@TP014563]

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.

[@ExcelOffTheGrid]

Yep, there are always new things to learn. 👍

[@patrickschardt7724]

Wow this is incredible That row/ index: trick is amazing

[@ExcelOffTheGrid]

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.

[@IvanCortinas_ES]

Brilliant explanation, Mark. Thank you very much for sharing the different options. It is a very interesting topic.

[@ExcelOffTheGrid]

Thanks Ivan - hopefully there are some interesting techniques in there that you can apply at some point.

[@stefankirst3234]

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...

[@ExcelOffTheGrid]

Thanks 😁. Method #2 is good, but if your workbook becomes slow, that it is the bit I would look to change.

[@eduardomunoz2764]

Brillant explanation, Mark. Thank you very much.

[@ExcelOffTheGrid]

Thank you. I'm glad it was useful.

[@tibibara]

Very useful tutorial, thanks!💯

[@ExcelOffTheGrid]

Glad it was helpful!

[@GeertDelmulle]

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 ;-)

[@ExcelOffTheGrid]

Nice idea. I like it. 👍

[@mohsenabdelbaset]

Very nice thanks Mark

[@ExcelOffTheGrid]

Thank You, I'm Glad you enjoyed it.

[@arbazahmad7177]

Excellent....🎉 thanks for sharing

[@ExcelOffTheGrid]

My pleasure 😊

[@gunjansharmaz]

@Mark you are a Genius.

[@ExcelOffTheGrid]

Wow thanks 😁

[@jerrydellasala7643]

Very nice!

[@ExcelOffTheGrid]

Thanks Jerry 😁

[@overengineeredinoz7683]

Very nice video of advanced skills. Thankyou. I have subscribed.

[@waitplanwp4129]

big hugs, love you ❤

[@ExcelOffTheGrid]

Wow! Thanks. 😁

[@viktorasgolubevas]

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]))

[@ExcelOffTheGrid]

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.

[@dannywood7450]

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?

[@ExcelOffTheGrid]

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.

[@dannywood7450]

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.

[@dannywood7450]

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.

[@LifeIsLife1978]

Cool but how can I use it in regular work in office?

[@ExcelOffTheGrid]

Well... that's up to you and if it's relevant for the work you do.

[@ismaelkourouma5558]

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.

[@ExcelOffTheGrid]

Yes that will work. We can use any function that returns a single value.

[@codybehrens]

Wouldn't =sort(@[Names]) work?

[@Dexter101x]

Any python tutorials in excel? I updated excel and now it has a preview of python in excel

[@ExcelOffTheGrid]

Not from me at the moment, but maybe at a future point.