Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto transfer data rows to other sheets when certain entries occur
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. -- Steve Sharpe |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto transfer data rows to other sheets when certain entries occur
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 --- |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto transfer data rows to other sheets when certain entries occur
Fear not it has already been done. Try:
http://www.xldynamic.com/source/xld.LeagueTable.html Peter "Struggling in Sheffield" wrote: 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. -- Steve Sharpe |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto transfer data rows to other sheets when certain entries o
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" wrote: 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 --- |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto transfer data rows to other sheets when certain entries o
(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 --- |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto transfer data rows to other sheets when certain entries o
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" wrote: (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 --- |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto transfer data rows to other sheets when certain entries o
Welcome, Steve.
-- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Struggling in Sheffield" wrote in message ... 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. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Auto transfer data rows to other sheets when certain entries o
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. "Billy Liddel" wrote: Fear not it has already been done. Try: http://www.xldynamic.com/source/xld.LeagueTable.html Peter "Struggling in Sheffield" wrote: 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. -- Steve Sharpe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Data Transfer | Excel Worksheet Functions | |||
Auto transfer rows from 1 sheet to another by account code. | Excel Worksheet Functions | |||
transfer data in excel between sheets | Excel Discussion (Misc queries) | |||
Auto Data Transfer | Excel Worksheet Functions | |||
counting non occur entries | Excel Discussion (Misc queries) |