Referencing Dynamic Arrays with Tables

Поделиться
HTML-код
  • Опубликовано: 13 дек 2024

Комментарии • 78

  • @s1ngularityxd64
    @s1ngularityxd64 2 года назад +5

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

  • @KO1967
    @KO1967 2 года назад +7

    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.

    • @AccessAnalytic
      @AccessAnalytic  2 года назад +5

      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.

    • @KO1967
      @KO1967 2 года назад +1

      @@AccessAnalytic Valid points

  • @johnhackwood1568
    @johnhackwood1568 2 года назад +4

    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.

  • @jude6387
    @jude6387 2 года назад +1

    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.

    • @AccessAnalytic
      @AccessAnalytic  2 года назад

      Great to know thanks for taking the time to leave a kind comment

  • @martyc5674
    @martyc5674 2 года назад +3

    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 👍

  • @GrainneDuggan_Excel
    @GrainneDuggan_Excel 2 года назад +1

    Interesting workaround Wyn.

  • @dre5229
    @dre5229 Год назад

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

    • @AccessAnalytic
      @AccessAnalytic  Год назад +1

      You’re welcome. Thanks for taking the time to leave a kind comment

  • @lorenzoladejobi8701
    @lorenzoladejobi8701 2 года назад +1

    Very useful. Thanks Wyn.

  • @JAADAAZ
    @JAADAAZ Год назад +1

    Beautiful content, sr.

  • @teaking1981
    @teaking1981 2 года назад +1

    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)

  • @patrickkinbonso1809
    @patrickkinbonso1809 Год назад +1

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

    • @AccessAnalytic
      @AccessAnalytic  Год назад

      At 10:09 I add the dash. You can choose anything ( must have at least 2 though )

  • @alimoudden444
    @alimoudden444 2 года назад +1

    Im in love with your content videos 💯👍

  • @GeertDelmulle
    @GeertDelmulle 2 года назад +2

    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?

    • @AccessAnalytic
      @AccessAnalytic  2 года назад +1

      We’ve had discussions Geert 😀. Me and others will continue to flag use cases to try and encourage a change

  • @xlrobot
    @xlrobot 2 года назад +1

    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?

  • @iankr
    @iankr 2 года назад +1

    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.

    • @AccessAnalytic
      @AccessAnalytic  2 года назад +1

      Not that I know of, but it’s useful to have incase you want to show all rows ( Ctrl select both )

  • @TubeWatcher935
    @TubeWatcher935 Год назад +1

    Very smart!

  • @ismaelkourouma5558
    @ismaelkourouma5558 Год назад

    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!

    • @AccessAnalytic
      @AccessAnalytic  Год назад

      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

  • @askeijaz
    @askeijaz Год назад

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

  • @thegaminglearning
    @thegaminglearning 2 года назад +1

    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.

  • @FRANKWHITE1996
    @FRANKWHITE1996 2 года назад +1

    Great content 🙌🔥🙏

  • @michaelbrown4925
    @michaelbrown4925 5 месяцев назад

    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.

    • @AccessAnalytic
      @AccessAnalytic  5 месяцев назад

      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.

  • @nethikarnarendra4884
    @nethikarnarendra4884 2 года назад +1

    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?

    • @AccessAnalytic
      @AccessAnalytic  2 года назад +1

      With VBA most likely yes. Not otherwise

  • @MrWish332
    @MrWish332 2 года назад +1

    Superb!!

  • @steven2745
    @steven2745 2 года назад +1

    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.

    • @AccessAnalytic
      @AccessAnalytic  2 года назад

      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,

  • @minamishail_Tech2024
    @minamishail_Tech2024 Год назад

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

  • @kebincui
    @kebincui 2 года назад +1

    Awesome👍🏻👍🏻

  • @jassimone4266
    @jassimone4266 Год назад

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

    • @AccessAnalytic
      @AccessAnalytic  Год назад

      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

  • @puguhhariyadi3971
    @puguhhariyadi3971 6 месяцев назад

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

    • @AccessAnalytic
      @AccessAnalytic  6 месяцев назад

      Dynamic Array Excel is the 365 version and Excel 2021

  • @feipan3227
    @feipan3227 Год назад

    Hidden gem❤

  • @OlivierTravers
    @OlivierTravers 2 года назад

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

    • @AccessAnalytic
      @AccessAnalytic  2 года назад

      Not something I've played with Olivier, still seems a Work In Progress to me

  • @patrickkinbonso1809
    @patrickkinbonso1809 Год назад

    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

    • @AccessAnalytic
      @AccessAnalytic  Год назад

      Yes the Table needs to be manually adjusted or make it WAY bigger than your data at the beginning.

    • @patrickkinbonso1809
      @patrickkinbonso1809 Год назад

      Then what is the purpose of referencing a dynamic array in a table?

    • @AccessAnalytic
      @AccessAnalytic  Год назад +1

      Because Dynamic Arrays Calculations are very powerful and I want to use the results in a Table for its features

    • @patrickkinbonso1809
      @patrickkinbonso1809 Год назад

      Thanks. Then what is the difference between this and a slicer controlled pivot table please?

  • @sajjadabouei6721
    @sajjadabouei6721 7 месяцев назад

    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💙

    • @AccessAnalytic
      @AccessAnalytic  7 месяцев назад

      I often reply to posts on slack www.reddit.com/r/excel/s/ov3ERlqAFZ

  • @jaimesantana2834
    @jaimesantana2834 7 месяцев назад +1

    Genius

  • @slryt
    @slryt 2 года назад +5

    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.

    • @AccessAnalytic
      @AccessAnalytic  2 года назад +1

      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.

  • @davidferrick
    @davidferrick 3 месяца назад

    Good stuff but I have to admit, making the drop list cell look "3D'ish" was a great little tip.

  • @Sancarn
    @Sancarn Год назад

    > 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
      @AccessAnalytic  Год назад

      Thanks, having Tables work with Dynamic arrays is on the wish list but I don’t see it happening soon

    • @Sancarn
      @Sancarn Год назад

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

    • @AccessAnalytic
      @AccessAnalytic  Год назад

      ​@@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!!