Referencing Dynamic Arrays with Tables

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

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

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

Very smart!

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

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

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!

Beautiful content, sr.

Hidden gem❤

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

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.

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 :(

Superb!!

Im in love with your content videos 💯👍

Great content 🙌🔥🙏

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?

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 👍

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?

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.

Interesting workaround Wyn.

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)

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.

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.

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?

Awesome👍🏻👍🏻

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

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.