Set a cell to blank if a value is selected in the adjacent cell across all rows

Hi,

Below is a simple table just to illustrate my query.

Let's say I have the following table where the user can select the value "Yes" from a drop down (an excel list with only the value "Yes" to control what can be entered) in cols. Home and Away.

Rule:

• For each row you can only have "Yes" in one column, i.e if you select "Yes" in Home then Excel must set Away to blank.
• If you have "Yes" already in Home and the user selects "Yes" in Away, then Home must be set to blank
• basically the user can change their mind repeatedly after selecting "Yes" in either column (Home or Away) and Excel must set the other column to blank each time

I need this to work for all rows on the same sheet, irrespective of the amount of rows on the sheet (technically not expecting this to exceed 2000). Is there a way to make this work without using VBA as I would like to save the file without macros (using excel 2010)? If not VBA is fine.

[TABLE="border: 1, cellpadding: 1, width: 500"]

[tr]

[td][/td]

[td]

Home

[/td]

[td]

Away

[/td]

[/tr]

[tr]

[td]

Fixture 1

[/td]

[td]

Yes

[/td]

[td]

<<set to blank>>

[/td]

[/tr]

[tr]

[td]

Fixture 2

[/td]

[td]

<<set to blank>>

[/td]

[td]

Yes

[/td]

[/tr]

[tr]

[td]

Fixture 3

[/td]

[td]

Yes

[/td]

[td]

<<set to blank>>

[/td]

[/tr]

[tr]

[td]

Fixture 4

[/td]

[td]

Yes

[/td]

[td]

<<set to blank>>

[/td]

[/tr]

[tr]

[td]

Fixture 5

[/td]

[td]

Yes

[/td]

[td]

<<set to blank>>

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[/TABLE]

Many thanks for your help.

Thanks Carim - I didnt think it could be done without using VBA but thought better to ask just in case. Your solution worked a treat. Kudos.:cheers: