need Formula to adjust to month tabs

Hi Gurus

I keep making references and this messes up my sheet, or i have to create to many sheets of a simple reason.

I have 12 months of tabs and then one sheet that makes a summary of the month, but rather than creating the sheet each month, I would just like to have an option to change the month name in the formulas.
Not sure I am saying it correctly.

I have the following formula to retrieve data from a BIG data sheet, but would like an easier option when I have to do FEB or any other month Next time.

=COUNTIFS(JAN!E:E;'F10 - Operations'!L19;JAN!BA:BA;1)

So the "JAN" i would like to replace with FEB (According to TAB name). (There is like 100 of these, and do not want to use the REPLACE function every time)

Can is not have a drop down to choose the month and then have the formula adjusted to that Value of the dorp down?

It can be done with Indirect function i am not good at building Indirect without seeing the file if you can help with a sample sheet sure i can do it

Punnam

=COUNTIFS(INDERECT(A1&"!E:E");'F10 - Operations'!L19;INDERECT(A1&"!BA:BA");1)
in A1there is sheetname

Thanks

I tried it, but the figures came out totally wrong to the original formula. I have made an example. The data sheets goed for about 5000lines.
And all my formulas is about COUNTIFS, cause it has to relay on 2 criterias.

I made a sample. Thanks so far, did not know of an INDIRECT function.

=COUNTIFS(INDIRECT($A$1&"!AD:AD"),L11)
it works

Originally Posted by tim201110

=COUNTIFS(INDIRECT($A$1&"!AD:AD"),L11)
it works

Great, Thanks. I found my problem, the first was my pc make all , into ;. And my reference was incorrect incorrect.

Many Thanks, now I have also learned something! Thanks

ronettes,

In whichever sheet you want to show the answer (which I assume is not 'F10 - Operations'), put the month name in B3, and this in whichever cell you want to show the answer:

=COUNTIFS(INDIRECT("'"& B3 & "'!" & "E:E");'F10 - Operations'!L19;INDIRECT("'"& B3 & "'!" & "BA:BA");1)

Ochimus