IS Blank & IS Text formulas
Hi,
I am trying to create a formula to give me a specific "status" based on a couple of criteria's.
My formula is this =IF(ISBLANK(RFx37), "Need RFx",IF(ISTEXT(RFxx37), "Need PO", IF(ISTEXT(POx37), "Open PO")))
I want to status cell to update based on other cells.
My columns are Status, RFx, & PO. If the RFx cell is blank return the status "Needs RFx", if there is text in the RFx cell return the status "Needs PO", if there is text in the PO cell return the status "Open PO".
I'm getting an #unparseable error so I've done something wrong.
Thanks.
Comments
-
POx37 and RFxx37
-
Just realized you were indicating the row... You had an x after the PO row which would have been triggering the error - cause POx isn't a column name.
=IF(ISBLANK([RFx]@row), "Need RFx",IF(ISTEXT([RFx]@row), "Need PO", IF(ISTEXT([PO]@row), "Open PO")))
See if that does the trick... But know that if one of the first two if statements fire you will never get to the third. It seems like the there will either be blank or text in the RFx row will which never fire the open PO. Just something to consider.
-
Hi Mike - thanks for the help. You are correct, I fixed the formula but now I am getting a blank.
Is there a way to make the formula fire the Open PO if all the others have been fired? An if/and scenario?
-
Yes, that is very possible. You can use AND to measure two requirements...
IF(AND([Column Name]@row= "x", [Column Name]@row = "Y"), "Than this", IF(...
You could also rearrange your current criterion to test the third option first,
=IF(ISTEXT([PO]@row), "Open PO", IF(ISBLANK([RFx]@row), "Need RFx",IF(ISTEXT([RFx]@row), "Need PO")))
That way it won't be ignored...
-
Hi Mike,
Moving the third option first is still giving me a blank result.
-
Ok, I think I figured out my issue. My PO numbers will include text and/or just numbers. My IFTEXT is accounting for number only POs. How do I change this?
-
try not(isblank(cellref)) instead of istext.
-
Well I've done something. My formula is =IF(ISBLANK(PO11), "Need PO", NOT(ISBLANK(PO11), "Open PO"))
Blank = Need PO fires, but Not Blank = incorrect argument
-
You need a second if statement. The if works like this
if( Criteria, True, False )
right now you are saying
if( PO11 is blank, then post Need PO, else post the conditional not(isblank(PO11)))
It won't recognize the conditional without another if statement,
try
=if(isblank(PO11),"Need PO",if(not(isblank(PO11)),"Open PO")
This is in an of itself a redundant statement however. It could be broken down much easier to
=if(isblank(PO11),"Need PO","Open PO")
because we already test if PO is blank with the first criteria.
-
Original Formula
=IF(ISBLANK(RFx37), "Need RFx",IF(ISTEXT(RFxx37), "Need PO", IF(ISTEXT(POx37), "Open PO")))
I believe based off of your original formula you are looking for something like this
=IF(ISBLANK(RFx1), "Need RFX", "Need PO") + " " + IF(NOT(ISBLANK(PO1)), "Open PO")
-
Did Luke help resolve your issue?
-
He did - it totally worked. Ending formula is =IF(ISBLANK(RFx486), "Need RFx", IF(ISBLANK(PO486), "Need PO", "Open PO"))
-
Glad you found a working solution. NOT to the rescue.
