SOLVED

a little help in using auto fill on formula

Copper Contributor

Hello,

I want to use auto fill on some formulas but I want it to jump multiple rows instead of 1, like 33 rows in this example:

row 1: =IF($A$1=Sheet2!$B3,Sheet2!$F9,0)
row 2:=IF($A$1=Sheet2!$B36,Sheet2!$F42,0)
row 3:=IF($A$1=Sheet2!$B69,Sheet2!$F75,0)
row 4:=IF($A$1=Sheet2!$B102,Sheet2!$F108,0)

 

is there a way to make auto fill work like that? any help would be appreciated 

Thank you!

2 Replies
best response confirmed by Ma_SiNi (Copper Contributor)
Solution

@Ma_SiNi Try this on row 1 and copy down:

=IF($A$1=OFFSET(Sheet2!$B$3,(ROW()-1)*33,0),OFFSET(Sheet2!$F$9,(ROW()-1)*33,0),0)

 

@Riny_van_Eekelen 

Thank you! it worked, the actual table starts from 6th row so I just had to change ROW()-1 to ROW()-6, I guess I need to learn more about OFFSET formula

1 best response

Accepted Solutions
best response confirmed by Ma_SiNi (Copper Contributor)
Solution

@Ma_SiNi Try this on row 1 and copy down:

=IF($A$1=OFFSET(Sheet2!$B$3,(ROW()-1)*33,0),OFFSET(Sheet2!$F$9,(ROW()-1)*33,0),0)

 

View solution in original post