How to make this formula show "PENDING"?

[Marq]

Below is a formula I have in a cell that calculates based on choices made from two drop down menus....the drop downs have the word "PENDING" when nothing as been chosen yet.

How do I made the cell that the formula is in show "PENDING" when either D3 or D4 (or both) are in PENDING status?

Right now the cell shows "FALSE" when D3 and D4 are in pending status....and it shows FALSE if either D3 or D4 are in pending status.

=IF($D$3="st",IF($D$4="Yes",$M$5,IF($D$4="No",$M$3,IF($D$4="PENDING",""))),IF(D3="ot",IF($D$4="Yes",$M$6,IF($D$4="No",$M$4,IF($D$4="PENDING","")))))

 

[Fluff]

What should happen if D3 is Pending but D4 and/or D5 are not?

 

[Marq]

What should happen if D3 is Pending but D4 and/or D5 are not?

D57 should be PENDING

If I don't have a Straight time or overtime choice made in D3 then PENDING should be in D57 regardless what D4 & D5 have in it.

 

[Fluff]

Then maybe

=IF(D3="Pending","Pending",D3&IF(AND(D4="No",D5="No"),"","-LEAD/ASB"))

 

[Marq]

Then maybe

=IF(D3="Pending","Pending",D3&IF(AND(D4="No",D5="No"),"","-LEAD/ASB"))

doesn't work...because if I chose ST in D3 and leave d4 & d5 as pending then D57 populates with ST-LEAD/ASB

It should still read pending because I haven't told the estimate if the job contains lead or asbestos...so how can it put the ST-LEAD/ASB billing code in D57??

 

[Fluff]

how can it put the ST-LEAD/ASB billing code in D57??

Because that's virtually what you asked for.

If D3="PENDING" and D4 = PENDING and D5=PENDING, PENDING
If D3 = ST and D4 = PENDING and D5 = Yes, ST-LEAD/ASB
If D3 = ST and D4 = Yes and D5 = PENDING, ST-LEAD/ASB
If D3 = OT and D4 = PENDING and D5 = Yes, OT-LEAD/ASB
If D3 = OT and D4 = Yes and D5 = PENDING, OT-LEAD/ASB

Whilst you didn't stipulate both D4 & D5 as pending, it appeared (from the above) that it would lead/asb

 

[Marq]

Because that's virtually what you asked for.

Whilst you didn't stipulate both D4 & D5 as pending, it appeared (from the above) that it would lead/asb

my bad..i inadvertently left that scenario out...i added them at the bottom of the list

If D3="PENDING" and D4 = PENDING and D5=PENDING, PENDING
If D3 = ST and D4 = PENDING and D5 = Yes, ST-LEAD/ASB
If D3 = ST and D4 = Yes and D5 = PENDING, ST-LEAD/ASB
If D3 = OT and D4 = PENDING and D5 = Yes, OT-LEAD/ASB
If D3 = OT and D4 = Yes and D5 = PENDING, OT-LEAD/ASB
If D3 = ST and D4 = PENDING and D5=PENDING, PENDING
IF D3=OT and D4 = PENDING and D5=PENDING, PENDING

 

[Fluff]

In that case

=IF(OR(D3="Pending",AND(D4="Pending",D5="Pending")),"Pending",D3&IF(AND(D4="No",D5="No"),"","-LEAD/ASB"))

 

[Marq]

In that case

=IF(OR(D3="Pending",AND(D4="Pending",D5="Pending")),"Pending",D3&IF(AND(D4="No",D5="No"),"","-LEAD/ASB"))

dang...still no...its almost there..very close.

its not working with this combination:

D3=ST and D4=NO and D5=PENDING...D57 should be pending...its showing up as ST-LEAD/ASB
D3=OT and D4=NO and D5=PENDING...D57 should be pending...its showing up as OT-LEAD/ASB
D3=ST and D4=PENDING and D5=NO...D57 should be pending...its showing up as ST-LEAD/ASB
D3=OT and D4=PENDING and D5=NO...D57 should be pending...its showing up as OT-LEAD/ASB

So basically its working with the "Yes" and "PENDING"'s in D4 & D5 combinations and not working with the "NO" an "PENDING" combinations D4 and D5

 

[Fluff]

May I suggest you read post#26 and make up your mind as to EXACTLY what you want.

 

[Marq]

May I suggest you read post#26 and make up your mind as to EXACTLY what you want.

Here we go...from the top for cell D57

