IS Blank & IS Text formulas

edited 12/09/19 in Smartsheet Basics


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.




  • L_123
    L_123 ✭✭✭✭✭✭
    edited 09/10/18

    POx37 and RFxx37

  • Mike Wilday
    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
    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. 

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

  • 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?

  • L_123
    L_123 ✭✭✭✭✭✭

    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

  • L_123
    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, 



    =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
    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
    Mike Wilday ✭✭✭✭✭✭

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

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

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