Microsoft"Do we continue to copy formulas?" No
"Or do we just put the one formula without @ in A3 and let it spill?" Yes
"This is a HUGE change to the way Excel works. No longer copying formulas." Thank God (or at least JoeMcDaid and his team)
[ Note: A major role of auditing software is to identify copying errors and overwritten cells -- no longer needed! ]
"And now if we look into cell B3, say, how do we figure out how the value got into that cell?"
Who cares? If
= FOO + BAR
is a correct formula applied to the array as a whole, the chance of INDEX( FOO+BAR, 2 ) being wrong is negligable.
Further notes: Naming the output $A$3:$C$3 presents an interesting challenge.
One way is to introduce the name FOOBAR that refers to =$A$3 in with case you have downstream formula in the form
= FOOBAR# - AVERAGE(FOOBAR#)
or you define it to refer to =$A$3#, in which case the downstream formulas are of the form
= FOOBAR - AVERAGE(FOOBAR)
The former makes it easier to locate the name that the developer has applied to a dynamic range but the latter causes less disruption if the formula is later uploaded to the name 'Refers to' box. It avoids references to FOOBAR# being turned into an error which has to be corrected by deleting the redundant "#" in each formula.
JoeMcDaidCould a redundant "#" appended to a name that is already a spilt range simply be ignored? It would allow me to prepare a named formula using worksheet cells and then upload to the name without issue.