The scenarios if D3 Is Pending:
IF D3=PENDING and D4=PENDING and D5=PENDING then D57 =PENDING
IF D3 = PENDING and D4=Yes and D5=PENDING then D57= PENDING
IF D3=PENDING and D4=No and D5=PENDING then D57 = PENDING
IF D3=PENDING and D4=PENDING and D5=Yes then D57=PENDING
If D3=PENDING and D4=PENDING and D5=No then D57=PENDING
IF D3=PENDING and D4=Yes and D5=YES then D57 = PENDING
If D3=PENDING and D4=No and D5=No then D57 = PENDING
If D3=PENDING and D4=No and D5=Yes then D57= PENDING
If D3=PENDING and D4=Yes and D5=No then D57=PENDING

So ANY scenario where D3 is PENDING...REGARDLESS what D4 and D5 have, D57 should always be PENDING. Why? Because I have not yet told the formula if the estimate will use a ST (straight time) billing rate or an OT (over time billing rate)...the ST and OT choice is the drop down in D3. I may know my D4 is a Yes and My D5 is a NO, but since I don't know if the job will be ST or OT, I want to leave it PENDING. Im not committing to a billing rate in the D3 scenarios'above.

Moving on.....

We'll call these the ST scenarios:

IF D3= ST and D4=Yes and D5=PENDING then D57=ST-LEAD/ASB
If D3=ST and D4=No and D5=PENDING then D57=PENDING (I believe this is one where I was confusing myself)
IF D3 = ST and D4=PENDING and D5=YES then D57=ST-LEAD/ASB
If D3=ST and D4=PENDING and D5=No then D57 is PENDING (I believe this is one where I was confusing myself)
IF D3=ST and D4=NO and D5=NO then D57=ST
If D3=ST and D4=YES and D5=YES then D57=ST-LEAD/ASB
IF D3=ST and D4=Yes and D5=No then D57=ST-LEAD/ASB
If D3=ST and D4=No and D5=Yes then D57=ST-LEAD/ASB

So any scenario where D3 Is ST and ANY D4 & D5 combination that contains at least one YES, then the ST-LEAD/ASB billing rate is chosen. Why? Because If the job contains lead OR contains asbestos then the LEAD/ASB billing rate has to be chosen. Thats why if I have a combo of a D4=NO and a D5=PENDING, then D57=PENDING because I have determined by this point the job does not contain lead but I'm waiting on the asbestos results...so I do not want to commit to a billing rate just yet...so im leaving it PENDING until I get that asbestos result in. Also, it is why I CAN commit to a LEAD/ASB rate if either D4 or D5 is a YES....so if I know the job contains lead, I chose YES in D4...then I know automatically I will be using the ST-LEAD/ASB rate regardless what the asbestos results come back...and visa-versa..if I KNOW the job contains asbestos I chose YES in D5 and may have D4 as PENDING (because i haven't gotten lead results back yet) the billing rate automatically goes to ST-LEAD/ASB...regardless what those lead results are. Simply put, as long as D3 has an ST (or an OT) I can commit to a billing rate if I have a YES in either D4 or D5....I can't commit to a billing rate If I have a D4 NO and a D5 PENDING or a D4 PENDING and a D5 NO...so If I can't commit to a billing rate just yet, then D57 should remain as PENDING.

We'll call these the OT scenarios:
Same rules apply as right above ..just change all the ST's to OT's

IF D3= OT and D4=Yes and D5=PENDING then D57=OT-LEAD/ASB
If D3=OT and D4=No and D5=PENDING then D57=PENDING (I believe this is one where I was confusing myself)
IF D3 = OT and D4=PENDING and D5=YES then D57=OT-LEAD/ASB
If D3=OT and D4=PENDING and D5=No then D57 is PENDING (I believe this is one where I was confusing myself)
IF D3=OT and D4=NO and D5=NO then D57=OT
If D3=OT and D4=YES and D5=YES then D57=OT-LEAD/ASB
IF D3=OT and D4=Yes and D5=No then D57=OT-LEAD/ASB
If D3=OT and D4=No and D5=Yes then D57=OT-LEAD/ASB

So again, any scenario where D3 Is OT and ANY D4 & D5 combination that contains at least one YES, then the OT-LEAD/ASB billing rate is chosen.

I hope this clears up any misunderstandings....it helped me understand it better. I think where I was screwing myself up and everyone else was, I kept writing what I was wanting and it was hard to keep track and also the scenarios where i listed I was confusing myself, I was saying one thing but meaning something else...once I wrote out the senario I was like "wait a minute....somëthing isn't isn't right here"...I literally said the combinations out loud to to myself about 5 times and then it clicked..i had that ÄHHH HAAAA"moment.

I am so sorry for any confusion and I appreciate you zand others helping me....What I laid about above is what I am looking for.