Grabbing data between worksheets

[andrew]

I'm assisting a school mini-league for hockey, and would have the following
table score set up.

Wk Date Home Score Away
1 13-Jul A 2 1 B
1 13-Jul C 3 0 D
1 13-Jul E 2 2 F
2 20-Jul F 0 1 A
2 20-Jul B 1 0 C
2 20-Jul D 1 1 E
3 27-Jul A 0 0 D
3 27-Jul C 2 3 F
3 27-Jul E 1 1 B

The above is a summary sheet (titled 'Fixture') of the fixtures (the scores
are just dummy data). I have 6 individual team sheets, and would like each
team sheet to grab the following:
a) list of weekly opponents for each team. Using above example, worksheet
for Team A (titled 'A') shows them playing at home for wk 1, then away wk 2,
then home again wk 1. I tried using VLOOKUP to check and display the
opponents in each row using this formula:

=IF(SUMPRODUCT(--(Fixture!A1:A9=A3),--(Fixture!A1:F9="A")),VLOOKUP(A3,Fixture!A1:F9,6,FALSE),VLOOKUP(A3,Fixture!A1:F9,3,FALSE))

where in sheet titled 'A', A3=1 (week 1) but i keep getting #VALUE!.

Is there any way to list from week 1 to week 3 within the team sheets to
show the list of opponents for easy reference? Appreciate any help, thanks!

 

[Dave Peterson]

I would do my best to keep the data on a single worksheet and use filters to see
what I want.

But if you must...

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[andrew]

just out of curiosity, if i want to use a formula is it possible?

 

[Dave Peterson]

Maybe someone will jump in with a formula suggestion.

 

[Max]

just out of curiosity, if i want to use a formula is it possible?

well, you might like this relatively simple, non-array formulas play which
automates it ..

Illustrated in this sample:
http://www.freefilehosting.net/download/3j9b3
AutoCopy Lines to Indiv Sheets by Key Col.xls

Construct:
In sheet: Fixture (the "master")
Assume source data as posted is in cols A to F, data in row2 down,
with the key col = col C (Home)

List the teams in H1 across: A, B, C, etc (can be in any order)
Put in H2: =IF($C2="","",IF($C2=H$1,ROW(),""))
Copy H2 across/fill down as far as required to cover the max expected extent
of data in col C

Click Insert > Name > Define
Put under "Names in workbook:": WSN
Put in the "Refers to:" box
=MID(CELL("Filename",INDIRECT("A1")),FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)
Click OK

The above defines WSN as a name we can use to refer to the sheetname in
formulas. It will auto-extract the sheetname implicitly. Technique came from
a post by Harlan.

Then, in a new sheet named after one of the teams, eg: A
With the same col headers pasted into A1:E1
Put in A2:
=IF(ROWS($1:1)>COUNT(OFFSET(Fixture!$G:$G,,MATCH(WSN,Fixture!$H$1:$IV$1,0))),"",INDEX(Fixture!A:A,SMALL(OFFSET(Fixture!$G:$G,,MATCH(WSN,Fixture!$H$1:$IV$1,0)),ROWS($1:1))))
Copy A2 across to F2, fill down to say F20, to cover the max expected extent
for any team. Cols A to F will return only the lines for the team: A from
"Fixture", with all lines neatly bunched at the top.

Now, just dress sheet: A up as desired, then make copies of it, rename each
copy as the other teams: B, C, D, E, F and you'd get the corresponding
results for those teams. Adapt to suit ..
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---