Excel- indirect function (Increment the sheet reference)

Question

I am trying to increment a cell (row wise) referenced inside an Indirect function. Here is the example

=SUMIF(INDIRECT("'"&E4&"'!$D$3:$D$5000"),OFFSET($D$5,ROW()-5)*1,0),INDIRECT("'"&E4&"'!$E$3:$E$5000"))

Since I am copying (auto fill) this formula in next few cols, I can't keep &E4& (used inside indirect function) as constant. But I want to copy (auto fill) the same formula in rows below this cell, I want to keep column E constant but update the row number so that in next row it picks E5 and then E6 and so on. E5 and E6 etc have sheet names that I have in my excel.

Answer 1

If you add the $ infront of the column symbol, the operator this will make that reference static. You can also toggle through all the possible combinations of this ie A1, $A$1, A$1, $A1 by highlighting the range and pressing F4. Your formula should look like this before copying down:

=SUMIF(INDIRECT("'"&$E4&"'!$D$3:$D$5000"),OFFSET($D$5,ROW()-5)*1,0),INDIRECT("'"&$E4&"'!$E$3:$E$5000"))

Note the $ in front of the E's. As a note if you are copying the formula down through rows the autofill should not vary the relative column so this is unnecessary to have in any case.

Answer 2

=SUMIF(INDIRECT("'"&E4&"'!$D$3:$D$5000"),OFFSET($D$5,ROW()-5)*1,0),INDIRECT("'"&E4&"'!$E$3:$E$5000"))

I have found an error in your formula:

Offset() requires three arguments and 2 more optional arguments:

    OFFSET(reference, rows, cols, [height], [width])

    Your code:
    OFFSET($D$5,ROW()-5)*1,0)

Your offset function only has two arguments: OFFSET($D$5,ROW()-5).

If you are attempting to use the asterisk as an wildcard for your sumif function, then it isn't being processed properly due to this incomplete offset function, hence why you are struggling with the cell reference issue.