How to update cell reference when "dragging" and INDIRECT forumla

[mikicia]

Hi all,

I've searched the forums, but don't have a really good answer for my formula. I am using the formula below to sum across several worksheets, and the formula I am using works fine, but when I copy the formula across to other cells, I can't figure out how to have it update my cell range:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!A:A"),$B4,INDIRECT("'"&$A$1:$A$4&"'!G:G")))

The range A:A is ok to be static, but I need the G:G to be dynamic so when I drag it to the right it will change from G:G to H:H. I:I, etc.


Any help would be much appreciated.

[Punnam]

hi,

Upload sample workbook with out any confidential data.

Punnam

[AlKey]

Maybe this?

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!A:A"),$B4,INDIRECT("'"&$A$1:$A$4&"'!"&CHAR(64+COLUMNS($A$1:G1))&":"&CHAR(64+COLUMNS($A$1:G1))&"")))

[mikicia]

Your formula worked.... thank you so much for your help!

[mikicia]

Ok update, when I get to "AA" Column and error appears as the character is "]"

[Tony Valko]

Another possibility...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!A:A"),$B4,INDIRECT("'"&$A$1:$A$4&"'!"&CELL("address",G:G))))

[AlKey]

I din't expect it will need to go so far to the right. But here is corrected formula. The range will go to the very end

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$1:$A$4&"'!A:A"),$B4,INDIRECT("'"&$A$1:$A$4&"'!"&SUBSTITUTE(ADDRESS(1,COLUMNS($A$1:G1),4)&":"&ADDRESS(1,COLUMNS($A$1:G1),4),1,"")&"")))