Forum Discussion
Conditional Colour Formatting. with multiple values.
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.
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"))
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.
- 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