Feb 21 2020 08:19 PM
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 next column I want to use, I get the error Spill!.
I've tried:
- Advanced>Editing> unchecking: Enable AutoComplete for cell values
- Advanced> Editing> unchecking: Extend data range format and formulas
- Advanced> Formulas> unchecking: enable multithreated calculation
- Advanced> Trust Center> Disable all applications Add-ins (may impair functionality)
- Options> Formulas> Workbook calculations> manual instead of automatic
- Options> Formulas> unchecking: Formula autocomplete
- Options> Proofing> autocorrect options>Fill formulas in tables to create calculated columns
Please help as I'm not sure what else to try
Feb 21 2020 10:18 PM
It's not a bug, it's a feature:
Feb 21 2020 10:56 PM
@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.
Feb 22 2020 09:11 AM
Feb 22 2020 09:14 AM
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.
Feb 22 2020 09:22 AM
@Cris11Ask Put an @ in front of the formula (after the =). But you'll disable a great new feature.
Feb 22 2020 09:42 AM
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}))