1

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?

1
  • You could try rewriting the function using OFFSET and possibly COLUMN for it to change like that. Nov 9, 2016 at 14:11

2 Answers 2

0

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.

0

That's how autofill works. What you can do, is put a $in front of the column like $B273to 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
enter image description here

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .