SOLVED

New Contributor

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

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

# Re: Auto fill Problem using INDIRECT() inside a SUMIFS() formula

Photos For clarification

# Re: Auto fill Problem using INDIRECT() inside a SUMIFS() formula

@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.

# Re: Auto fill Problem using INDIRECT() inside a SUMIFS() formula

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

# Re: Auto fill Problem using INDIRECT() inside a SUMIFS() formula

@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?

# Re: Auto fill Problem using INDIRECT() inside a SUMIFS() formula

I can't thank you enough, really appreciate