Why is cell increment not detected correctly in LibreOffice Calc

Question

I have a column which I'd like to fill by selecting the top two cells and then drag down the column. The cell contents are:

=Sheet1.B11
=Sheet1.B31

So when I drag down I expect to see

=Sheet1.B51
=Sheet1.B71

Instead, I get

=Sheet1.B13
=Sheet1.B33

Why is Calc not detecting the increment correctly? Adding more cells manually does not help.

Answer 1

The numbers in cell references are not single numbers which can be used to create a series in this way. In other words: The cell reference B11 is not "B"&11, but even one single cell reference.

To get references from Sheet1.B11 upwards in steps of 20, you could use INDEX like this:

=INDEX($Sheet1.$B$1:$B$100000,11+(ROW(A1)-1)*20)

Put this formula into a cell and fill it down.