Fixing #SPILL on Dashboard by auto-added new row when I add row to Table

[amcckey]

I have a dashboard that I populate with data from chunks of data I've named in the Name Manager. (Note the data is also in tables.) Everything is set up and working on the dashboard, but the problem is when I add a new row to the table (which thus gets included in the Named data), it screws up my dashboard and I see #SPILL. It's because I have data in the rows below so there isn't a blank row for my new row to be added...but...can I not tell Excel to  add a row for me? The #SPILL happens when I add data in cell A8 on the Implementation worksheet and then that new row can't get added to the dashboard on the Dashboard worksheet. How can I get that new row automatically added on the Dashboard to avoid the #Spill?

I have a feeling this is a super simple fix for Excel wizards...but this is a new area for me so trying to figure this out. I hope the screen shots are helpful. Thank you! :)

The formula in cell A2 on the Dashboard is =dashImplementation.

dashImplementation in Name Manager is:

tblImplemenation is:

This is what the Implementation worksheet (and Table) looks like and you can see I've typed in New data in cell A8

And then this is what happens to my dashboard. I would love for Excel to just auto-add a row so there is no #SPILL

[#2]

@amcckey 

I have a feeling this is a super simple fix for Excel wizards...but this is a new area for me so trying to figure this out. I hope the screen shots are helpful.

Sadly, screen shots -- despite the cliche about pictures being worth a thousand words -- are far less helpful than the real thing. So if my suggestion doesn't resolve it, since it looks like this is all dummy data anyway, why don't you post the real thing on OneDrive or GoogleDrive, and paste a link here that grants edit access.

But here's my suggestion: reverse the sequence of items on your Dashboard. Move the formula that produces a dynamic array -- that incurs the SPILL error when blocked -- below all of the named ranges data elements.

Or off to the side. 

In short, design the Dashboard so that the area that needs to be open-ended is open-ended.

[#3]

@amcckey 

Rather than using distinct formulas for the various parts of your dashboard stack them so they form a single spilt range.  The following is more complicated than it need be because I have also inserted a blank row between the tables by EXPANDing the top one and have added a blank column to the second so that it matches the first in terms of columns.

= LET(
    top,    EXPAND(Table1, ROWS(Table1)+1,, ""),
    bottom, EXPAND(Table2, , COLUMNS(Table2)+1, ""),
    VSTACK(top, bottom)
  )