making Indirect array references dynamic

[neilsolaris]

Hi

I had some help today creating an indirect formula. I'm now seeking tips on making this formula dynamic. At the moment, if I copy this formula one column to the right, it still references the F column, whereas I'd like it reference the G column. Here the formula (it's a little longer than what I originally posted, but hopefully it still makes sense).

=IF(SUMIF(INDIRECT("'"&$A6&"'!$C$11:$C$500"),"Bank",INDIRECT("'"&$A6&"'!F$11:F$500"))>0,SUMIF(INDIRECT("'"&$A6&"'!$C$11:$C$500"),"Bank",INDIRECT("'"&$A6&"'!F$11:F$500")),"")

Many thanks for your help.

[neilsolaris]

I just realized that the IF in my formula could be done away with if I go to options and select not to show zeros. So here it is more simply.

=SUMIF(INDIRECT("'"&$A6&"'!$C$11:$C$500"),"Bank",INDIRECT("'"&$A6&"'!F$11:F$500"))

[Glenn Kennedy]

Indeed... a waste of time...

=IFERROR(1/(1/SUMIF(INDIRECT("'"&$A6&"'!C11:C500"),"Bank",INDIRECT("'"&$A6&"'!"&ADDRESS(11,COLUMNS($A:F),1)&":"&ADDRESS(500,COLUMNS($A:F),1)))),"")

will hide zeros... or just

=SUMIF(INDIRECT("'"&$A6&"'!C11:C500"),"Bank",INDIRECT("'"&$A6&"'!"&ADDRESS(11,COLUMNS($A:F),1)&":"&ADDRESS(500,COLUMNS($A:F),1)))

[neilsolaris]

Thanks a lot Glenn, that's brilliant! I'll go through the formula in a bit to see how it works.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

[neilsolaris]

Done (on both counts)!