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

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

[@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. 👍

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

[@gunjansharmaz]

@Mark you are a Genius.

[@ExcelOffTheGrid]

Wow thanks 😁

[@overengineeredinoz7683]

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

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

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

[@viktorasgolubevas2386]

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.

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

[@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. 👍

[@LegendScroller]

I liked #4 that one was my 💡 moment.

[@ExcelOffTheGrid]

Great stuff - hopefully you can put it to good use.

[@tibibara]

Very useful tutorial, thanks!💯

[@ExcelOffTheGrid]

Glad it was helpful!

[@eduardomunoz2764]

Brillant explanation, Mark. Thank you very much.

[@ExcelOffTheGrid]

Thank you. I'm glad it was useful.

[@arbazahmad7177]

Excellent....🎉 thanks for sharing

[@ExcelOffTheGrid]

My pleasure 😊

[@mohsenabdelbaset]

Very nice thanks Mark

[@ExcelOffTheGrid]

Thank You, I'm Glad you enjoyed it.

[@waitplanwp4129]

big hugs, love you ❤

[@ExcelOffTheGrid]

Wow! Thanks. 😁

[@jerrydellasala7643]

Very nice!

[@ExcelOffTheGrid]

Thanks Jerry 😁

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

[@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?

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

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