Using IF AND OR in a complex formula to find blank cells.

[LadyCx71]

Hello All

I am working with IF AND OR in my formula but continually get an error. The challenge
Column A has results of Q1, Q2, Q3 or Q4 to represents periods in a year.

If Column A has a result of Q1, columns B cannot be blank, otherwise it is an error - this I have worked out using =IF(AND(A2="Q1",B2=""),"Error","")

i am struggling to expand the Formula, If Column A has a result of Q2, column B should be blank, column C alone, C and D or C,D and E has to have information in that cell or it is an error message.

So If Column A shows Q3 to have a pass result Column A and B needs to be Blank and column C alone or C and D needs to be populated with information, all other columns populated is an error
This is the same for resut of Q4, Columns A,B and C, needs to be blank and Column D populated. Any other inputs should have an error


I really hope someone can help as I have been trying to do this for the last 3 days

[windy58]

Can you upload a sample showing the expected results?

[LadyCx71]

Thank you. Below is the results I am trying to get.

Qtr inv1 inv2 inv3 inv4
Q1 Pass Pass Pass Pass
Q2 Error Pass Pass Pass
Q3 Error Error Pass Pass
Q4 Error Error Error Pass


Rationale
Essentially I an trying to identify errors. Q1 represents the quarters of a year, so Q1 is January 1 to March 31. And if we plan to complete work for a customer during that period we should issue an invoice in inv (phase) 1, and subsequent phases if there is a payment plan

If the customer's work is scheduled to completed in Q2, April 1 to June 30th. We should not have payment between Jan 1 and March 31, as a customer will not pay before the work is completed. Hence the input error should be highlighted.


Thank you

[windy58]

You could use a vlookup.
You need to create a list of the combinations and their outcomes then use =VLOOKUP(A8&$B$7,Lookup,2,FALSE)
See attached

Windy

[LadyCx71]

Thank you so much for your help with this, I think I am getting there, but want to ask will this work if the cells are already populated with information that is not pass or error, as the cells will have a numerical value. I have added a sample of how it will really look rather than the pass/error outcome that I want.

Once you have seen the table if the input as such green = pass result and yellow = error
LadyCx2 reply.xlsx