Relative reference with Indirect function

Hi Experts,

I am trying to sum over multiple sheets dependent on multiple criteria. I reproduce below the formula in one of the cells. The formula is working fine except i need the it to be relative. i.e. when i drag the formula across and down I want the "k26" to change accordingly.

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetlist&"'!B2"),B12,INDIRECT("'"&Sheetlist&"'!k26")))

I have tried the following but am getting ref errors. What am i doing wrong?

=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheetlist&"'!B2"),B10,INDIRECT("'"&Sheetlist&CELL("address",K26))))


'sheetlist' refers to a named list and B2 and B12 being criteria.

Sbz

How far across do you want to copy (i.e. how many columns?). If it is less than 26 then you could use a CHAR function to generate the equivalent of the K, dependent on the column the formula is in, and the 26 could be generated by ROWS($1:26) which will increment when it is copied down. Do you want the B12 (shown as B10 in the lower example) to also change when the formula is copied?

Pete

That works a treat. Really appreciate the prompt response. Thank you.

Thanks for the response, appreciate you looking at the problem. - Special-K's correction above to my formula seems to have fixed it.