Grow secondary table when additional data added to primary data

Medhum

New Member
Joined
Jan 23, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Medhum

New Member
Joined
Jan 23, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,313
Office Version
  1. 365
Platform
  1. Windows
As Jason said, a formula cannot do that.
IMO, it would be best not to use a table.
 

Medhum

New Member
Joined
Jan 23, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,313
Office Version
  1. 365
Platform
  1. Windows
Some functions will spill by using the # symbol like
Excel Formula:
=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

Well-known Member
Joined
Dec 30, 2008
Messages
12,414
Office Version
  1. 365
Platform
  1. Windows
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
Excel Formula:
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,123
Messages
5,622,870
Members
415,935
Latest member
kes1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top