Array formula not expanding to match lines added to table

I have a number of array formulas that refer back to the table in the top left. The array formulas are in Columns I and L, and cells N200, N203, and P203.

Normally when I add a new line to the top left table (usually by clicking on the empty box below the previous final line and typing the date), the table expands by one row and the array formulas all adjust to match, i.e. all the A180/C180s in the formulas become A181/C181s, etc.

However every once in a while (maybe once a month), the arrays simply refuse to update, and I can't figure out why. When they fail, they all fail, it's not hit or miss. You'll see in the attached sheet that though I've added a line to the table (A181) and data to that line, all the arrays still read through A180/C180.

Can someone explain to me why this randomly fails to update, and if it's something I'm doing incorrectly?

And that leads to an associated question. In the past when this has failed in this manner, I've laboriously gone through and changed all the cell references manually, in every single individual cell. (After doing it once, the arrays usually magically start auto-expanding again.) I'm certain there is a way to make those formulas refer to a named range, instead of all being manual references, but after spending an hour on it, I keep getting #VALUE errors. I've been using http://spreadsheetpage.com/index.php...named_formula/ and pages like it as a reference without success. Obviously, I would prefer that the formulas auto-update without issue per my first issue above, but in the event that they don't, it'd be really nice to just change the ranges in one location and have that propagate across the sheet.

If you clear the Date Filter and look at row 2 down to 23 you will see that there are no formulae in the cells.

That's the reason that you're not getting them copied down ... Excel stores the formulae (somehow) in the first row of Table data. You don't have any formulae there so it has nothing to copy down.

Regards, TMS

1) You don't need array formulas in your application
2) Your formulas are referencing standard ranges...not structured table references
3) Hardcoding the dates in the formulas when you have dates to reference directly is labor intensive.

Try these regular formulas, copied down, in your file:

Please Login or Register  to view this content.

Is that something you can work with?

(2) Your formulas are referencing standard ranges...not structured table references

Shouldn't make a difference. It will still auto-expand but it has to know what the formula is. And, in this case, it looks as though the formulae have been deleted.

Regards, TMS

It "shouldn't" make a difference...However, as a matter of clarity:
If I specifically and unambiguously want a formula to reference a table field, I either:
• Use a structured table reference
or
• reference a Named Range that references the table field

Not sure of the point you're making. If the formula hasn't been "stored" in the Table in the first row of data, it won't be copied to new rows. AFAIAA, you can use whatever formulae you want in a Table, but clearly it makes sense to use Structured Table References ... and that is what you'd get if you select cells in the Table when you create the formula. But, if you manually type a formula using traditional cell references, they will calculate as normal.

Looks like we agree

Here is your file with a simple solution to the formula problem in the first 20 app rows. I just inserted rows above the table and hid them. The table works properly.

There are several problems with the original file.
In the file you attached, when I add a row to the referenced table it has no impact on the array formulas....their references do not include the new row. Those array formulas need to be changed. I still recommend the non-array, structured-table-reference formulas I posted.

Glad you got something you can use!

Hmmmm. I don't know what is going on. The some cell references update correctly for me and some don't with the file I uploaded. When I first opened it, after your message, it seemed to work....then it didn't at all.

I give up.....something really wrong with this.

This seems to be better:---at least when I download the file it seems to work.