Getting a value from the cell above as Column formula

Jasper_

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:

1. "LINE-ID" : Auto Number Column
2. "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.

Leibel S

@Jasper_

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

Jasper_

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

Paul Newcome

Are you able to provide some screenshots for reference?

Leibel S

@Jasper_

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

Jasper_

@Leibel Shuchat

Thanks so much, worked like a charm!

I still have no idea how though but thank you.