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

★ Want to automate Excel? Check out our training academy ★

★ Get the example file ★
Sign up for our free Insiders program and get the example file:

★ About this video ★
If you have ever tried to put a Dynamic Array calculation into a Table you will get the #SPILL! error. So, in this video, we look at 4 workarounds for achieving this.

INCLUDES:
0:00 Introduction
0:16 Background
2:12 Synchronized Array
3:17 Value Selection
5:09 Collapsed Text
6:22 Column Name Selection
7:52 Wrap-up

★ Download 30 most useful Excel VBA Macros ebook for FREE ★

★ Where to find Excel Off The Grid ★

#MsExcel

[ExcelOffTheGrid]

Sign up for our free Insiders program and get the example file:
File Reference for this video: 0219 Dynamic Array in Table

[bilalsheikh]

Wow incredible techniques Mark. Superb.❤

[RichardJones]

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

[roboats]

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

[IvanCortinas_ES]

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

[TP]

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.

[patrickschardt]

Wow this is incredible

That row/ index: trick is amazing

[eduardomunoz]

Brillant explanation, Mark. Thank you very much.

[stefankirst]

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

[overengineeredinoz]

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

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

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

[dannywood]

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?

[ismaelkourouma]

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.

[LifeIsLife]

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

[Dexterx]

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