SOLVED

Auto fill Problem using INDIRECT() inside a SUMIFS() formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2287322%22%20slang%3D%22en-US%22%3EAuto%20fill%20Problem%20using%20INDIRECT()%20inside%20a%20SUMIFS()%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2287322%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20really%20need%20help%3CBR%20%2F%3EWhen%20i%20am%20Trying%20to%20auto%20fill%20my%20formula%2C%20Row%20Range%20Still%20Fixed%20i%20can't%20make%20it%20fill%20series%3C%2FP%3E%3CP%3E%3DSUMIFS(INDIRECT(%22'%22%26amp%3B%24F%242%26amp%3B%22'!A9%3AD9%22)%3B'1'!%24A%246%3A%24D%246%3B%24B%242%3B'1'!%24A%248%3A%24D%248%3B%24D%243)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERange%20!A9%3AD9%20is%20Fixed%3C%2FP%3E%3CP%3Ei%20want%20to%20make%20next%20line%20be%26nbsp%3B!A10%3AD10%3C%2FP%3E%3CP%3Eand%20next%26nbsp%3B!A11%3AD11%20etc..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2287322%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2287343%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20fill%20Problem%20using%20INDIRECT()%20inside%20a%20SUMIFS()%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2287343%22%20slang%3D%22en-US%22%3E%3CP%3EPhotos%20For%20clarification%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%221.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275310i9EC0C1EBC571002C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221.png%22%20alt%3D%221.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%222.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F275311i50EB3F4B2F7071D9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222.png%22%20alt%3D%222.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2287384%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20fill%20Problem%20using%20INDIRECT()%20inside%20a%20SUMIFS()%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2287384%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1035989%22%20target%3D%22_blank%22%3E%40AGB200%3C%2FA%3E%26nbsp%3BWell%2C%20INDIRECT%20takes%20a%20text%20string%20to%20determine%20the%20cell%20or%20cell%20range%20to%20use.%20The%20part%20%3CSTRONG%3EA9%3AD9%3C%2FSTRONG%3E%20is%2C%20thus%2C%20NOT%20a%20regular%20relative%20reference%20that%20will%20update%20automatically%20when%20copying%2Fdragging.%20It's%20just%20a%20text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooked%20at%20you%20schedule%20and%20I%20wonder%20why%20you%20created%20monthly%20sheets%20to%20begin%20with.%20Have%20you%20considered%20collecting%20all%20debits%20and%20credits%20in%20only%20single%20table%20with%20headers%20like%20%3CDATE%3E%20%3CACCOUNT%3E%20%3CDESCRIPTION%3E%20%3CAMOUNT%3E%20%3CPROJECT%3E%20%3F%20Then%20you%20don't%20need%20to%20deal%20with%20INDIRECT%20and%20you%20can%20use%20many%20of%20Excel's%20built-in%20features%20to%20analyse%20or%20summarise%20the%20data%20by%20month%2Fquarter%2Fyear%2C%20by%20account%2C%20by%20project.%20Whatever.%20Just%20a%20thought.%3C%2FPROJECT%3E%3C%2FAMOUNT%3E%3C%2FDESCRIPTION%3E%3C%2FACCOUNT%3E%3C%2FDATE%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2287657%22%20slang%3D%22en-US%22%3ERe%3A%20Auto%20fill%20Problem%20using%20INDIRECT()%20inside%20a%20SUMIFS()%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2287657%22%20slang%3D%22en-US%22%3EThanks%20a%20lot%20for%20you%20response%2C%20i%20have%20some%20reason%20for%20making%20monthly%20sheet%2C%20so%20I%20need%20function%20that%20make%20the%20same%20as%20you%20saw%2C%20and%20actually%20I%20saw%20some%20people%20use%20row()%20insist%20of%20range%20but%20I%20don't%20know%20how%20to%20do%20it%20but%20thank%20you%20very%20much%20for%20your%20time%3C%2FLINGO-BODY%3E
New Contributor

i really need help
When i am Trying to auto fill my formula, Row Range Still Fixed i can't make it fill series

=SUMIFS(INDIRECT("'"&$F$2&"'!A9:D9");'1'!$A$6:$D$6;$B$2;'1'!$A$8:$D$8;$D$3)

 

Range !A9:D9 is Fixed

i want to make next line be !A10:D10

and next !A11:D11 etc..

 

5 Replies

Photos For clarification

1.png

 

2.png

 

@AGB200 Well, INDIRECT takes a text string to determine the cell or cell range to use. The part A9:D9 is, thus, NOT a regular relative reference that will update automatically when copying/dragging. It's just a text.

 

Looked at you schedule and I wonder why you created monthly sheets to begin with. Have you considered collecting all debits and credits in only single table with headers like <Date> <Account> <Description> <Amount> <Project> ? Then you don't need to deal with INDIRECT and you can use many of Excel's built-in features to analyse or summarise the data by month/quarter/year, by account, by project. Whatever. Just a thought.

Thanks a lot for you response, i have some reason for making monthly sheet, so I need function that make the same as you saw, and actually I saw some people use row() insist of range but I don't know how to do it but thank you very much for your time
best response confirmed by AGB200 (New Contributor)
Solution

@AGB200 Okay! Then you can use OFFSET. I believe that's what you have seen before. Have a look in the attached file, Sheet 0 column D. would that work for you?

 

I can't thank you enough, really appreciate