Updating Individual Sheets from a master

[michaelgraves176]

I run a sports league and have a league master schedule as an
individual worksheet with date/home team/away team/scores etc. I then
have individual worksheets for each team with their schedule. I would
like to create a formula that will update scores on the individual
team sheets, when entered once on the master sheet.

Please help.

Thanks

 

[Guest]

One way is to use SUMPRODUCT

If your 'master' sheet looks like this:

6/1/2007 Red Dogs 100
6/2/2007 Wild Dogs 88
6/3/2007 Red Skins 77
6/4/2007 Red Dogs 69
6/5/2007 Wild Dogs 92
6/6/2007 Red Skins 95
6/6/2007 Red Devils 84
6/6/2007 Red Day 72
col A (dates), col B (team names), col C (scores)

On sheet2 (Red Dogs) you have the dates on col A, and the team name on col
B. Put this formula on C2:
=SUMPRODUCT(--(master!$A$1:$A$8=Sheet2!A1),--(master!$B$1:$B$8=Sheet2!B1),--(master!$C$1:$C$8))
This is an array forumula, so you must commit by pressing Ctrl+Shift+Enter
at the same time. This formula assumes each team has only one score on any
given day. It will not work if the team has more than one score on one day -
it will sum the scores together (most likely your teams play only once in a
day).

h.t.h