Cell Entries Validation

[Kellyscoffee]

Hi All. I need to determine a YES or NO condition if Documents OK in column G, if the relevant cells coloured by filter selection in Column A are populated with data. Thanks in advance.

[AliGW]

Welcome to the forum.

Based on your description and NO mocked-up results (!), try this:

=IF(A2="","No","Yes")

[Kellyscoffee]

Sorry Ali, in the case of filtered condition cell A2, I need to determine if document details have been added to cells E2 and F2 to then state in G2 that documents are OK with a Yes. So if cell E2 is populated and F2 is not populated then documents OK is No. Hope this helps.

[AliGW]

Yes, I suspected you had not stated the case clearly enough!

Try this:

=IF(A2="","",IF(AND(A2<>"",OR(E2="",F2="")),"No","Yes"))

[TMS]

One way, G2:

Please Login or Register  to view this content.

copied down.

You can simplify your Conditional Formatting a lot. Please see the updated example.

[Kellyscoffee]

Great that works perfectly TMS. Thanks AliGW for your assistance. Much appreciated.

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

[Kellyscoffee]

Sorry not quite finished. If the filter cell A2 is blank and does not have any filter condition applied, how does the formula change to negate the use of Yes or No in documents ok in G2?

[TMS]

Not sure exactly what you are looking for. If there is nothing in column A, my formula will return blank in column G. And the CF formulae will clear the formatting.

What do you want to happen? You didn’t have any examples of "error" conditions.

[Kellyscoffee]

Hi TMS, yes the formatting does clear. But lets take a cell in column A where the filter has been applied and I delete the filter so the cell is empty, the result in column G will say NO, whereas I would like this blank in this condition. Hope that helps?

[AliGW]

As I suggested in my second formula:

=IF(A2="","",your_formula)

[TMS]

Ah, I guess you are talking about Ali's formula as that returns blank, yes or no. Mine will only return blank (if it is blank if column A is blank or the combination is not valid) or OK (if the combination is valid).

[Kellyscoffee]

Brilliant both options work well. I need to determine now, which one best suits the application. Many thanks for your time and support today.