Continue format
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
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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!
-
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.
-
@fb_smd - might need more specifics, are you trying to update the Summary field through a Form - because that won't work.
-
@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.
-
@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.
-
@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.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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.
-
So in your example formula, is row 15 the row above then?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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
-
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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
That worked! I'll do research on how to use each function for a future project that is similar. Thank you @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")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
@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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!