# increase column reference when copying downwards

#### littlesister

##### New Member
I'm sure this question has been asked before, but I can't find anything that answers my question.

I have a formula which contains column references that I need to copy vertically, while increasing the column reference with every new copied row.

e.g. C1 contains formula:
=SUMPRODUCT(--(Report1!\$X\$2:\$X\$10806>Report1!S\$2:S\$10806))

Now when I copy this formula I need the next rows to show:

D1
=SUMPRODUCT(--(Report1!\$Y\$2:\$Y\$10806>Report1!S\$2:S\$10806))

E1
=SUMPRODUCT(--(Report1!\$Z\$2:\$Z\$10806>Report1!S\$2:S\$10806))

etc...

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
=SUMPRODUCT(--(Report1!X\$2:X\$10806>Report1!S\$2:S\$10806))

Dom

Sorry, my mistake; I mean copying downwards:

e.g. C1 contains formula:
=SUMPRODUCT(--(Report1!\$X\$2:\$X\$10806>Report1!S\$2:S\$10806))

C2
=SUMPRODUCT(--(Report1!\$Y\$2:\$Y\$10806>Report1!S\$2:S\$10806))

C3
=SUMPRODUCT(--(Report1!\$Z\$2:\$Z\$10806>Report1!S\$2:S\$10806))

Not tested but try:

=SUMPRODUCT(--(OFFSET(Report1!\$X\$2:\$X\$10806,0,ROW(A1)-1)>Report1!S\$2:S\$10806))

Dom

weird:

this doesn't work:
=SUMPRODUCT(--(OFFSET(Report1!\$X\$2:\$X\$10806,0,ROW(A1)-1)>Report1!S\$2:S\$10806))

But when I replace the ROW(A1)-1 by a digit, it works:

=SUMPRODUCT(--(OFFSET(Report1!\$X\$2:\$X\$10806,0,1)>Report1!S\$2:S\$10806))

Caused by any limitation within Excel?

by the way it says: #VALUE!

Boo, I'm getting the same. I've used the same technique before for normal calculations but it just doesn't seem to like including ROW in either a SUMPRODUCT or SUM array formula.

I'm sure it must be possible, will keep looking.

Dom

Thanks Dom.

I can always just add a column with an index (1, 2, 3, etc) and reference to the index to get the right offset, but if you find a better way, let me know!

Replies
2
Views
193
Replies
2
Views
167
Replies
3
Views
736
Replies
1
Views
188
Replies
5
Views
349

1,196,358
Messages
6,014,786
Members
441,847
Latest member
hw407

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back