Continue format

fb_smd

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

Paul Newcome

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

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

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

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

fb_smd

@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

@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

@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

@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

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

fb_smd

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

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

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

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

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