Getting a value from the cell above as Column formula
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:
 "LINEID" : Auto Number Column
 "ROW#" : Column Formula: =MATCH([LINEID]@row, [LINEID]:[LINEID], 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
Check out the Formula Handbook template!