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.
Yep, absolutely, I shy away more and more from VBA these days if I can. More options to run on web, avoid security warnings etc. I used to live in VBA but not so much anymore.
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.
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 👍
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)
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?
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.
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!
Maybe capture the slicers in a pivot table containing exactly the same field as the slicer and reference that, or even create slicer from data model and turn the linked pivot into cube formulas
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.
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.
Thanks, indeed VBA can solve a lot. The additional challenge there : - knowing how to write robust VBA - organisations banning VBA - inability to work online I used to be writing VBA all day every day. My work needs and technology changes have meant I rarely touch it these days.
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.
As I mentioned in the video you could indeed do it that way - especially in this simple scenario. However in a few recent real life requirements this was the better solution,
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 :(
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
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.
Agreed, I can see how there would be difficulties in managing the process and programming it successfully. The Excel team are open to us giving useful examples of where we’d use this. Maybe one day this will be a reality.
> 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.
@@AccessAnalytic with the amount of money Microsoft are getting for excel per year I'm quite surprised generally by the lack of progress with the application honestly... Something like dynamic tables should be trivial
@@Sancarn to be fair they have been focusing on Excel for Web, while introducing the concept of Dynamic Array Excel and in-cell data types. I have a list of 10 things that I would like to see implemented before DA working with tables, and I'm sure 10 other people have 10 different needs. It's a tough job keeping us happy!!
Awesome solution and all these short-cut tips are the cherry on the cake 😊
Cheers!
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.
Yep, absolutely, I shy away more and more from VBA these days if I can. More options to run on web, avoid security warnings etc. I used to live in VBA but not so much anymore.
@@AccessAnalytic Valid points
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.
Cheers John 😁
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.
Great to know thanks for taking the time to leave a kind comment
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 👍
Cheers Martin
Interesting workaround Wyn.
Cheers Grainne
Very useful, I just came across the problem a few days ago. Thanks for the instructions
You’re welcome. Thanks for taking the time to leave a kind comment
Very useful. Thanks Wyn.
You’re welcome Lorenzo
Beautiful content, sr.
Thank you
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 Glyn
Great video once again. Is there a way to just have the show show slicer without the dash next to it please
At 10:09 I add the dash. You can choose anything ( must have at least 2 though )
Im in love with your content videos 💯👍
Thanks Ali !
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?
We’ve had discussions Geert 😀. Me and others will continue to flag use cases to try and encourage a change
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?
A nice idea
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.
Not that I know of, but it’s useful to have incase you want to show all rows ( Ctrl select both )
Very smart!
Cheers 😀
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!
Maybe capture the slicers in a pivot table containing exactly the same field as the slicer and reference that, or even create slicer from data model and turn the linked pivot into cube formulas
which video editing and screen recording software are you using? the video looks so clean.
Camtasia
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.
Thanks for the comment
Great content 🙌🔥🙏
Cheers Frank
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.
Thanks, indeed VBA can solve a lot. The additional challenge there :
- knowing how to write robust VBA
- organisations banning VBA
- inability to work online
I used to be writing VBA all day every day. My work needs and technology changes have meant I rarely touch it these days.
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?
With VBA most likely yes. Not otherwise
Superb!!
Cheers Vishal
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.
As I mentioned in the video you could indeed do it that way - especially in this simple scenario. However in a few recent real life requirements this was the better solution,
good trick that worked a year ago now, is any updates that help automatic expand & shrink to fit Dynamic array new results ?
Not that I’m aware of
Awesome👍🏻👍🏻
Thanks Kebin
is there a way of bringing up the available named ranges (F3 in data validation source) for mac?
Hi, I don't know about Macs sorry. I'd suggest posting the question here techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat
Wow.., very useful, let me know, Wyn What office is it ? Thanks anyway
Dynamic Array Excel is the 365 version and Excel 2021
Hidden gem❤
Glad you liked it 😀
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 :(
Not something I've played with Olivier, still seems a Work In Progress to me
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
Yes the Table needs to be manually adjusted or make it WAY bigger than your data at the beginning.
Then what is the purpose of referencing a dynamic array in a table?
Because Dynamic Arrays Calculations are very powerful and I want to use the results in a Table for its features
Thanks. Then what is the difference between this and a slicer controlled pivot table please?
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💙
I often reply to posts on slack www.reddit.com/r/excel/s/ov3ERlqAFZ
Genius
😊 cheers
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.
Agreed, I can see how there would be difficulties in managing the process and programming it successfully. The Excel team are open to us giving useful examples of where we’d use this. Maybe one day this will be a reality.
Good stuff but I have to admit, making the drop list cell look "3D'ish" was a great little tip.
Cheers 😀
> 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.
Thanks, having Tables work with Dynamic arrays is on the wish list but I don’t see it happening soon
@@AccessAnalytic with the amount of money Microsoft are getting for excel per year I'm quite surprised generally by the lack of progress with the application honestly... Something like dynamic tables should be trivial
@@Sancarn to be fair they have been focusing on Excel for Web, while introducing the concept of Dynamic Array Excel and in-cell data types.
I have a list of 10 things that I would like to see implemented before DA working with tables, and I'm sure 10 other people have 10 different needs. It's a tough job keeping us happy!!