Posts by Ace_69

    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.