I have the following formula

``````A2=IF(SUM(B273:B279)=0,"true","false")
``````

I want to pull the cell along a row (to the right) and want the formula to adjust as follows

``````B2=IF(SUM(B274:B280)=0,"true","false")
C2=IF(SUM(B275:B281)=0,"true","false")
...
``````

If I extend it the regular way, by dragging the cell, the Column is changing from B to C and so on. How can I change it so that the two row numbers, but not the column letter are adjusting?

• You could try rewriting the function using `OFFSET` and possibly `COLUMN` for it to change like that. – Christofer Weber Nov 9 '16 at 14:11

A completely different way to go is to bypass the autofill and make your own. In A2 change the code to

``````=IF(SUM(OFFSET(\$B\$273,COLUMN()-1,0):OFFSET(\$B\$279,COLUMN()-1,0))=0,"true","false")
``````

Then just drag it out on the row like you wanted to.

Good luck!

Explanation:

What we do here is add and offset to the reference cell. Syntax for offset is:

``````OFFSET(reference, rows, cols, [height], [width])
``````

We refer to the original target `B273` but we don't want autofill to mess with it anymore so we make it an absolute reference by adding `\$` before the column and row. Offset then changes the row we want to look at. (B273,x,0)
height and width are optional

But we need the offset in row to change with the columns, as we drag the code out, so instead of a number, we add another function

``````COLUMN()
``````

Column() simply returns the current column. In A that would be 1, B=2, C=3 and so on.

Since we start in the A column, it's already adding one row. Therefore we need to remove one row to compensate, hence the `-1`.
This can of course also be done by changing the initial `\$B\$273` to `\$B\$272` and remove the `-1`.
I just wanted to include the -1, so that it's clearer what to do if you plan on starting the sequence on another column, and so that we can see what the original target is.

That's how autofill works. What you can do, is put a `\$`in front of the column like `\$B273`to keep `B` from changing to `C`.
Dragging along the row will now only make copies, because autofill doesn't know what you want, so that doesn't help much.
What you can do now however, is dragging it down to fill the numbers, copy the row, and then paste special; Transpose

It may not be the best workaround, but it's simple.