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.

 

[a massive zebra]

=COUNTIF(INDIRECT("'PL"&ROW(A1)&"'!$T$9:$T$28"),"WIN") Then copy this formula down as required.

 

[Danfrancozola]

It's not working. What does A1 mean? What tab does that cell reference to?

 

[Danfrancozola]

It's not working. What does A1 mean? What tab does that cell reference to??

 

[a massive zebra]

ROW(A1) would give a value of 1 as it is the first row. When you copy the formula down across the remaining data it will increase in increments of 1 for each row, so PL1 in row 1 becomes PL2 in row 2 etc. It should reference to the current tab. This formula worked fine for me. What results are you getting? Are you sure the wins in cells T9 to T28 are actually entered as WIN?

 

[Danfrancozola]

So my stats page is on one tab, which I have cell references to PL1, PL2, PL3 etc.

I need the formula on this sheet to show the calculation when linked to these other tabs. When I drag it down I need it to change between these tabs so I dont have to do it manually.

I'm just getting 0 as an answer all of the time?

 

[RoryA]

Let's say you have PL1, PL2 and so on in cells A1, A2 etc on the Stats sheet. You can use:

=COUNTIF(INDIRECT("'"&$A1&"'!$T$9:$T$28"),"WIN")
in another cell on the Stats sheet, which will calculate as =COUNTIF('PL1'!$T$9:$T$28,"WIN")
If you copy that down it will become:
=COUNTIF(INDIRECT("'"&$A2&"'!$T$9:$T$28"),"WIN")
which is calculated as =COUNTIF('PL2'!$T$9:$T$28,"WIN")
and so on.

 

[Danfrancozola]

PL1 and PL2 are the names of the worksheets that I need to change.

I have put that formula in to my stats page and its coming back #REF.

Im so confused

 

[Kaps_mr2]

1. check that you do not have spaces either side of PL1 or PL2 in your sheet names.
2. get one team working first and then the others will follow.

 

[Danfrancozola]

I have put it in my stats page which is called ht result. I have put it in the cell for arsenal (L7) and tried to link it to PL1. It keeps coming up with an error. PL1 shows all of the stats for arsenal for each of their games.