SOLVED

Copy down formula issue

%3CLINGO-SUB%20id%3D%22lingo-sub-1279821%22%20slang%3D%22en-US%22%3ECopy%20down%20formula%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279821%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20this%20is%20the%20formula%20i%20would%20like%20to%20copy%20down%2080%20cells.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(IFERROR('1'!%24B%2442%2C0)%2C('1'!%24B%2442)%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20only%20thing%20I%20want%20changing%20is%20the%20%221%22.%20For%20example%2C%20the%20next%20one%20would%20be%202%20then%203%20then%204%20all%20the%20way%20to%2080.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20tried%20grabbing%20the%20mini%20black%20box%20around%20and%20dragging%20down%2C%20but%20the%20formula%20doesn't%20go%20to%20the%20next%20number.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1279821%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1279937%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20down%20formula%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F593401%22%20target%3D%22_blank%22%3E%40NJ1000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(IFERROR(INDIRECT(ROW(A1)%26amp%3B%22!%24B%2442%22)%2C0)%2C(INDIRECT(ROW(A1)%26amp%3B%22!%24B%2442%22))%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1285259%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20down%20formula%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1285259%22%20slang%3D%22en-US%22%3E%3CP%3EA%26nbsp%3Bbig%20thank%20you%20sir%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F428790%22%20target%3D%22_blank%22%3E%40Patrick2788%3C%2FA%3E%26nbsp%3B.%20Your%20solution%20to%20the%20problem%20is%20most%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1288262%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20down%20formula%20issue%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1288262%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F593401%22%20target%3D%22_blank%22%3E%40NJ1000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou're%20welcome.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

Hello this is the formula i would like to copy down 80 cells.

 

=IF(IFERROR('1'!$B$42,0),('1'!$B$42),0)

 

The only thing I want changing is the "1". For example, the next one would be 2 then 3 then 4 all the way to 80. 

 

I've tried grabbing the mini black box around and dragging down, but the formula doesn't go to the next number. 

3 Replies
Highlighted
Best Response confirmed by NJ1000 (Occasional Contributor)
Solution

@NJ1000 

=IF(IFERROR(INDIRECT(ROW(A1)&"!$B$42"),0),(INDIRECT(ROW(A1)&"!$B$42")),0)

Highlighted

A big thank you sir, @Patrick2788 . Your solution to the problem is most appreciated.

Highlighted

@NJ1000 

You're welcome.