IF Statement Does Not Copy on Rows Added Via Form

Options

I have an IF statement that calculates an expense amount field based on mileage entered:

IF(MileageField>0,MileageField*.585)

If there isn't a value in the MileageField, then the expense amount field is just whatever dollar amount is entered (all other types of expenses). These rows are populated by a form, which enters the new rows at the top of the sheet. The IF statement does not populate when a new row is added by a form. Is there a workaround here besides having to manually copy the IF statement to all new rows? Thanks for your help!

Best Answer

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Carlyn F

    You can't have a formula field and also enter something manually into that same field. You'll also want to make your formula a column formula to make sure it shows up on new rows. You would then NOT be able to enter anything manually into that field on a form.

    What it sounds like you need is another column for "OtherExpenses" that can be entered manually, and have the Expense Amount field be a formula like this:

    =IF(ISNUMBER(MileageField@row), MileageField@row * 0.585, OtherExpenses@row)

    In English, if there's a number in the MileageField, multiply it by 0.585, otherwise, populate this cell with whatever is in the OtherExpenses field on this row.

    Once you are sure this formula does what you want, right click and choose Convert to Column Formula at the bottom.

    On your form, users can enter numbers into the MileageField and/or into OtherExpenses, and behind the scenes the MileageField will always override the OtherExpenses field when it comes to populating the Expense Amount column.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Carlyn F
    Carlyn F ✭✭✭
    Options

    Oh my gosh this is a great solution! Thank you so much!!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Options

    @Carlyn F Glad it helped you!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Carlyn F
    Carlyn F ✭✭✭
    Options

    @Jeff Reisman

    Uh, so it is still a great solution, but I can't get my formula to work. Can you look at it please?

    =if(ISNUMBER(Mileage@row),Mileage@row*585,Expense Not Mileage)

    Not sure what I'm missing here, I've tried adding spaces and extra parentheses, but it is giving me the dreaded #UNPARESEABLE error. Can you see what I've got wrong?

  • Carlyn F
    Carlyn F ✭✭✭
    Options

    @Jeff Reisman

    Shoot! You are going to be so happy that you tried to help me. I left off the @row specification at the end of my formula, although it still doesn't work when it is there. Here it is:

    =if(ISNUMBER(Mileage@row),Mileage@row*585,Expense Not Mileage@row)

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓
    Options

    When a column name is more than one word, or contains a number or special character, you need to put the column name in square brackets. Also, capitalize function names in formulas, and I put spaces after commas just for clarity's sake:

    =IF(ISNUMBER(Mileage@row), Mileage@row *0.585, [Expense Not Mileage]@row)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Carlyn F
    Carlyn F ✭✭✭
    Options

    I made the changes and it works perfectly! Thank you so much!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!