IF Statement Does Not Copy on Rows Added Via Form
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
-
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!
Answers
-
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!
-
Oh my gosh this is a great solution! Thank you so much!!
-
@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!
-
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?
-
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)
-
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!
-
I made the changes and it works perfectly! Thank you so much!!!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 378 Global Discussions
- 208 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!