Forum Discussion
How to stop auto complete AND spill! error of formulas in following rows/columns?
Cris11Ask In addition to Detlef_Lewin 's comment, I took the liberty to correct two small errors in your sheet. The named ranges for both "Seasonal_adjustments" and "Ad_expenditures" pointed to a range from column C to G, rather than D to G. This caused the dynamic array formula in D20, to produce correct numbers but in the wrong quarters. Wrapped the formula in D20 in "ROUND(...............,0)" and all seem to work just fine.
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}))Cris11Ask Put an @ in front of the formula (after the =). But you'll disable a great new feature.
