Array formula error

Question

I want to use: b6 =B5:B20+1 (the actual formula is much longer, thousands of rows etc, but this is the gist, and gives the same error)

If I use b5 =20

                    b6    =b5+1

                    b7    =b6+1

                      etc

it all works just fine, and it is exactly the same as the array formula I'm trying to spill into the cells below.

This is not a circular reference, but Excel mistakenly thinks it is and refuses to spill the array. How to fix?

Thanks,

Maarten

Answer 1

In the meantime I'm still looking for a solution. Worked on another spreadsheet to make it spilled formulas, but it had a similar step-by-step calculation, and the spreadsheet crashed. This really needs to be solved.

Answer 2

Doing it as we speak, no doubt.

Answer 3

Is the Enterprise scanning for whales in your animation?

Answer 4

Hmmm, it seems the Table only automates the copying of the formulas, and indeed, it only works when you start with an empty seed row. But it then stores all rows separately, and I can still edit individual rows, no different from just double-clicking the corner of the top cell whenever I make a change. It still saves at the original large size, which is the one thing I was hoping to fix.

Answer 5

Thanks, I will try the table option, see if that makes the spreadsheet any quicker or smaller to save compared to repeating the formulas 9000 times for each column

The spreadsheet also automatically saves animations