Help me I'm going CRAZY!!! COUNTIF AND INDIRECT FORMULA?!

[Danfrancozola]

So basically I am creating a database of football statistics to include corners, goals, possession etc.
I am concentrating on many leagues around the world and for each team I am doing a seperate tab, so there will be many tabs.

For each team in the premier league for example, each tab is known as PL1, PL2, PL3 etc.

I need the same formula on a statistics tab for each of the teams.

Arsenal for example are known this season as PL1.

For a formula to count how many games they have won I have this formula: =COUNTIF('PL1'!$T$9:$T$28,"WIN")

When I drag down to the new cell, I need the PL1 to change to the next tab which will be PL2.


I have tried so many different formulas and I just can't seem to get it right. Someone out there must know the answer!

Thanks a million!

Danfranco.

 

[RoryA]

What exact formula have you used?

 

[Danfrancozola]

=countif(indirect("'"&$a1&"'!$t$9:$t$28"),"win")

 

[RoryA]

And what is in A1 on the same sheet as that formula?

 

[Danfrancozola]

Nothing. It's just blank.

 

[RoryA]

That's why you get an error then. A1 needs to contain the name of the sheet you want the formula to do the COUNTIF on. Or change the A1 part to point at the correct cell which does have the sheet name.

 

[Danfrancozola]

That's why you get an error then. A1 needs to contain the name of the sheet you want the formula to do the COUNTIF on. Or change the A1 part to point at the correct cell which does have the sheet name.

I'm sorry Rory this is quite complicated and I think we might have our wires crossed abit.

Let me try and explain.

So I have a football database.

Each team within this database have a worksheet specifically for them. Arsenal are in PL1. Aston Villa in PL2.
On worksheet PL1, I have a table to show arsenal's stats for each of their games against other teams. This shows goals, corners, cards, ht result, who scored first etc.

I also have different worksheets for each stat, so one for cards, corners etc.

For the one I have that is HT result, I have to calculated how many games Arsenal have won at half time from the ones they have played.

In cell M7 on that page I have the following formula to do this: =COUNTIF('PL1'!$T$9:$T$28,"WIN")

In cell M8 on that worksheet, I want to do the same thing for Aston Villa who are on worksheet PL2.

Instead of going down each cell and changing PL1 to PL2, PL3 etc, I want to just be able to drag the cell down so it does this for me.

Sorry its abit of a nightmare.

 

[RoryA]

You need a list of sheet names, say in column L. So if you have PL1 in L7, PL2 in L8 and so on, your formula in M7 becomes:
=COUNTIF(INDIRECT("'"&L7&"'!$T$9:$T$28","WIN")
which you can then copy down and it will pick up the different sheet names in column L. If you don't have the sheet names listed in cells, you're out of luck without using code, or manually editing the formulas.

 

[Danfrancozola]

Thank you Rory you are a life saver!