Combining Indirect with Countifs

Morning all,

I have a successful Countifs function that accumulates data from an individual sheet;

=COUNTIFS('Week 3.2015'!$A$2:$A$298,$F$2,'Week 3.2015'!$E$2:$E$298,$A21,'Week 3.2015'!$G$2:$G$298,$C21,'Week 3.2015'!$H$2:$H$298,$B21,'Week 3.2015'!$L$2:$L$298,F$3)

I have various sheets for different weeks that I combine on 1 Control sheet that gives a breakdown of the data. At present I copy and paste the function and then manually amend the information for the relevant sheet.

Is there a way of referencing a sheet name in a cell so that it automatically searches for the data required. i.e the same function as above but automatically changes 'Week 3.2015' to 'Week 4.2015'? Having had a look around I think Indirect may work but where do I add it to the above function? Or happy to use another but would like to keep the Countifs the same.

Thanks in advance,

Phil

Try

=COUNTIFS(INDIRECT("'"&A5&"'!$A$2:$A$298"),$F$1,
INDIRECT("'"&A5&"'!$E$2:$E$298"),$A21,
INDIRECT("'"&A5&"'!$G$2:$G$298"),$C21,
INDIRECT("'"&A5&"'!$H$2:$H$298"),$B21,
INDIRECT("'"&A5&"'!$L$2:$L$298"),F$3)

Thanks Bob, with A5 being the sheet name I presume?

Yeah, that is right. What's the weather like where you are?

Doesn't like it at the moment but I shall have a tinker and see what I can come up with.

Weather 8oC bright and dry

Hi Bob,

Still struggling slightly.

On a seperate area of the spreadsheet, I have the following with fewer variables;

Original
=COUNTIFS('Week 5.2015'!$E$2:$E$349,$A4,'Week 5.2015'!$G$2:$G$349,$B4)
Including Indirect
=COUNTIFS(INDIRECT("'&AD1&"'!$E$2:$E$349"),$A4,INDIRECT("'&AD1&"'!$G$2:$G$349"),$B4)
Where AD1 reads Week 5.2015

Still coming up with an error... help please!

You dropped a quotes

=COUNTIFS(INDIRECT("'"&AD1&"'!$E$2:$E$349"),$A4,INDIRECT("'"&AD1&"'!$G$2:$G$349"),$B4)

Yes I did!!! You're a star and I'm really grateful, thank you.