Copy rows of data and automatically update

[leerobbo]

Alright all

Sorry to be posting so soon again about another problem!

I'm trying to copy cell data from one worksheet to another based on the value within the cell data. The cell data is about football match information and the criteria it must meet will be a team. Essentially it will be taking the information about the match to a worksheet about that team. So the first team would be Arsenal. There was some success with the advanced filter however this wouldn't update once I changed a piece of data on the original worksheet.

The team name are within columns d2:e381 with column d being the home team and column e being the away team. The team has 38 fixtures so that is the maximum number of potential results. The information for the matches are in cells A2:L381.

I'm using Excel 2007.

Any help would be appreciated.

Thanks

 

[How_Do_I]

Hi there,

Firstly I haven't got xl 2007 so my solution is 2003 based.

I take it that you will end up with 20 workbooks, 1 per team? I wouldn't do that, in my solution I'd have the 20 names in a Data Validation list where I've yellow highlighted "Chelsea"...

Drag the yellow highlighted formula in A2 across and down...

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Home	Away	HTHome	HTAway	Date	FTHome	FTAway	Chelsea
2	Tottenham							Played Home	4
3								Played Away	3
4								Played	7
Sheet1

Excel Workbook
	A	B	C	D	E	F	G
1	Home	Away	HTHome	HTAway	Date	FTHome	FTAway
2	Tottenham	Chelsea	0	5	14-Aug-10	0	0
3	Bolton	Fulham	0	0	14-Aug-10	0	0
4	Chelsea	Blackpool	10	3	14-Aug-10	0	4
5	Sunderland	Birmingham City	1	0	14-Aug-10	2	2
6	Wolverhampton	Stoke City	2	0	14-Aug-10	2	1
7	Chelsea	WBA	20	0	14-Aug-10	6	0
8	Aston Villa	West Ham Utd	2	0	14-Aug-10	3	0
9	Blackburn	Chelsea	1	15	14-Aug-10	1	0
10	Liverpool	Arsenal	0	0	15-Aug-10	1	1
11	Chelsea	Newcastle United	30	0	16-Aug-10	3	0
12	WBA	Sunderland	0	0	21-Aug-10	1	0
13	West Ham Utd	Bolton	0	0	21-Aug-10	1	3
14	Wigan Athletic	Chelsea	0	25	21-Aug-10	0	6
15	Everton	Wolverhampton	1	0	21-Aug-10	1	1
16	Chelsea	Tottenham	40	2	21-Aug-10	1	2
Sheet1