Auto transfer data rows to other sheets when certain entries occur

[Struggling in Sheffield]

Hi,
Using Excel I'm building a simple spreadsheet database of all football
results in a league each season, with a master 'Results' worksheet containing
the following columns: -

Master 'Results' worksheet

A B C D E F G
Date Home team Away team H/T score F/T score


I'm then creating a separate worksheet for each team within the league to
record (amongst other things) just that team's results.

Using the master 'Results' worksheet above, I want Excel to automatically
copy across into each individual team's worksheet the data above but showing
only the rows containing that individual team's name, i.e. the relevant team
is listed either in Column B (Home team) or Column C (Away team).

Using answers to previous questions in the Forum I've managed to build an
expression that will do this - but only for one column (B only or C only - so
I get a listing of either 'Home' games or 'Away' games - but not both
together in date order), and I'm struggling to find an expression that will
automatically copy the relevant rows over where the team is found in EITHER
column B or C.

I don't know anything about Macros so if anyone can help with an either/or
expression that would work, to produce a gap free list in each individual
team's worksheet, I'd be very happy to stop banging my head against hard
surfaces.

Many thanks.

 

[Max]

Post your current expression. Chances are that responders out here could
enhance it for you, or maybe suggest a workable alternative to drive the
desired auto-copy results out. Good luck.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[Billy Liddel]

Fear not it has already been done. Try:
http://www.xldynamic.com/source/xld.LeagueTable.html

Peter

 

[Struggling in Sheffield]

Hi all,
OK chaps, here's the current expressions I'm using.

Source data is in worksheet: '2007-08 All results', (columns A to G)

'2007-08 All results' worksheet

A B C D E F G
Date Home team Away team H/T score F/T score


Receiving worksheet (I want to auto-transfer data to) is: 'Birley City FC'

(Expression 1) In 'Birley City FC', grid A2:
=IF('2007-08 All results'!B2="","",IF(OR('2007-08 All results'!B2="Birley
City FC"),ROW(),""))

(Expression 2) In 'Birley City FC', grid B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX('2007-08
Results'!A:A,SMALL($A:$A,ROWS($1:1))))

I've then copied B2 across to H2.
Then I've selected A2 to H2 and copied down to row 1200 (which is the
maximum extent of the source data in '2007-08 All results').

The above expressions auto-transfer all the entries from my source sheet
(columns A to G) where Birley City FC are playing at 'Home' (where Birley
City FC is listed in source sheet column B), and places them in a list (with
no gaps) in my receiving sheet (in columns B to H).

If (in Expression 1) I substitiute C2 for B2 this then auto transfers all
the entries from my source sheet where Birley City FC are playing 'Away'
(where Birley City FC is listed in source sheet column C).

What I want the expression to do is pick up and auto-transfer any line where
'Birley City FC' is present (i.e. listed in EITHER column B or C, 'Home' or
'Away').

I'm sure there must be (ha ha) just some little tweak needed to the initial
expression that will allow this to happen?

Cheers,
Steve.

 

[Max]

(Expression 1) In 'Birley City FC', grid A2:

=IF('2007-08 All results'!B2="","",IF(OR('2007-08 All results'!B2="Birley
City FC"),ROW(),""))

Try replacing the above expression with this:
=IF(OR('2007-08 All results'!B2="Birley City FC",'2007-08 All
results'!C2="Birley City FC"),ROW(),"")
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---

 

[Struggling in Sheffield]

Thanks very much indeed Max, that seem to work just fine.
As usual it's just so blinkin' obvious when it's explained!
All the best,
Steve Sharpe.

 

[Max]

Welcome, Steve.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik

 

[Struggling in Sheffield]

Hi Bill,
Thanks for trying to help, it's not quite what I'm looking for - although I
have down loaded a copy maybe for possible future use!
All the best,
Steve Sharpe.