calculating from combined worksheets

[Stuart Emerson]

Hi,

I am on the comittee of a local sports team and need to be
able to tabulate the facts and figures of our teams
performance for the league on a regular basis. Previously
this has been done by manually entering onto a
spreadsheet, but we would like it to automatically update.

We have a spreadsheet that has the following coloumns
along the top

Date Opposition Venue Competition Score Player 1
Goals Subbed Yellow Cards Red Cards Player 2 Goals etc

This is filled in by the manager of each team to detail
who the players have been, who scored goals etc.

We then have a further sheet that details the players
names in a list down the side and the competitions aong
the top with sub headings under each of

full game subbed sub used sub not used goals
yellow card red card

We need this sheet to automatically update from the input
of the data into sheet one.

Any help would be greatly appreciated. IF you think you
can help but need more data please email me and i can
attach the file for easier reference

 

[Bernie Deitrick]

Stuart,

I assume that the first table has names filled in the table body.

If that is the case, then you can fill the second table with COUNTIF formulas, linked to the names in the first column of the second
table as the criteria and with the columns of the first table as the range, along the lines of

=COUNTIF(Sheet1!G:G,$A5)

This would count the number of times the name in cell A5 appears in column G of sheet1 - if column G is the Goals column, then it
would count goals scored by the player in A5.

Note the A5 is $A5: This makes it easier to copy the formula from one cell to the whole table.

HTH,
Bernie
Excel MVP