Continue format

Options
fb_smd
fb_smd ✭✭
edited 10/03/23 in Formulas and Functions

how can I apply this formula to all the column? so far it's not working and says that the syntax isn't quite right. =IF(Balance@row > Balance15, "True", "False")

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Actually... Now that I think about it, that shouldn't be working the way you want it to. It is basically going to always compare "@row" to the second to last row and not necessarily the row above.


    You are going to need to insert an auto-number column (formatting doesn't matter) and then a text/number column (called "Row" in this example) and use this column formula:

    =MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)


    Then use this instead:

    =IF(Balance@row > IFERROR(INDEX(Balance:Balance, Row@row - 1), Balance@row), "True", "False")

Answers

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    Hi @fb_smd -

    From the help files: Column formulas cannot refer to cells or ranges with specific row numbers such as with absolute references, specific cell references, or partial-column ranges. Use @row, column references, and cross sheet references instead.

    To work around this you could put your Balance15 total into a Sheet Summary field and reference that and then it will work as a column formula:

    =IF(Balance@row > [Balance Summary]#, "True", "False")

    Where [Balance Summary]# is the sheet summary field.

    Hope this helps!

  • fb_smd
    fb_smd ✭✭
    Options

    Thank you @ker9, it did help. I created a new column and now when I add a value manually on the sheet it automatically adds the values I wanted. My new issue is that when I want to add values through a form, it doesn't add the values I want.

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    @fb_smd - might need more specifics, are you trying to update the Summary field through a Form - because that won't work.

  • fb_smd
    fb_smd ✭✭
    Options

    @ker9 so the main goal I have is when someone fills the form, the formulas I have can be applied to the new fields added. When I do it manually, it updates, but when someone fills the form it doesn't fill it.

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    @fb_smd - the formulas may need to be converted to column formulas. To do that, right click on a single formula in a column, and at the bottom of the menu select Convert to Column Formula.

    The formulas should then be there when data comes in through forms.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @fb_smd What is the reason for specifically referring to Balance15? Are you trying to reference the row above, or is that supposed to be set as a reference to row 15 on every other row?


    @ker9 You can't update a sheet summary field directly from a form, but you can use form submissions to add new rows to a sheet and then use a formula in the sheet summary field to pull in the most recent form entry.

  • fb_smd
    fb_smd ✭✭
    Options

    @Paul Newcome it's because it checks if there were any changes on the previous balance, if there were it will show as True. And if a new value is added manually, then it will appear automatically like it appears on the screenshot. But, if the value is added on the form it won't appear automatically.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    So in your example formula, is row 15 the row above then?

  • fb_smd
    fb_smd ✭✭
    Options

    Yes, so the 15 will be the above and in row 16 it should be the update. In 17, the update doesn't work because it comes from the form

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Ok. Try this to pull in the value from the row above with a valid syntax for a column formula...

    =IF(Balance@row > IFERROR(INDEX(Balance:Balance, COUNTIFS(Balance:Balance, @cell <> "") - 1), Balance@row), "True", "False")

  • fb_smd
    fb_smd ✭✭
    Options

    That worked! I'll do research on how to use each function for a future project that is similar. Thank you @Paul Newcome

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Actually... Now that I think about it, that shouldn't be working the way you want it to. It is basically going to always compare "@row" to the second to last row and not necessarily the row above.


    You are going to need to insert an auto-number column (formatting doesn't matter) and then a text/number column (called "Row" in this example) and use this column formula:

    =MATCH([Auto-Number]@row, [Auto-Number]:[Auto-Number], 0)


    Then use this instead:

    =IF(Balance@row > IFERROR(INDEX(Balance:Balance, Row@row - 1), Balance@row), "True", "False")

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    @Paul Newcome - thank you for jumping in and resolving! For some reason I was under the impression that we were summing a column and that's why I suggested Sheet Summary. My bad on that assumption.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!