How do I use cross-column references in a block of Excel spill formula rows?

I am making a dynamic spreadsheet for calculating steam consumption of turbines, where the user enters key inputs (inlet temperature, inlet pressure, outlet pressure, efficiency) at the top of each column and the key outputs (outlet temperature and wetness) are calculated beneath. Each column of calculation represents a single turbine stage, so in some cases two adjacent columns are required for a single turbine (if it has a primary and secondary stage, with steam entering or leaving in between). All calculations in a given column are only using data from inputs in that same column.

I am trying to use spill arrays so that the user can just add more columns as required, and not worry about correctly copying along the many rows of calculations. This seems sensible, given my earlier statement that the calculations are fundamentally column-wise.

The problem I have is that in some cases, the inlet temperature in one column needs to be set to equal the outlet temperature of the column before it i.e. secondary stage inlet equals primary stage outlet. Excel seems to treat spill array inputs as real arrays, rather than element-wise, for tracking dependencies at least - so I get a circular reference error. The error is spurious, because no circular calculations exist within a column in this scenario.

The easiest way to reproduce the behaviour, without the complexities of the real-world problem is:

1. In a new sheet, enter the values 1 and 2 into cells A1 & B1, respectively.
2. In cell A2, enter =BYCOL($1:.$1, LAMBDA(x, 2*x))
3. In cell B1 again, try to set it to =A2.

What this trivial example should do is:

1. Cell A2: take the value '1' from cell A1 and multiply it by 2.
2. Cell B1: take the value '2' calculated in cell A2.
3. Cell B2: take the value '2' from cell B1 and multiply it by 2.

This does not work even though there is no actual circular reference. Is this just a hard limitation of how Excel tracks dependencies of array formulae?

It seems that at time of writing, Excel's dependency tracking functionality is only able to track spill arrays as if they were any other array, and so it cannot differentiate between a calculation that is element-wise and one that operates on the whole array input argument(s).

The only viable solutions for tasks where one column needs to take outputs from another column as input are:

1. Use a helper row to hold the true input and then use an external (to Excel's native formulae) means to copy the reference output to the input cell. This could be VBA or, in the worst possible case, a user input with visual feedback if the value is out of date.

2. Stack multiple (optional) calculations into a single column. So in my real-world example, I may just design for a maximum number of sequential turbine stages and stack those calculations into each column.