How to make this formula show "PENDING"?

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
903
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
If D4 & D5 are both pending, but D3 is not should that still return pending?
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
903
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
If D4 & D5 are both pending, but D3 is not should that still return pending?
Yes..if D3 if ST or OT and D4 & D5 are both PENDING then D57 it should be pending

Why? In that scenario u mentioned, it would be me knowing the job won't be worked on overtime...it will be worked as straight time..BUT Im still waiting on the lead and asbestos results to come in....so I still cant commit to a billing rate until I get the lead and asbestos results back (for D47 D5)….so basically I have one factor of the job known: I know we wont work this job OT, so I can chose ST in D3.....but I wont know if the billing rate will be ST or ST-LEAD/ASB until I get the results back.....so I need to leave it as PENDING.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=IF(D3="Pending","Pending",IF(OR(D4="Yes",D5="Yes"),D3&"-LEAD/ASB",IF(COUNTIF(D4:D5,"Pending")>0,"Pending",D3)))
 
Solution

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
903
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
Ok, how about
Excel Formula:
=IF(D3="Pending","Pending",IF(OR(D4="Yes",D5="Yes"),D3&"-LEAD/ASB",IF(COUNTIF(D4:D5,"Pending")>0,"Pending",D3)))
THAT''S IT!!!!!!!!!!!!!!!!!!!!!!

man oh man!!..u did it!...I cant begin to tell you how much this helps me out....Im about to have a ton of 2021 estimates to do and this wlll help streamline the process for me and others.

Thanks so much!...this case is closed!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,903
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,809
Messages
5,574,435
Members
412,592
Latest member
moonsugar
Top