Hi,
I’m trying to auto-fill the values for the empty cells.
To do so, I entered in one cell the following formula on 2 adjacent cells:
=$A$1+1
=$A$1+2
In order to auto-fill the values for the cells below them, I select both cells, then dragged the little black corner down and got the following:
=$A$1+1
=$A$1+2
=$A$1+1
=$A$1+2
...
I expected to get:
=$A$1+1
=$A$1+2
=$A$1+3
=$A$1+4
Just like Microsoft Excel does.
Tested it with 3.5.7 and 4.1.04 and same behavior. Excel 2010 Starter has also the same behavior. But I would expect your results too.
Don’t really understand why you need dollar signs in this formula, strange to me… Lets suppose to say that formula you are writing in are in Column B of spreadsheet, then write the following:
B1 cell: A1+1
B2 cell: B1+1
Click on B2 cell’s black square and pull it down to copy all of the values.
P.S. I don’t use Excel anymore (so can’t really check how Excel is working), but if I remember correctly your approach is only going to be working if number 1 and 2 are inserted in cell, mark them and pull black rectangle. This is working fine in LibreOffice Calc too. But when you are dealing with formulas, then you need to understand what are dollar signs for. For example check the following article (it is about Excel, but it also applies to Calc): http://www.notjustnumbers.co.uk/2011/05/excel-tip-dollar-sign-in-formula-fixing.html
One could do something like:
B1 cell: =$A$1+ROW()
When one copies that cell, the value of ROW() (without any arguments) will have the value of the row it is on, resulting in the value of 1 for cell B1, 2 for cell B2, etc.
The problem is that if that section of the spreadsheet is relocated further on down on the page or on another page, ROW() might not be what one wants, but one might decide one would rather have ROW()-5 if one had inserted five rows before the desired row. Of course, relocating the value may also mean the absolute cell reference of $A$1 would also need changing.
ROW() can also take a cell reference, so one could code, for example,
=$A$1+ROW()+1-ROW($A$1)
and then if the sheet gets relocated to a different spot on the page, after correcting the absolute reference, one would get the correct values again.
I personally am not fond of that solution. I am one of those who would prefer using a column that starts with the value 1 and use a formula that adds 1 to it, e.g.,
C1 cell: 1
C2 cell: =C1+1
and then copy C2 down as far as needed.
Then one could use
B1 cell: =$A$1+C1
and copy that formula down as far as needed.