Grow secondary table when additional data added to primary data

[Medhum]

I have a spreadsheet where I have imported a month electricity usage report for each month (Each Day is also broken down into 48 x 30 min periods) using Power Query and then created additional reports based on Day or Week or Month including rolling 7 day Average etc. The spreadsheet now takes several refresh clicks and over a minute for each refresh to complete. I decided to look at how I could reduce this and think about how I would do this if Power Query was not available. So hear is my question. I have the main imported table with all the raw data and want to extract all unique dates into a separate table however, all I seem to get is either the table will fill to the current length or I get #Spill Error. If I don't use a table, then something like the Unique Function will work as a dynamic array, or if I define the first and last Dates using MAX or Min Functions the the Sequence will also work as a Dynamic array. I would prefer to use tables as I like the way that formulas flow down. I would be grateful for any suggestions.

 

[Fluff]

to the best of my knowledge formulas alone will not add new rows to tables.

Agreed.

Perfect, I had tried some other functions with Unique, but not Index. Many Thanks

Glad we could help & thanks for the feedback.

 

[Medhum]

If you want the result table to resize automatically as new data is added to the source (as suggested by the title of the thread) then you need to remove the table to allow the spill ranges to function.

Fluff may well know something that I don't, but to the best of my knowledge formulas alone will not add new rows to tables.

Thanks, Yes I would like this results table to grow automatically, and I have checked, but can now see that the fill works, but it does not grow the table

 

[Fluff]

As Jason said, a formula cannot do that.
IMO, it would be best not to use a table.

 

[Medhum]

Many thanks for all your responses. It looks like I will have to use ranges after all.

With more checking the table won't shrink either

 

[jasonb75]

The downside or ranges is that not all formulas will spill, which is annoying when you want to aggregate your data.

@Fluff, are you aware of any methods that will force array resistant functions to spill? I did a bit of research a few weeks ago and found nothing, I do have a way to force sumifs (and similar) to spill parallel to an existing spill range but it doesn't work with sumproduct, which is one of the functions that I assume is going to be needed here.

 

[Fluff]

Some functions will spill by using the # symbol like

=SUMIFS(DataTable[Price / kWh],DataTable[Date],H6#)

But a lot wont, I have seen a couple of posts where people have used Mmult of Offset to get it to work, but not something I've ever done.

 

[jasonb75]

I hadn't thought of Mmult, Offset did come to mind but went to the bottom of the pile for being volatile.

I unintentionally found that

=IF(ROW(H6#),some formula)

appears that it will force anything to spill. Formula evaluation shows the expected results per row at the second to last step but the final step and cell result are always that of the first row in the spill.

Tried adding @ and # in a few other places with no change. I;m going to keep looking and will post anything that I find which might benefit the OP but I don't like to clutter other peoples threads with side discussions.