Data validation - Modify source value then give feedback to user if value in cells is different

[kmoreau48]

Hello everyone,

I searched on the forum and couldn't find a post that has a concern similar to mine.

I have a column with data (project number) which is the source of a data validation list of multiple cells. Let's say my list of project goes like this: AAA, BBB, CCC, DDD
Then in the cells with the drop-down list, I choose the values and populate a couple of cells with the project numbers.

For some reason, there was a mistake in the project number DDD, and it should have been DDD-1. If I try to enter the value DDD-1 in the cell, I will get an error because DDD-1 is not in the list of project. So I have to change the value DDD to DDD-1. My concern is that other users expect the DDD to change to DDD-1 in every cells, but that is not the case. Also there is no error pop up that DDD is not in the drop-down source list.

Is there a way to show the user that the value in the cells is no longer present in the list of project and that the user needs to change the value in those cells?

I was thinking about a conditional formatting where it could highlight the project list if there is a value in a cell that is not present in the project list. This way when the user delete or modify the project list, it will highlight itself right away until all the values in the cells are the same as the ones in the project list. If anyone know how this can be done as well that would be great.

PS: I would like to avoid using VBA

Thanks

Kevin

 

[Chrisdontm]

Here is how I would approach it;

I would make my Data Validation List a Table with one column.

Then name the range of the column and use that name in the Data Validation list.

In my sheet, the Table is called Table2, so the Data Validation entry will be;

=Table2[Column1]

Then if you had to add to the list you just type the new word at the bottom of the column

in the Table…..then it will be in the Data Validation drop downs immediately from then on.

For the sake of this example the named range will be: " The_List "

Lets say that your column needing Data Validation is in Column " B " from row 6 to 20.

I would highlight the range of B6:B20 and click on Conditional Formatting,

Go down and choose: " New Rule…"

Then choose: " Use a formula to determine which cells to format "

Then in the bar provided I would put this formula;

=COUNTIF(The_List,B6)=0

( Notice that the formula only uses the first cell of the range, since you highlighted the range

it will apply it on down the highlighted range. )

Then choose: " Format " and " Ok "

Then choose the tab: " Fill " ( or you could choose other tabs to change the font, or borders…)

Choose your color, and click: " OK ", then " OK ", then " OK ",

It should work at that point…

If the drop downs are in different places you will have to set a new Conditional Formatting

Rule for each range, just highlight them first each time…

Feel free to respond if I wasn't clear on something.

<colgroup><col style="mso-width-source:userset;mso-width-alt:22125;width:454pt" width="605"> </colgroup><tbody>
[TD="width: 605"]I believe you are on the right track to set some Conditional Formatting in those cells.[/TD]

</tbody>