Flag when a character is entered instead of a number =IF(NOT(ISNUMBER statement...

09/11/20
Answered - Pending Review

Hello,

I cannot figure out how to add in 2 other cost fields into this statement. My purpose is to have 1 check box (in a single column) enabled with a check mark, whenever one puts text into a field where we need only numbers. The field names are : Travel Cost, FTG Cost and HS Cost. Once I get this part done, I will then setup a notification back to the user to resubmit a new form using numbers.

I was able to get only 1 column to work, but I need to add 2 more. What is the formula, please and Thank you!

=IF(NOT(ISNUMBER([Travel Cost]@row)), 1)

Answers

  • Hi @GetITDone

    You need to add an OR function.

    =IF(OR(NOT(ISNUMBER(Travel Cost]@row)), NOT(ISNUMBER([FTG Cost]@Row)), NOT(ISNUMBER([HS Cost]@Row))), 1, 0)

    This will check the box if any cell is not a number.

    Hope it helped!

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @GetITDone

    To add to David's excellent answer.

    Here's another option.

    Try something like this.

    =IF(COUNTIF([Travel Cost]@row:[HS Cost]@row, ISTEXT(@cell)) > 0, 1)
    

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Nice one @Andrée Starå . I often forget that range can be done within a row.

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @David Joyeuse

    Thanks!

    Easy to miss!

    I have some options I always forget as well. 😉

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

  • Thanks everyone.

    For some reason I couldn't get this one to work for 4 'costs' columns within a short period of time. What would it look like if I have Travel cost, HS Cost, FT Cost and GT cost?

    =IF(COUNTIF([Travel Cost]@row:[HS Cost]@row, ISTEXT(@cell)) > 0, 1)
    

    So I used below, My only concern making sure I do not move any columns around as it will pick up unnecessary costs.

    =IF(COUNTIF([Travel Cost]@row:[HS Cost]@row, ISTEXT(@cell)) > 0, 1)
    

    What would it look like if I have Travel cost, HS Cost, FT Cost and GT, how to make it specific columns? Thanks

Sign In or Register to comment.