Dynamic, Spilled, Named Ranges - how to override SPILL

[TimB]

I have a spreadsheet I put together a couple years ago that used to work great, and now is screwed up by the new dynamic, spilled array .... stuff.

The spreadsheet uses cell values in a named range, say column B, as input parameters in a variety of formulas located in cells of column C.

In the template file the first character in the formula cells is an &, so that this column can be used to populate tables for different projects, then can be replaced with = to convert them to functioning formulas.

I used this file for the first time in a while and now the use of the named range is, apparently, triggering this lovely new dynamic array SPILLED function thing.
It wants to use the formula in cell C5 to spill down results using the values in cells B5 thru B55.

None of the formulas contain dymanic spilled operator commands.

Can we not use named ranges as regular old array functions anymore?

How do I fix this?

Updated, any formula I write in the tab with the name of the named range acts as a spilled formula. LOVE IT!

[TMS]

Don't know if this helps ...

https://support.office.com/en-gb/art...rs=en-GB&ad=GB

https://blog-insider.office.com/2019...ions-in-excel/

Gut feeling is you might be stuffed ...

[TimB]

Thanks Trevor, I saw those already. No help that I could make out.

I added and attachment with the issue if interested.

I would find it hard to believe that Microsoft would screw up a basic use of named ranges except that, they are Microsoft.
After all, I can't type 2-4 anywhere in Excel without it insisting it is Feb. 4th.

[TMS]

Oh, I can believe it. I rarely start threads these days but I did recently. A client had been using a workbook I developed for seven years. They got some new laptops (and Excel) and one of the functions failed. I was able to find a workaround for the problem but I never got to the bottom of what had changed. So, yes, they (MS) can screw you, albeit with the best of intentions.

I'll have a look when I get a chance (but not tonight) 😴

[AliGW]

The way to overcome the SPILL default is to first remove the SPILL area completely, then start again with your formula in the first cell - enter it in the old-fashioned array way (CTRL+SHIFT+ENTER), then drag copy down.

I understand your frustration, but your thread title wasn't quite what we ant, so as a courtesy, I have updated it slightly for you.

[TimB]

Hi I came back to see if there were any suggestions, and I opened my file again this AM to respond to Ali.

In the course of doing so, I find that my file (a copy on my hard drive I last saved in 2018) is magically acting as intended without defaulting to a spilled dynamic array.

So Ali, I can't help but be a little snidely ……. Excel, LOVE IT, it's magic! (I wonder what it is going to do on Monday when I try to use it in the project tables.)

Ali, I appreciate your reply, but I am not using an array formula. I have different formulas going down column E and I am using the named range function so the formulas know to look in the same row of column C for the input parameter.

[AliGW]

I know that you are not using an array formula - however, what I have suggested is the workaround. I am telling you this because I have discovered this issue myself and that IS what solves it. You can take it or leave it, and be just as snide as you like.

[TimB]

My file is back to defaulting to spill mode, triggered, I presume, by the named range. Perhaps spill mode took the weekend off.

Ali, I tried your suggestion. I am a novice at array formulas, but as far as I can tell, if I convert the top formula to a "legacy" array formulae then copy down, I just get the same array formula in all the cells.

I can type shift+control+return, manually for each cell and that un-spills the output, but that defeats the purpose of naming the input range.

My awkward "fix" is to delete the range name. add a temporary column populated with the cell numbers that have the input data values, and use a substitute function to put the cell numbers in formulas, copy values, replace & with =

[CK76]

You could use...
=INDEX(FIND(_Sed01,E3),ROWS($A$1:A1))

Copy down.

Or enter first cell (G3) with CTRL + SHIFT + ENTER. Then drag down that cell.