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:


    Glad you could fix your problem ... :wink:


    Thanks a lot for your Kudos ... AND for the Like ... :smile:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!