Forum Discussion
Error message on already completed cells in excel
Hi All
I've created a dynamic drop down list where cell B displays a drop down list based on a selection in cell A. E.g. Cell A is "category" of which 2 options are valid, namely a. fruit or b. vegetables. If the user selects "fruit" in cell A, cell B will only display a selection of fruit. If the user selects "vegetables" in cell A then cell B will only display a list of vegetables.
How do I show an error retrospectively. E.g. In round one the user selected vegetable in cell A, and also selected, let's say, "tomato" in column B. Which at the time is correct, therefore no error messages were displayed.
But what happens when the master list is updated and now "tomato" is no longer a valid entry because it is recategorised as a fruit. How can I ask excel to highlight that cell B is not matching any options associated with cell A.
Any ideas?
5 Replies
- You can use conditional formatting to highlight cells in column B that do not match any options associated with cell A. Here's how you can do it:
1. Select the range of cells in column B that you want to apply the conditional formatting to.
2. Click on "Conditional Formatting" in the "Home" tab of the ribbon.
3. Select "New Rule".
4. Select "Use a formula to determine which cells to format".
5. In the formula bar, enter the formula `=AND($A2="fruit",NOT(ISERROR(MATCH($B2,fruit_list,0)))))` if "fruit" is selected in cell A or `=AND($A2="vegetables",NOT(ISERROR(MATCH($B2,vegetables_list,0)))))` if "vegetables" is selected in cell A. Replace "fruit_list" and "vegetables_list" with the range of cells that contain the valid options for each category.
6. Click on "Format" and select the formatting that you want to apply to cells that meet the condition.
7. Click on "OK" to close the dialog boxes.
This should highlight any cells in column B that do not match any options associated with cell A.
Conditional formatting is a feature in Excel that allows you to apply formatting to cells based on certain conditions or rules.Thank you very much for the reply! I just realise that I didn't express myself quite clearly in the original post. In this case the category is not static. Example, today the category can be named "fruit" tomorrow it can be anything else.
I'm creating a template where a user can decide what the category is. So, I need to insert a formula where it points to a specific cell, irrespective of its name.
To create the original dropdown list I created a dependent dropdown by using the "offset" & "match" capability to cater for the dynamic properties of the capabilities.
So, instead of saying "fruit" in the formula, how do I point excel to a dynamic range? I have 6 possible categories, with 6 possible related entries which can be anything as per the user's original entries.Did you look at the sample workbook that I attached? It can easily be modified to work with a dynamic category list.
See the attached sample workbook. I used a conditional formatting rule of type 'Use a formula to determine which cells to format'.
- Thank you very much!!
