SOLVED

a little help in using auto fill on formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2289280%22%20slang%3D%22en-US%22%3Ea%20little%20help%20in%20using%20auto%20fill%20on%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2289280%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20want%20to%20use%20auto%20fill%20on%20some%20formulas%20but%20I%20want%20it%20to%20jump%20multiple%20rows%20instead%20of%201%2C%20like%2033%20rows%20in%20this%20example%3A%3C%2FSPAN%3E%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSPAN%3Erow%201%3A%26nbsp%3B%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSPAN%3E%3DIF(%24A%241%3DSheet2!%24B3%2CSheet2!%24F9%2C0)%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSPAN%3Erow%202%3A%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSPAN%3E%3DIF(%24A%241%3DSheet2!%24B36%2CSheet2!%24F42%2C0)%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSPAN%3Erow%203%3A%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSPAN%3E%3DIF(%24A%241%3DSheet2!%24B69%2CSheet2!%24F75%2C0)%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSPAN%3Erow%204%3A%3C%2FSPAN%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%20height%3D%2230px%22%3E%3CSPAN%3E%3DIF(%24A%241%3DSheet2!%24B102%2CSheet2!%24F108%2C0)%3C%2FSPAN%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20a%20way%20to%20make%20auto%20fill%20work%20like%20that%3F%20any%20help%20would%20be%20appreciated%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2289280%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2289821%22%20slang%3D%22en-US%22%3ERe%3A%20a%20little%20help%20in%20using%20auto%20fill%20on%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2289821%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1036415%22%20target%3D%22_blank%22%3E%40Ma_SiNi%3C%2FA%3E%26nbsp%3BTry%20this%20on%20row%201%20and%20copy%20down%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(%24A%241%3DOFFSET(Sheet2!%24B%243%2C(ROW()-1)*33%2C0)%2COFFSET(Sheet2!%24F%249%2C(ROW()-1)*33%2C0)%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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