• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to Validate with Conditional Formatting


New Member
I'm working on a workbook that will entail several tabs and I need one, possibly more, cells to use a formula in conditional formatting to validate entries based on dynamic input from other users. All in all, I'm looking to add as much error checking, data validation, and automation to this as possible. The ultimate idea with the soon-to-be workbook is to have three or four tabs feeding into a larger tab that will have all data listed. The first three or four tabs will have the error checking, data validation, and automation on it to help personnel see where errors are and know what blocks they must fill out in accordance with regulations and standards while the final tab will be the "birds-eye view" of all data entered in the workbook with little to no formatting in it. Here is exactly what I'm looking for in regards to my question and the assistance I need:

1. Initial Action: Data Input from a drop-down list in Column "V" of the "Evaluation Rating Scheme" tab.

2. Check/Validation: Use a table in the data tab to validate the entry meets regulatory standards. In the data tab, the check should be using the concatenated (or information) listed in Column "T". If that's not needed, then it needs to check Column "Q" against Column "S".

3. Conditional Formatting: Highlight black with red text if the entry doesn't meet standards and white with black text if correct in Column "V" of the "Evaluation Rating Scheme" tab.

4. Additional Function: Automatically have Column "Z" use the same validation to say "Yes" or "No" (based on the same standard from number 2) in the "Evaluation Rating Scheme" tab.

I have concatenated information in Column "W" on the "Evaluation Rating Scheme" tab, but it's not necessary for the overall scheme of the workbook. I was just attempting to figure it out on my own using all kinds of different formulas and functions that I was already familiar with. If it is not needed, feel free to delete that column.

I would greatly appreciate any assistance I can get on figuring out this puzzle. I've attached the initial "test" workbook here as well to help visualize and see what I need.