Getting a value from the cell above as Column formula

Options

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.

Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer βœ“
    Options

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

Answers

  • Jasper_
    Jasper_ ✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide some screenshots for reference?

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer βœ“
    Options

    @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_
    Jasper_ ✭✭
    Options

    @Leibel Shuchat

    Thanks so much, worked like a charm!

    I still have no idea how though but thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!