Can only write formula in first cell of column

[ebf6543]

Hello,

I recently updated Excel and am running into an error that I don't know how to fix.

I'm organizing data into columns and I'm only able to write formulas in the top cell of the column. When I try to drag the formula down as I normally would, I get a #SPILL! error.

When I mouse over the cells below the top cell in the column, the formula shows up in the formula box at the top (greyed out), but when I try to click it to edit it, the cell is actually empty. I thought this might be a quirky autofill setting, but I turned that off in my settings and this is still happening.

The formula is calculating mostly fine even though it's 'ghost' autofilled, but there are a couple of errors in random cells that I'd like to audit, but cannot because (as I said) Excel shows that the cell is empty, so I can't investigate that cell specifically.

What is going on here? How do I fix this? I just want it back the way it used to be, where I could click and drag formulas.

Thank you!

 

[StephenCrump]

Welcome to the Forum!

Don't panic. This is just the new dynamic array formula functionality of Excel 365, which with new functions like SORT, UNIQUE and FILTER, opens up all sorts of possibilities. You'll also no longer have to worry about array entering formulae - there's no need in Excel 365.

To illustrate dynamic arrays, in a clean worksheet enter something in cells A1:B2. Now in D1, enter the formula = A1:B2. Older versions of Excel would return the 2x2 array, but only display the value of A1 (the top left hand cell). Now you see the whole array spill into adjacent cells. But if any of these are occupied, you'll see a #SPILL error.

Normally we use formulae that collapse arrays into a single value, e.g. by summing, or averaging. And this will work in exactly the same way in Excel 365 (without the need to array enter from time to time).

What formula are you using that causing the #SPILL! problem. Can you post a screen shot, preferably using the XL2BB add-in: XL2BB - Excel Range to BBCode

 

[ebf6543]

Thanks for the quick reply! I'm not sure how to use that add-in (my apologies!) but this is the formula:

=IF(Errors!$C$2:$C$300="CP",IF(Errors!$F$2:$F$300=B$1,Errors!$G$2:$G$300," ")," ")

I'm basically categorizing errors that are noted in aggregate on another tab. If I try to drag this formula down the column, as previously stated, I get a #SPILL! error. This is annoying, but fine, and, as you said, the dynamic array function is useful.

The particular thing that I'm struggling with right now is - curiously, two random cells in my error categorization table are returning #VALUE errors. There is nothing special about the data that those cells are referencing compared to the other cells that are returning fine, and I'd really like to be able to audit the formulas for those cells individually to source the problem, but because of 365, I cannot. Any suggestions?

 

[ebf6543]

Nevermind - error solved! Was a simple misunderstanding with the Match function (who knew it can only lookup 255 characters?).

#SPILL errors are still pretty confusing to me, though, so any general advice you have there would be appreciated too!

 

[StephenCrump]

I suggest you Google excel dynamic arrays - there's a wealth of good material out there. And if you don't mind spending US$3, I can recommend this eBook (about 80 pages): Guide to Excel Dynamic Arrays by MrExcel

Your formula in Post #3 uses arrays, and did so even before your 365 upgrade. If the formula was working correctly, then presumably it was entered in rows 2-300. Although you didn't realise it, it relied on a concept called Implicit Intersection.

In Excel 365, you can enter the formula once, as written. Or you can enter cell by cell as =IF(Errors!$C2="CP",IF(Errors!$F2=B$1,Errors!$G2," ")," "), copied down the column.

The latter will make it slightly easier to track the #VALUE errors. But it's not that much harder with the single array formula - obviously a #VALUE error in the Nth result will come from row N+1 in the Errors sheet.