Purchase calculation from the grant amount

Joseph Aloysias

I'm maintaining a separate sheet to maintain the purchaser grant and I used index match formula to auto populate based on the purchaser grant ID, Now I want to minus total price from the grant and show the remaining grant

when I do the second purchase with the same purchaser grant ID I should automatically get the remaining grant (89144.60) in 7th row purchaser grant column, in the same way for other purchases

Please help me with the workaround and formula's

David Tutwiler

I think you could do this with a SUMIF statement. That way you could sum all of your Total Prices together before you do the remaining Grant.

There are two ways to approach this. If you evaluate the entire column every time you make the calculation, you could make this a Column Formula. The problem with that method is that your Remaining Grant amount will be the same for every instance of your Purchaser Grant ID. If you would prefer to show the waterfall of how each total price affected the Remaining Grant, then you will need to format your formula differently and it will not be formatted in a way that can be converted to a formula column. As long as you have a few rows of data and pull the formula down to all of your current entries, it should replicate on any new rows. I will show an example of both formulas below:

Column Formula (Same Remaining Grant Value for all instances of Purchaser Grant ID)

=[Purchaser Grant]@row - SUMIF([Purchaser Grant ID]:[Purchaser Grant ID], [Purchaser Grant ID]@row, [Total Price]:[Total Price])

Translation - "Sum all of the Total Price values of all the rows where the Purchaser Grant ID matches this row's Purchaser Grant ID. Then subtract the Purchaser Grant from that Sum and return it as the value in the cell (Remaining Grant)."

Waterfall Remaining Grant (Only Evaluate rows above the current row)

=[Purchaser Grant]@row - SUMIF([Purchaser Grant ID]$1:[Purchaser Grant ID]@row, [Purchaser Grant ID]@row, [Total Price]$1:[Total Price]@row)

Translation - "Sum all of the Total Price values from ONLY the first row through my current row where the Purchaser Grant ID matches this row's Purchaser Grant ID. Then subtract the Purchaser Grant from that Sum and return it as the value in the cell (Remaining Grant)."

Joseph Aloysias

Waterfall Remaining Grant formula works perfect(exactly this is what I'm looking for) but the problem is these requests are filled by a form, we can't drag the formula manually every time for new requests. is there any other work around?

David Tutwiler

If you drag it down to at least 3-4 rows, then it should automatically add it to any new form entry when a new row is created. Smartsheet grids are generally smart enough to know when you're copying a formula over and over and will try to match it for new entries. This was actually the only option before column formulas came about.

Copy the formula to your top existing row and then drag it down to all existing rows. Then run a test with your form, it should add it to the new entry.

Joseph Aloysias

But its adding "0" in the cells, also adding new rows and creating Request ID (Auto number column)

David Tutwiler

I understand the confusion. You will only want to drag your formula down to the last row with information in it, not down through all of the blank rows. That way, when the form adds the new entry at the bottom of your list, it will know where to grab the formula from (the last row in your sheet with information in it).