Conditional Colour Formatting. with multiple values.

[Ladda_97]

Hello.

I'm new here and a pretty basic user of excel. I work in print and use Excel to make Datamerge (DM) files in Adobe InDesign. This is a multiple party process and we've had a couple of issues so I'm looking to add a redundancy to make for less issues. 
We have a product that changes it's DM layout depending on which location it's going to. I have two template sheets that we use and I'm looking to set it up so that a cell changes colour to Red if it's the wrong location listed. 

I.E. I'm using the sheet layout for Locations A-C but I accidentally put in Location D into the sheet I want location D to turn Red and Locations A-C to turn Green. The location Names don't ever change but I have some 100+ Locations that I need to set it up for.

I've gotten it to work for Location D using the conditional Formatting rules with 
=Sheet1!$C$2=Sheet 2!$A$2:$A$33 but if I insert Location E it just turns back to white.

Sorry if this is rambling. 
Also sorry if the answer is obvious I tried to make it work with Google but couldn't get it so I turn to this community

[#2 · Accepted solution]

@Ladda_97 

Let me suggest the following (as long as I understood correctly your problem):

On each locations sheet, set up a list with all the "correct" and valid locations for that specific sheet. Let's say you list your valid locations from B1:B50.

Then, you wish to enter 1 location in A1 and see if that location is within the valid locations to enter in that sheet, which those are listed in B1:B50.

On A1, go to conditional formatting in the home tab. Click on new rule and then on "Use a formula to determine which cells to format". On the designated space, write the following:

=IFERROR(MATCH(A1,B1:B50,0),"Wrong")="Wrong"

And then set the fill colour format to red.

Again, do the same procedure with a new rule and insert the following:

=ISNUMBER(IFERROR(MATCH(A1,B1:B50,0),"Wrong"))

And then set the fill colour format to green.

Hope this functionality suits you well.

[#3]

Hey. So this seems to be a step in the right direction but it's not working quite right. When there is no value it goes red, and if the value is correct it stays Red
=ISNUMBER(IFERROR(MATCH($C$2,Sheet2!$A$2:$A$48,0),"Wrong"))
=IFERROR(MATCH($C$2,Sheet3!$A$2:$A$33,0),"Wrong")="Wrong"
is what I currently have

Edit.: ok I got the green version working using 
=ISNUMBER(IFERROR(MATCH($C$2,Sheet 2!$A$2:$A$48,0),"Wrong"))
Also the Green Version seems to only be looking at the C2 Cell and not the matching cell in the same row as I pull it down.
However I still cant quite get the Red one working as it currently goes Red when empty and white when filled

=IFERROR(MATCH($C$2,Sheet 3!$A$2:$A$33,0),"Wrong")="Wrong"

Edit 2: I got both versions working. I just duplicated the Red script and made one version Green and one Version Red. However the issue I was having was the inclusion of the $signs. (again very basic Excel User but apparently the $ signs mean that it's only going to look in the one Cell (didn't know that)) so my final script was 
=ISNUMBER(IFERROR(MATCH($C2,Sheet2!$A$2:$A$48,0),"Wrong"))
=ISNUMBER(IFERROR(MATCH($C2,Sheet3!$A$2:$A$33,0),"Wrong"))

[#4]

@Ladda_97 

Very glad it finally worked and the fact that you corrected it by yourself! I am sorry to not to provide an accurate reply at first, though.

[#5]

Thats all good. It still set me in the right direction and then I was able to work it through myself.
Still greatly appreciated for the help you were able to provide