Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Trending News

Promoted
MrBuzz
Lv 4
MrBuzz asked in Computers & InternetOther - Computers · 1 decade ago

Excel help with lists?

I've created an equipment list in excel.

I've set it so when selecting a boat for a person you have to pick it from a drop down list, or select the value "own" if they already own a personal boat.

I have the range of boats listed on the right and it's from this range that the drop down lists are created,

I want to either set it in one of the following was. whichever is easier.

A) The text in the range turns red when the value is selected. eg. if the boat called a habitat is selected then it's colour will turn red in the list.

OR

B) The boats can only be selected once. i.e. one per person. (however multiple people can select "own" as many own a boat themselves.

Thanks very much for any help.

1 Answer

Relevance
  • Cozmosis
    Lv 7
    1 decade ago
    Favorite Answer

    Doing "A) text in the range turns red" is really easy.

    Column A is the list of Boats

    Column C is your drop down lists

    Select column A.

    Select Format\Conditional Format

    Condition 1:

    Formula is =IF(MATCH(A1,$C:$C,0)>0,TRUE,FALSE)

    Select the Format button

    Select Font and make the font color Red

    OK

    Select the cell that has "own" and delete its Conditional Format.

    Doing "B) The boats can only be selected once" I think can be done, but it's a little tricky.

    Again:

    Column A is your boat list

    Column C is your drop down lists.

    We need to make a helper column in say Column D

    Put this formula cell D1

    =IF(ISNA(MATCH(A1,$C:$C,0)),A1,"")

    and drag\copy it down the length of the list in column A

    This will be a duplicate of column A if the boat is not selected in one of the drop down lists in column C. You also need to add "None" at the bottom or top of Column D. If "own" is in say A2, you will need to make the formula in D2 just =A2.

    Now make all of your drop down lists in column C equal column D instead of column A.

    Now this solution isn't perfect. Your drop down lists will have blanks in it where an item is already selected. A person can still select a blank item but nothing happens. They just get a blank selection. That's as close as I could get.

Still have questions? Get your answers by asking now.