Referencing Dynamic Arrays with Tables 

Awesome solution and all these short-cut tips are the cherry on the cake 😊

Really good practical topic Wyn. Dynamic arrays are awesome but don't format dynamically, so your Table workaround gives a good solution. And the Grouping controls to the right setting, you would have heard my groan from here, never knew that one.

Very smart!

Since using unique I have wanted to use in a table. This is great can't wait to try it out. Thanks for sharing this.

Very useful. Thanks Wyn.

Thanks Wyn. Interesting approach. I typically do something similar but with a little VBA tied to the Worksheet_Change event and your SelectedWorkOrder you can simply clear the filter and apply the new filter based on your selection. Thus no buttons requiring the user to click additional things, no helper columns, etc. Much cleaner but does require a little VBA.

Interesting workaround Wyn.

Very useful, I just came across the problem a few days ago. Thanks for the instructions

Im in love with your content videos 💯👍

Beautiful content, sr.

Interesting approach Wyn, I use dynamic arrays all the time and I use conditional formatting to make them appear as tables, I understand that’s not what your doing here as you want to utilize table behaviour and keep it as a table. One to keep in the memory bank 👍

Great content 🙌🔥🙏

Genius

Hidden gem❤

Awesome👍🏻👍🏻

Superb!!

There is a way to automate this completely! If you write an object-based event operation macro for that worksheet, you can run a macro that would do the filtering for you if the value in your drop-down menu changed. And, in fact, you wouldn't even need your helper columns because all of those calculations could occur in the macro. In addition, you could build into the macro the automatic resizing of the table to eliminate the need for all of the blank rows in the first place. This way of doing things, there are 0 extra rows and 0 helper columns, leading to much smaller file sizes and a 100% automated table update when selecting from the drop-down. Same concept, better result. Does require VBA though.

Cool video learnt a lot for sure, I think it may be possible to for go one of the helper columns for hidden rows it seems to work count the rows in a filter for only visible rows in the table less the count of rows that are visible, which shows a positive for hidden rows and a negative where rows that should be hidden are visible ROWS ( FILTER ( Table , Table Column Show ="Visible" ) ) less the AGGREGATE ( CountA , Ignore Hidden , Table Column Show)

Great video once again. Is there a way to just have the show show slicer without the dash next to it please

That was one of the great ways to include Dynamic Arrays in the Table. Tables help in many ways. However dynamic arrays and tables can't stay under the same roof 😅😅. Now Power Query supports dynamic arrays. We need to press the refresh button only. Dynamic Arrays will change the data in Powe Query. We can externally apply the SUMIFS function. Things will get automated and that too in Table Structure.

Hey Wyn, thanks for this tutorial. Currently I am struggling to do something if someone has an idea here, very similar to this above. So, in my dashboard, I would like to use the slicers (for instance the Year slicer), which will dynamically change my data. Data is filtered by the Filter function and stored in an Excel table 😁😁. The idea is to update the Dashboard without refreshing any Pivot tables. My issue is exactly at the opposite of this case, I want the the dynamic filter collapsing or expanding based on the Excel table drove by a slicer. Tricky!

Nice! Have you considered using conditional formatting on the table to make the rows with zeros “disappear” using ;;; for number formatting and remove the background shading?

Brilliant - some great techniques there, Wyn! Just one other thing re tidying up - can you hide the unneeded "-" slicer button (I presume you don't need this)? This is so you can only see the "Show" button.

Hi Wyn, in my experience I tend to gravitate towards dynamic arrays (DA’s) and not so much excel tables (ET’s). And your proces in reverse (ET=>DA) is trivial, and I do use that happily (I do like ET’s). OTOH, it would be great to be able to use spilling DA’s in ET’s, even if they were restricted to just column vectors, and in that capacity, they could very well replace the (uniform) column formula, a concept that already exists inside ET’s (default = blank). This sounds to me as the logical next step for increasing the interoperability of DA’s and ET’s. If this were available, your current proces would be equally trivial. :-) Perhaps -as an MVP- you can suggest this to the Excel Team?

which video editing and screen recording software are you using? the video looks so clean.

Thank you. Can you show how to create dynamic table with fixed number of rows. If the data is more then fixed rows (in table), then new table has to be created dynamically for spill over data. In short, I would like to know if we can create automatically new tables dynamically with fixed number of rows in table?

There was some nice tricks in the video. Why use the table at all? If you just reference the dynamic array in a sumif formula (=SUMIFS(tblData[Hours],tblData[Name],Summary2!F11#)) it'll do the same thing, without the need to create a table, show or hide rows.

good trick that worked a year ago now, is any updates that help automatic expand & shrink to fit Dynamic array new results ?

Another area where I've struggled referencing dynamic arrays is in OfficeScripts. More specifically I haven't been able to use the A1# type of reference via OfficeScripts. I'm not sure whether there's a syntax I'm missing that should work or maybe it's just not supported. OfficeScripts is under-documented :(

is there a way of bringing up the available named ranges (F3 in data validation source) for mac?

Wow.., very useful, let me know, Wyn What office is it ? Thanks anyway

king of working with dynamic reference and setsups I came a crossed a problem for dynamic resizing for a kind of setup is there any chance that I ask you that? is there any discord channle or s.th😅😅😅😅 I know you have tons to do but I just ask if there are any possibilities💙

Good stuff as a workaround but why-oh-why don't spill formulas and structured references just play nice? They are both individually awesome but not being able to spill formulae in tables is very, very vexing.

Also, is this workaround restricted to the number of rows that you created in the dynamic array reference table? Does it mean that we need to manually update the reference table range when the number of rows in the source data expands more than the number of rows available in the reference table please? Thanks

> It would be nice if the table just automatically expanded and collapsed based on the dynamic array, that currently isn't possible! You could quite easily sync this up actually using VBA events. ```vb Private Sub Worksheet_Calculate() Call resizeTable("Table3", Me.Range("D6#").Rows.CountLarge) End Sub Private Sub resizeTable(ByVal sTableName As String, ByVal iRows As Long) With Me.ListObjects(sTableName) If .ListRows.Count > iRows Then .Range.Offset(iRows + 1).Resize(.ListRows.Count - iRows).Value = Empty Call .Resize(.Range.Resize(iRows + 1)) End With End Sub ``` Ultimately here in the worksheet calculate sub we are saying resize Table3 to the number of rows in D6#. Simple yet effective.