Apr 25 2021 04:54 PM
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!
Apr 25 2021 10:03 PM
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)
Apr 26 2021 03:59 PM
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
Apr 25 2021 10:03 PM
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)