Change sheet number in middle of sumif formula

[P4r4gon]

Hi,

I have this formula

=SUMIF('1'!$D$4:$D$10,$B5,'1'!$G$4:$G$10)

I need the sheet number to increase as I drag it across, everything else does as it should.
I've used indirect before, but can't see how to fit it in here.

Thanks

[Tony Valko]

What is the first cell that you are entering the formula in?

[P4r4gon]

It will be C5 in a sheet named Overview

I 52 sheets (weeks) need the overview to in C5 look for the name in B5 in the cells D4:D10 in sheet1 (Week 1) then return the total mins in cells G4:G10, then in D5 of the overview sheet do the same with the same cells but just from shhet2 (weeek 2)

Thanks for looking

[Tony Valko]

Try this...

=SUMIF(INDIRECT("'"&COLUMNS($C5:C5)&"'!D4:D10"),$B5,INDIRECT("'"&COLUMNS($C5:C5)&"'!G4:G10"))

[P4r4gon]

Brilliant. Works a treat. Thanks a lot.

[Tony Valko]

You're welcome. Thanks for the feedback!