Getting a value from the cell above as Column formula
![Jasper_](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I am trying to use the value of the cell in the row above the row that is getting added to the sheet in a formula that adds and subtracts from it. The value needs to be calculated automatically upon the row coming onto the sheet.
Basically I am trying something like this "=[ThisColumn]@rowAbove - [OtherColumn]@row + [OtherColumn2]@row". All the columns contain numbers only.
I have seen posts like this:
"A process I like to use to solve for these kind of issues is to create 2 columns:
- "LINE-ID" : Auto Number Column
- "ROW#" : Column Formula: =MATCH([LINE-ID]@row, [LINE-ID]:[LINE-ID], 0)
This will allow you to run a check on a previous row value by using Index([ThisColumn]:[ThisColumn], [ROW#]@row -1)"
But when I add this formula to the next row or indeed as a column formula I get a error about it looping.
All I need is something that will pull the value from the cell above it so i can use it to calculate the new value based on what was entered in the new row.
Best Answer
-
Sounds like you are trying to make a balance column on a ledger . The way you would do that is using a formula similar to the below:
=IF(OR([π’ LITERS RECIVED]@row <> "", [π’ LITERS ISSUED]@row <> ""), SUMIFS([π’ LITERS RECIVED]:[π’ LITERS RECIVED], [ROW#]:[ROW#], <=[ROW#]@row) - SUMIFS([π’ LITERS ISSUED]:[π’ LITERS ISSUED], [ROW#]:[ROW#], <=[ROW#]@row), "")
Let me know what you think.
Interesting to see you can use emojis in column names and formulas :)
Answers
-
I see that "=INDEX([π’ CURRENT FUEL BALANCE]:[π’ CURRENT FUEL BALANCE], COUNT([π’ CURRENT FUEL BALANCE]:[π’ CURRENT FUEL BALANCE]) - 1) - [π’ LITERS ISSUED]@row + [π’ LITERS RECIVED]@row" also let me get the value of the cell above but it still gives a looping error when I try use it one more than one row
-
Are you able to provide some screenshots for reference?
-
Sounds like you are trying to make a balance column on a ledger . The way you would do that is using a formula similar to the below:
=IF(OR([π’ LITERS RECIVED]@row <> "", [π’ LITERS ISSUED]@row <> ""), SUMIFS([π’ LITERS RECIVED]:[π’ LITERS RECIVED], [ROW#]:[ROW#], <=[ROW#]@row) - SUMIFS([π’ LITERS ISSUED]:[π’ LITERS ISSUED], [ROW#]:[ROW#], <=[ROW#]@row), "")
Let me know what you think.
Interesting to see you can use emojis in column names and formulas :)
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 61.7K Get Help
- 330 Global Discussions
- 191 Industry Talk
- 422 Announcements
- 4.3K Ideas & Feature Requests
- 129 Brandfolder
- 127 Just for fun
- 126 Community Job Board
- 444 Show & Tell
- 27 Member Spotlight
- 1 SmartStories
- 279 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!