Conditional formatting using Index(Match()) - faulty when 1 criteria has duplicates

[Andrewjs]

Hi,

I've attached a mocked sample workbook of what i'm doing.

There are 3 sheets:

• Main-Internal --- This is a general database of default information
• Public-External --- This is a database of special instructions if a specific customer be linked to a product
• Entry-Database --- This is where i operate and enter new rows of data regularly

Cell formula:

There's one main formula in play, in my "G" column on the 'Entry-Database' sheet. It essentially says:
If i have not entered a customer name in "E", then go with the default comments for that product from Table2 on sheet 'Main-Internal', but if i did put a customer in "E" then output the remark for that product specifically tailored to that customer found on Table3 on sheet 'Public-External'.

Conditional formatting (validation):

• Product exists (Fill cell color green)
  

  Please Login or Register  to view this content.

• Customer Exists (Fill cell color green)
  

  Please Login or Register  to view this content.

• Product name does NOT exist on the External/Customer table (Fill cell color purple)
  

  Please Login or Register  to view this content.

My problem - The 3rd conditional format (purple):

This seemed to work alright initially... But as soon as i listed more than one row for the same customer, but different product name & comment, it only recognizes the topmost sorted value.

So even though i want all of the below values to show up green (if not blank), only one will stay green while the rest (from the same customer name) show purple even though they do exist in the table and are associated with that customer name. Is there a better way to write this so a cell only fills purple if that product name is indeed NOT in 'Public-External'!$B:$B ?

Thanks

[TheN]

I'm confused as to what you are trying to achieve here, sorry.

Could you possibly mock it up so we can see expected results of the area you are having trouble with. This sounds quite easy to achieve, but I don't quite grasp your explanation fully. Can you possibly color the cells purple as they should be manually, so I can see what it is you are asking for. There is no conditional formatting applied to this mock up either, so maybe it is not the version you wished to post?

[Andrewjs]

Oh weird - The conditional formats are in the 'Rules Manager' for me when i download it, for Table5. Hmm well yes let me see if i can be a little more clear.

Mainly,

-I want to be alerted in some way (the cell color change to green) when i enter something on Sheet3!$F:$F that does not exist in Sheet1!$A:$A

-I also want to be alerted (another green fill, diff column) when i enter something on Sheet3!$E:$E that does not exist in Sheet2!$A:$A
(Often times i wont enter anything in Sheet3!$E:$E and leave it blank, this is normal and fine)

Both of those seem to work fine, no problem. Below has been hard to figure out how to write properly.

-When i DO enter something in Sheet3!$E:$E, that means i will want to be alerted (fill cell purple) if Sheet3!$F:$F does not exist in Sheet2!$B:$B

^ The trick to that is, i need it to check a little more than whether or not it exists in Sheet2!$B:$B. It needs to exist there AND be in a row that Sheet2!$A:$A value matches Sheet3!$E:$E
Which my existing condition seems to do, until it realizes that in some cases Sheet2!$A:$A can have several rows containing identical text.

I'm hoping that clears something up - i've gotta run and don't have time at the moment to re-mock, but i will tomorrow if there's no response.

[Andrewjs]

Ok i've uploaded an updated mock which i've removed conditional formulas and inserted a few notes onto Sheet3.

Let me know if there's any more info i can provide or make clearer.

Main focus is the purple fill - don't worry much about green.

[Andrewjs]

Bump - Anything on this anybody?

[JeteMc]

If I understand correctly you want the cell to fill purple if it contains a product that is in the main - internal sheet but is not associated with a customer in the public - external sheet. Seems to me that is what the formula in column G of the entry - database sheet already targets, with the #Value error. That being the case you could use the following as a formula applied to the range F2:F16 on the entry - database sheet: =ISERR(G2)
Notice that in the attached copy of your file I did as you suggest and add one of the lines (office supply place - binder) to the table on the public - external sheet which causes the purple fill to disappear.
Let us know if you have any questions.

[Andrewjs]

Oh, cool! That seems so simple.

My real workbook doesn't actually have the formula output of G2 on the same table i would actually enter data and want to see the purple - but i just put the same formula in a hidden column and this works great!

Thanks a lot, seems bug-free so far.

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.