# How to make 'dynamic' Table to be auto-updated when size of another Table changes?



## gifariz (Dec 29, 2022)

I'm new to excel Table. I wonder if it is possible to make a Table to be auto-updated when size of another Table changes?

Here is my case below, please see in screenshot (I make it simplified for this question).
Excel file is uploaded here: Dynamic Table

I have 3 Tables: Properties, ElementA, ElementB.
First and second tables are have input data (yellow header).
But size of third table actually depends on unique values of a column in second table (ElementA[Name]), thus size is dynamic.
What I want is to do is like ElementB[Name] = UNIQUE(ElementA[Name]), but its not possible since Table cannot deal with dynamic array formula output.
(In the excel, I temporarily copy paste result from UNIQUE formula to ElementB[Name] for making this example).

I also cannot do all calculation of third table in second table, because real data in second table has >100,000 rows (too slow), so I need to do filtering of second table for further calculation in third table.
If I make pivot table from second table to do filtering, I still don't know how to make the third table from this pivot table.

Is there workaround which is automatic (since copy paste UNIQUE formula output is not automatic)?
Hope it is understandable.
Thank you in advance.


----------



## RoryA (Dec 29, 2022)

Why does the third table need to be an actual table?


----------



## gifariz (Dec 29, 2022)

RoryA said:


> Why does the third table need to be an actual table?


Because it is easier to do column-based calculation (writing and understanding the formula) with Table column key. Isn't it the purpose of structurized Table?
Someone suggested me Table-based analysis is better in my previous post.


----------



## RoryA (Dec 29, 2022)

That's what happens when you don't provide context for your questions. 

Using spilled arrays gives you the benefit of dynamic ranges without needing tables, unless you plan to use the results in Power Query (in which case you should probably be doing all of it in PQ).


----------



## gifariz (Dec 29, 2022)

RoryA said:


> That's what happens when you don't provide context for your questions.
> 
> Using spilled arrays gives you the benefit of dynamic ranges without needing tables, unless you plan to use the results in Power Query (in which case you should probably be doing all of it in PQ).


Thank you for your response.

I have been using dynamic array so far, but Table has some benefits that I like more:
- I can write formula with table column key instead of cell name, better readibility. Although LET function also can be used with dynamic array for naming the variables, I feel using column key is easier.
- If I have an input change of one row, no need to re-calculate the whole rows which dynamic array does. Better performance. I feel excel becomes lighter also.

So I guess no solution for my question right?
I have to come back to dynamic array, or make macro to automate copy paste for updating third table.


----------



## RoryA (Dec 29, 2022)

You could use power query to create the third table or a macro. Personally I would use dynamic arrays.


----------



## gifariz (Dec 29, 2022)

RoryA said:


> You could use power query to create the third table or a macro. Personally I would use dynamic arrays.


Oh okay, I will try and see if using PQ to update third table (just to update first column from Unique function result) is easier than macro. Thank you.


----------

