Too Many If's - Help to simplify

[batman1056]

This is what I am trying to do
if F1="Yes", iF K1 ="Q1" and IF B1 is blank then "yes", if K1="Q2" and IF C2 is blank then "Yes", If K1="Q3" and IF D2 is blank then "Yes", If K1="Q4" and IF E2 is blank then "Yes"

Basically colomns Q1,Q2,Q3,Q4, LIVE and Pending
Depending on a value in K1 (which is a drop down selector consisting of Q1-Q4) I want it to check that its live "Yes" in F1 then look for the the correct period and tell me if any data is missing - if it is then give me a Yes.

Is there a simpiler way then using all the IF's

Thanks

[Blake 7]

Hi there Batman,,,, na nana naananana bang pow smash crash!

I tried to follow your post but got lost half way through.

May be a good idea to post a sample of your workbook describing what your desired output is.

Would make it easier to follow. for those with half a brain like me!

[Ron Coderre]

I *think* I understand what you're looking for...
Try this formula:

Please Login or Register  to view this content.

Is that something you can work with?

[NBVC]

Perhaps?

=IF(F1="Yes",IF(OR(AND(K1 ="Q1",B1=""),AND(K1="Q2",C2=""),AND(K1="Q3",D2=""),AND(K1="Q4",E2="")),"Yes",""),"")

[Blake 7]

Wow Ron - nicely followed! I took a wrong turning somewhere!!

[sailepaty]

Try this,

=IF((F1="Yes")*(CHOOSE(MID(K1&"00",2,1)+1,"No",B1,C2,D2,E2)=""),"Yes","No")

or

=REPT("Yes",(F1="Yes")*(AND(K1="Q1",B1="")+AND(K1="Q2",C2="")+AND(K1="Q3",D2="")+AND(K1="Q4",E2="")))

Regards

[Ron Coderre]

If K1 will always be one of these: Q1, Q2, Q3, Q4
Then this may be a possibility:

Please Login or Register  to view this content.

[batman1056]

I went with this in the end

=IF(INDEX(ragy,ROW(B3)-1,MATCH($K$1,qtrs,0))="","Pending","Other")

Many thanks for the other ideas - I just added the check at the front of the above to see if its Live and its works a treat. will play about with your sugestions thanks

Uploaded the solution so people can see what I was trying to do

Edd

[Ron Coderre]

I'm glad we could help...Thanks for posting your formula!