Forum Discussion
How to stop auto complete AND spill! error of formulas in following rows/columns?
I'm trying to type a formula just in one cell and when I hit enter it automatically fills the rows next to them and I don't know how to stop that. If I try to delete or put another formula on the nex...
Thank you! it's nice to know this trick for the future!
Sadly I'll be taken off points if I add stuff to my formulas so I was hoping to deactivate the automatic feature. Any ideas suggestions would be super helpful.
Nope, you can't switch dynamic arrays off, if only to shift on older versions which doesn't support them. It's better not to rely on default behaviour of pre-DA Excel, but say to Excel directly what exactly you'd like to calculate. Bit more efforts, but much more powerful solutions.
For example, here you may say to Excel directly which columns you'd like to calculate as
=Sales_parameter_1*INDEX(Seasonal_adjustment,1,{2,3,4,5})*SQRT(Sales_parameter_2+INDEX(Ad_expenditures,1,{2,3,4,5}))