Hi Everyone!
I have a dataset that looks like this:
For data validation purposes I need to conditional formatting to help highlight columns D to U with the following criteria:
Scenario 1: Region Split
1) If [Column A] SKU = SKU (because SKU can have duplicates across multiple rows)
2) And if [Column D] = "Regional split"
Then there must be a corresponding "X" in Column D to I.
Correct validation:
There should be a corresponding X in each Region per SKU.
Incorrect validation Example:
There is no "X" in Column F for AP value so it should be flagged out in Red.
Or if there is an "X" duplicated in Column G for CA value, it should be flagged out in Red.
Or if it is Regional Split and no "X" in Column D to I, flagged out in Red.
Scenario 2: Buy month split
1) If [Column A] SKU = SKU (because SKU can have duplicates across multiple rows)
2) And if [Column D] = "Buy Month split"
Then there must be a corresponding "X" in Column J to U.
Correct validation:
There should be a corresponding X in each Month per SKU.
Incorrect validation Example:
There is no "X" in Column J for JAN value so it should be flagged out in Red.
Or if there is an "X" duplicated in Column K for FEB value, it should be flagged out in Red.
Or if it is Buy Month Split and no "X" in Column J to U, flagged out in Red.
I have been cracking my head with this for weeks now and decided to seek help in forum! :'(
If anyone has an idea to share, it would be deeply appreciated!
Thank you in advance!