detecting data in a worksheet

[cjsmith22]

this will probably be completely obvious to some of you but being a bit thick its got me stumped. i have a workbook which contains the results and a table for the current premier football league.i have two ranges in the worksheet (myresults and mytable). What needs to happen is the table needs to update depending on the results that particular week.

the thing that i cant get my head around is this - "the macro should be able to run successfully for any number of teams in the league and any number of matches played. both of these should be detected from the data. you may assume that the spelling of teams names in the results section corresponds exactly to the spelling used in the league table - bearing in mind that a teams name can appear twice in a set of results"

so how do i get my macro to detect, for example whats in cell b2 of the myresults range, eg.tottenham, and get it to correspond with where tottenham may happen to be in the mytable range?

if that made the slightest bit of sense at all, thanks in advance

chris

 

[Richard Schollar]

Chris

I think it would be beneficial if you could post an example of the League table you are aiming to produce and the dataset of results that is going to drive this (preferably via the html prog) and an expected result from that data. It would avoid any misunderstandings in what you are trying to achieve.

Best regards

Richard

 

[Von Pookie]

Cross-posted:
http://www.ozgrid.com/forum/showthread.php?t=41903

 

[cjsmith22]

Copy of Table22.xls
	A	B	C	D	E	F	G	H	I
5	Date	HomeTeam				Awayteam
6	25-Sep-05	Middlesbrough			0	Sunderland			2
7	26-Sep-05	Tottenham			1	Fulham			0
8	01-Oct-05	Portsmouth			0	Newcastle			0
9	01-Oct-05	Sunderland			1	WestHam			1
Update 8

Copy of Table22.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
11			Home					Away
12		P	W	D	L	F	A	W	D	L	F	A	GD	Pts
13	Chelsea	7	4	0	0	9	1	3	0	0	5	0	13	21
14	Charlton	6	1	0	1	1	2	4	0	0	9	2	6	15
15	Bolton	7	2	1	1	3	1	2	1	0	5	3	4	14
16	WestHam	6	2	1	1	8	3	1	1	0	2	1	6	11
17	ManUtd	6	1	1	1	3	3	2	1	0	4	0	4	11
18	ManCity	7	1	1	1	2	2	2	1	1	5	4	1	11
19	Arsenal	6	3	0	0	8	1	0	1	2	1	3	5	10
20	Wigan	6	1	1	1	2	2	2	0	1	3	2	1	10
21	Tottenham	6	1	1	1	2	2	1	2	0	3	1	2	9
22	Middlesbrough	6	1	1	1	2	4	1	1	1	4	3	-1	8
Update 8

 

[cjsmith22]

thanks for the reply parsnip. couldnt include all of both ranges because they wouldnt fit. basically my results range needs to update my leaguetable range, so with the example of tottenham (A21 at the minute),they have just won 1-0 so their league stats need to be updated (+3 in the pts column, +1 in the P column, +1 in the W column etc) the problem is this needs to work for any set of results ie. the macro needs to check what data is in a particular cell of the results range and match it to the same in the league table range. does that make any sense? i think the macro is going to turn out a lot more complicated than i had originally anticipated. cheers for the help anyway.
chris