Increment ROW, not COLUMN when dragging formula horizontally

0
votes

I'd like to drag the formula

=if(and(AHTpivot!$A1=statusSheet!$A1, AHTpivot!$B1="wrap-up"),AHTpivot!$C1, "")

right to adjacent columns about 1,000 times. I only want statusSheet!$A1 to increase, and it needs to increase in rows instead of columns.

For instance, if the formula is in A1, and I drag it to B1, it should be in cell B2. [?]

=if(and(AHTpivot!$A1=statusSheet!$A2, AHTpivot!$B1="wrap-up"),AHTpivot!$C1, "")
2
google-sheetsgoogle-sheets-formula

2 Answers

1
votes

Maybe:

=if(and(AHTPivot!$A1=indirect("StatusSheet!$A"&Column()),AHTPivot!$B1="wrap-up"),AHTPivot!$C1, "")
0
votes

You could also use an array formula if you don't want to drag the formula.

=ARRAYFORMULA(IF(

(AHTpivot!$A1=INDIRECT("statusSheet!$A"&COLUMN(A1:AAA1))
* (AHTpivot!$B1="wrap-up")

,AHTpivot!$C1, "")

The * acts as an and.

Change AAA1 to the column reference you desire. Or, change to COLUMN(A1:1) if you want to go to the end of the sheet.

Credit to pnuts for the INDIRECT idea. :)