IS Blank & IS Text formulas

shornbuckle

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.

 

L_123

POx37 and RFxx37

Mike Wilday

You either need to indicate the row number or use @row for these formulas. 

=IF(ISBLANK([RFx37]@row), "Need RFx",IF(ISTEXT([RFxx37]@row), "Need PO", IF(ISTEXT([POx37]@row), "Open PO")))

Try that and let us know if it works. 

Mike Wilday

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. 

shornbuckle

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?

Mike Wilday

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... 

shornbuckle

Hi Mike,

Moving the third option first is still giving me a blank result. 

shornbuckle

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?

L_123

try not(isblank(cellref)) instead of istext.

shornbuckle

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

L_123

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.

L_123

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")

 

 

Mike Wilday

Did Luke help resolve your issue? 

shornbuckle

He did - it totally worked. Ending formula is =IF(ISBLANK(RFx486), "Need RFx", IF(ISBLANK(PO486), "Need PO", "Open PO"))

Mike Wilday

Glad you found a working solution. NOT to the rescue.