Filtering data

**Stefan22** · 05-10-2010, 09:36 AM

This driving me nuts as i'm completely newbie in Excel.

In the first column i have the home teams and away teams in the second.What i'm trying to do is to make combo box in the third(or wherever) which links up those two columns.

f.e.

home(1) away(2) overall(3)???
Arsenal Chelsea 1-1
Chelsea Manchester 1-0
Manchester Arsenal 0-0

So if i click Arsenal from the list(autofilter) in the first column(1) it apears Arsenal-Chelsea,same action(clicking Arsenal from the list) with the second column(2) gives Manchester-Arsenal.What i need is combo box in the third column(3)(or wherever) which,when i click f.e. Arsenal from its drop-down list,shows all Arsenal's matches(no matter if it was at home or away).

Hope that was clear.

Tnx in advance.

**NBVC** · 05-10-2010, 09:41 AM

You would need to create a formula in the 3rd column

e.g

=OR(A2="Arsenal",B2="Arsenal")

and copy down..

Then filter for TRUE.

Note: You can enter the criterial... "Arsenal" without quotes, say in C1

Then in C2: =OR(A2=C$2,B2=C$2) copied down

And filter for TRUE.

So when you change the word in C1, then a different set of TRUE statements come up to filter for.

**Stefan22** · 05-10-2010, 10:17 AM

Thanks for quick response mate.

The problem is that the process you describe is too slow(typing in the first place) and doesn't fit my needs.I need to filter it with just one click,as in the both first and the second column(home and away).

Is there any way to create combo box in C1 and just pick the team from a drop list?Possible?