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
Can excel compare one cell to a list?
I'm using conditional formatting on a drop down list. Because I've already named the whole list on another sheet in order to put the values into the drop-down I was hoping that I could just use the name of the list (eg. =([cell #]<>[name of list]) then change the colour of the cell to red). But I'm guessing the reason that didn't work is because Excel now thinks that the cell should contain the WHOLE list, not just any value from it.
Is there a faster way than just doing a separate argument for each list item? If it was a range of numbers it would be easy but the values are text so I can't do it that way.
How do I refer to a range of cells on another sheet, though? I put it on Sheet 3 to keep it out of the way and stop the sheet with the form on it getting messy.
1 Answer
- ?Lv 68 years agoFavorite Answer
You need to test your value against each value in the whole list.
Say your list is in E2:E20 and your value is in A2. You're testing for A2 being equal to any of E2 through E20. You return 'True' if it is, 'False' if is isn't.
=IF(ISNA(VLOOKUP(A2,E2:E20,1,0)),"False", "True")
(Yahoo changes the format for some reason, the end of the line is
quote False quote comma quote True quote close bracket!)
[edit : for the list on another sheet just reference it there as normal. So if your list was on Sheet4 in E2 to E20 just put Sheet4!E2:E20 as the reference. If you named the list, say, Keith, then reference it as Sheet4!Keith ]
