My actual use case is somewhat more complicated, but I believe if I can solve this minimal example then I should be able to leverage the result.
Say you have a spreadsheet that contains a simple formula.
e.g.
B1=A1 + 16
I can use autofill, but ideally it would be nice to use a dynamic array formula instead.
B1=A:.A + 16
This works, unless any value in column A
references column B
.
A2=B1
Even though B1
only needs A1
to return its own value, it faults presumably because B1
is technically returning an array and looking at the entire column of A
. Is there a clever workaround for these situations or is this simply not a use case for an array formula?
The way to resolve this is:
A2=A1+16
So, just rethink your solution because the approach you try to apply is not good using any instrumentation not particular Excel.
P. S.
The workaround is to enable iterative calculation in Options-->Formulas-->Calculation options.
[A2]=B1:B6
[B2]=A2:A7+16