How to make this formula show "PENDING"?

M

Marq

Well-known Member
Joined
Dec 13, 2004
Messages
914
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","")))))
 
Sort by date Sort by votes
OK, we are not communicating very well here. Let me ask try asking you a differently:

If D3 = "ST" and D4 = "Yes" and D5 = "Yes", what should be displayed in cell D12?
If D3 = "ST" and D4 = "Yes" and D5 = "No", what should be displayed in cell D12?
If D3 = "ST" and D4 = "No" and D5 = "Yes", what should be displayed in cell D12?
If D3 = "ST" and D4 = "No" and D5 = "No", what should be displayed in cell D12?
If D3 = "OT" and D4 = "Yes" and D5 = "Yes", what should be displayed in cell D12?
If D3 = "OT" and D4 = "Yes" and D5 = "No", what should be displayed in cell D12?
If D3 = "OT" and D4 = "No" and D5 = "Yes", what should be displayed in cell D12?
If D3 = "OT" and D4 = "No" and D5 = "No", what should be displayed in cell D12?

You also mentioned that "PENDING" can appear in cells D3 and/or D4.
What about D5? Can "PENDING" appear there? What is the default value for that cell?
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Joe4 said:
OK, we are not communicating very well here. Let me ask try asking you a differently:

If D3 = "ST" and D4 = "Yes" and D5 = "Yes", what should be displayed in cell D12?
If D3 = "ST" and D4 = "Yes" and D5 = "No", what should be displayed in cell D12?
If D3 = "ST" and D4 = "No" and D5 = "Yes", what should be displayed in cell D12?
If D3 = "ST" and D4 = "No" and D5 = "No", what should be displayed in cell D12?
If D3 = "OT" and D4 = "Yes" and D5 = "Yes", what should be displayed in cell D12?
If D3 = "OT" and D4 = "Yes" and D5 = "No", what should be displayed in cell D12?
If D3 = "OT" and D4 = "No" and D5 = "Yes", what should be displayed in cell D12?
If D3 = "OT" and D4 = "No" and D5 = "No", what should be displayed in cell D12?

You also mentioned that "PENDING" can appear in cells D3 and/or D4.
What about D5? Can "PENDING" appear there? What is the default value for that cell?
Click to expand...
im sorry for making it confusing and I greatly appreciate your patience and your help

FOR D12:
If D3 = "ST" and D4 = "Yes" and D5 = "Yes", ST-LEAD/ASB
If D3 = "ST" and D4 = "Yes" and D5 = "No", ST-LEAD/ASB
If D3 = "ST" and D4 = "No" and D5 = "Yes", ST
If D3 = "ST" and D4 = "No" and D5 = "No", ST
If D3 = "OT" and D4 = "Yes" and D5 = "Yes", OT-LEAD/ASB
If D3 = "OT" and D4 = "Yes" and D5 = "No", OT-LEAD/ASB
If D3 = "OT" and D4 = "No" and D5 = "Yes", OT
If D3 = "OT" and D4 = "No" and D5 = "No", OT
 
Upvote 0
Marq said:
If D3 = "ST" and D4 = "Yes" and D5 = "Yes", ST-LEAD/ASB
If D3 = "ST" and D4 = "Yes" and D5 = "No", ST-LEAD/ASB
If D3 = "ST" and D4 = "No" and D5 = "Yes", ST
If D3 = "ST" and D4 = "No" and D5 = "No", ST
If D3 = "OT" and D4 = "Yes" and D5 = "Yes", OT-LEAD/ASB
If D3 = "OT" and D4 = "Yes" and D5 = "No", OT-LEAD/ASB
If D3 = "OT" and D4 = "No" and D5 = "Yes", OT
If D3 = "OT" and D4 = "No" and D5 = "No", OT
Click to expand...
So according to this, it looks like cell D5 has no impact on anything in this formula (not sure why you mentioned it then).

I think the formula you need looks liks this:
Excel Formula: 
=IF(OR(D3="PENDING",D4="PENDING"),"PENDING",D3 & IF(D4="YES","-LEAD/ASB",""))
 
Upvote 0
Joe4 said:
So according to this, it looks like cell D5 has no impact on anything in this formula (not sure why you mentioned it then).

I think the formula you need looks liks this:
Excel Formula: 
=IF(OR(D3="PENDING",D4="PENDING"),"PENDING",D3 & IF(D4="YES","-LEAD/ASB",""))
Click to expand...
AAAHHH..i had D5 In there!!!!.....ok....I put your formula in D12 and it works!...D5 was in there because in D26, the same rules apply but in D26 case, D4 has no impact! I will put it in cell D26 and replace D4 with D5 in the formula and report back.
 
Upvote 0
I have used your formula in D26 and made the D4 to D5 change and it works. Thank you so much! All weekend I was trying to figure this out.

Now I have one more final piece of the puzzle...in cell D57, it will read BOTH D4 & D5 (along with D3)

FOR D57:
If D3 = "ST" and D4 = "Yes" and D5 = "Yes", ST-LEAD/ASB
If D3 = "ST" and D4 = "Yes" and D5 = "No", ST-LEAD/ASB
If D3 = "ST" and D4 = "No" and D5 = "Yes", ST-LEAD/ASB
If D3 = "ST" and D4 = "No" and D5 = "No", ST
If D3 = "OT" and D4 = "Yes" and D5 = "Yes", OT-LEAD/ASB
If D3 = "OT" and D4 = "Yes" and D5 = "No", OT-LEAD/ASB
If D3 = "OT" and D4 = "No" and D5 = "Yes", OT-LEAD/ASB
If D3 = "OT" and D4 = "No" and D5 = "No", OT
 
