SUMPRODUCT INDIRECT FORMULA CELL REFERENCE NOT UPDATING WHEN DRAGGING ACROSS

[Ajwilltravel]

Hi everyone, when I drag this formula across, the underlined cell reference is not updating - i.e when i drag it to the right across columns I want K6:K150 to become L6:L150, M6:M150 and so on...

Can anyone tell me how to change the formula so that it does update?

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$J$305:$J$310&"'!k6:k150"),INDIRECT("'"&$J$305:$J$310&"'!$d6:$d150"),$D282,INDIRECT("'"&$J$305:$J$310&"'!$i6:$i150"),$I282,INDIRECT("'"&$J$305:$J$310&"'!$c6:$c150"),$C282,INDIRECT("'"&$J$305:$J$310&"'!$g6:$g150"),$G282))

Thanks very much !

 

[Domenic]

You can use the CELL function to return a reference as text . . .

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$J$305:$J$310&"'!"&CELL("address",K6)&":"&CELL("address",K150)),INDIRECT("'"&$J$305:$J$310&"'!$D6:$D150"),$D282,INDIRECT("'"&$J$305:$J$310&"'!$I6:$I150"),$I282,INDIRECT("'"&$J$305:$J$310&"'!$C6:$C150"),$C282,INDIRECT("'"&$J$305:$J$310&"'!$G6:$G150"),$G282))

Hope this helps!