Team history

[Patcheen]

I'm trying to set up a database of the history of our local club Example - Our team, have met Team 1 twice (See team info)

What i want is when i select the team from the drop down list i want it to show all meetings between the 2 teams in the
team info sheet

the team info sheet gets all the information it need from the Team Database -

the team sheet (Which will be hidden) is where i enter the teams that have been played against and will reflect in the drop down list

i have currently entered 10 teams but this will increase as i enter team names in time....

Probs are -

I cant get the date to come from the team database to team info

cant get the team database info to come to the team info sheet

All help appreciated thank u

[ChemistB]

On Team Database, you have Score (score of team in Col A), Our team (score of your team) and then "For" and "Against" What are those 2 columns? fouls?

What exactly are you trying to get on Team Info sheet? Do you want the sum of the stats against that team or do you just want to pull, row by row, the results from that team? Looks like the first in which case, you won't be pulling the date unless you want all the dates that you played that team also?

[Patcheen]

For is goals scored by our team - Against is goals scored against out team

in the team info sheet i want the date with the corresponding result

Example - Played team 1 on 15/5 lost 1 - 0 so on the team info sheet in the date column it will show the date and the corresponding scores that were entered in the team database

[Patcheen]

Anyone got any other ideas?

[ChemistB]

I used this arrayed formula in Team Info A5 copied down and across to E5

=IFERROR(INDEX('Team Database'!B$2:B$7, SMALL(IF('Team Database'!$A$2:$A$7=$G$1, ROW('Team Database'!$A$2:$A$7)-ROW($A$2)+1), ROWS($A$1:$A1))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
See attached

[micope21]

Hi

As you on excel 2010? You can use Aggregate without Ctrl+Shift+Enter Array formula? Only work excel 2010 onward, Won't work if you on excel 2003 or 2007

A5

Please Login or Register  to view this content.

enter then copy cross and down

D5

Please Login or Register  to view this content.

Enter copy down

F5

Please Login or Register  to view this content.

Enter then copy down and cross

IF AGGREGATE does not work for you?

The use this

Please Login or Register  to view this content.

This is Array formula, Press same time Ctrl+Shift+Enter till you see like this {}

See the files