Upvote 0
Marq said:
Now I have one more final piece of the puzzle...in cell D57, it will read BOTH D4 & D5 (along with D3)
Click to expand...
It looks like it is realyl just a matter of taking the previous formula, and making the last IF use an OR clause, like we do with the PENDING part, i.e.
Rich (BB code): 
=IF(OR(D3="PENDING",D4="PENDING"),"PENDING",D3 & IF(OR(D4="YES",D5="YES"),"-LEAD/ASB",""))
 
Upvote 0
Joe4 said:
It looks like it is realyl just a matter of taking the previous formula, and making the last IF use an OR clause, like we do with the PENDING part, i.e.
Rich (BB code): 
=IF(OR(D3="PENDING",D4="PENDING"),"PENDING",D3 & IF(OR(D4="YES",D5="YES"),"-LEAD/ASB",""))
Click to expand...
its working for the D4 choices but not for the D5 choices...for example:

If I leave D4 in PENDING status and choose either yes or no for D5, then D57 still shows PENDING when it should show one of the rate codes. (ST, OT, etc..etc)

But if I choose Yes or No in D4....and leave D5 pending....then the proper rate code comes up in D57.
 
Upvote 0
Marq said:
If I leave D4 in PENDING status and choose either yes or no for D5, then D57 still shows PENDING when it should show one of the rate codes. (ST, OT, etc..etc)

But if I choose Yes or No in D4....and leave D5 pending....then the proper rate code comes up in D57.
Click to expand...
This is beginning to feel like a Catch-22 where I am trying to catch an ever-elusive moving target!
(Every time I post an answer, you seem to change the conditions!).

That is because we still have the original conditions of if D3 or D4 is "PENDING", then to return "PENDING". We did not discuss any other possibilities of a "PENDING" in D3 or D4 returning anything other than "PENDING" yet with this new situation.

So in this new current scenario, please define ALL the situations in which "PENDING" should be returned.
 
Upvote 0
Joe4 said:
This is beginning to feel like a Catch-22 where I am trying to catch an ever-elusive moving target!
(Every time I post an answer, you seem to change the conditions!).

That is because we still have the original conditions of if D3 or D4 is "PENDING", then to return "PENDING". We did not discuss any other possibilities of a "PENDING" in D3 or D4 returning anything other than "PENDING" yet with this new situation.

So in this new current scenario, please define ALL the situations in which "PENDING" should be returned.
Click to expand...
haha...if I gave it all to you at once you'd be more confused now than before. I was hoping that last working formula you gave me would be the end of it...and I used it in another column D cell and I was like "oh yeah..this particular cell needs to work off both D4 and D5".

The "moving target" is not on purpose so please bear with me.

These are the cells I put your formula in:
D12
D26
D45
D57
D69

D12 works!
D26 works!
D45 works!
D57 half way working (as I explained in previous posts)
D69 works!

I didn't want to flood you with ALL the "D" cells I need this formula in.....I figured D12 was all I needed resolved then I could just copy and paste the formula into the other D's and make the edits I needed to make them work for that particular cell (i.e. d4 d5 changes)...and everything was going swimmingly UNTIL I got to D57.....D57 is the only unique one out the bunch where if EITHER D4 or D5 are yes then LEAD/ASB rates will be applied.

So in essence, D57 would be its own stand alone formula...everything else you provided works perfect....D57 is the only scenario that needs to read the following:

(I highlighted in red the change that would be in your existing formula)

FOR D57:
If D3 = "ST" and D4 = "Yes" and D5 = "Yes", ST-LEAD/ASB
If D3 = "ST" and D4 = "Yes" and D5 = "No", ST-LEAD/ASB
If D3 = "ST" and D4 = "No" and D5 = "Yes", ST-LEAD/ASB
If D3 = "ST" and D4 = "No" and D5 = "No", ST
If D3 = "OT" and D4 = "Yes" and D5 = "Yes", OT-LEAD/ASB
If D3 = "OT" and D4 = "Yes" and D5 = "No", OT-LEAD/ASB
If D3 = "OT" and D4 = "No" and D5 = "Yes", OT-LEAD/ASB
If D3 = "OT" and D4 = "No" and D5 = "No", OT
 
Upvote 0
Here are the PENDING scenarios for D57

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

BELOW ARE THE OTHER SCENARIOS I POSTED for D57
If D3 = "ST" and D4 = "Yes" and D5 = "Yes", ST-LEAD/ASB
If D3 = "ST" and D4 = "Yes" and D5 = "No", ST-LEAD/ASB
If D3 = "ST" and D4 = "No" and D5 = "Yes", ST-LEAD/ASB
If D3 = "ST" and D4 = "No" and D5 = "No", ST
If D3 = "OT" and D4 = "Yes" and D5 = "Yes", OT-LEAD/ASB
If D3 = "OT" and D4 = "Yes" and D5 = "No", OT-LEAD/ASB
If D3 = "OT" and D4 = "No" and D5 = "Yes", OT-LEAD/ASB
If D3 = "OT" and D4 = "No" and D5 = "No", OT
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Populating row headers based on column values
2
Replies
14
Views
766
Jonam711
J
T
  • Question
VBA Macro running, but not saving to Sharepoint
Replies
3
Views
599
Fluff
Fluff
T
  • Solved
Nesting in the FILTER function
Replies
4
Views
398
Eric W
Eric W
R
  • Solved
If cell contains specific text, then return a value
2 3
Replies
27
Views
2K
Sufiyan97
Sufiyan97
U
  • Solved
Newbie Seeking Help with Ranges, Loops, and Select
2 3
Replies
27
Views
502
USNA91
U

Forum statistics

Threads
1,216,085
Messages
6,128,733
Members
449,465
Latest member
TAKLAM

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top