Subtraction

Gabriella Silvagnoli

Hi I have a total budget at the top of my column that I am tryin to periodically subtract from, every time I incur an expense.

Have been doing -= and entering each row click by click

Is there an easier formula to do this and that automatically subtracts every time I add in a new row?

Paul Newcome

Are you able to provide a screenshot of how your data is laid out?

Gabriella Silvagnoli

Yes, please see attached. I am trying to automate this so that every time I add an expense to this column, it will subtract it from the 500k in bold at the top and give me a total at the bottom

Paul Newcome

Are you rows indented underneath of that row? If so, you should be able to use something along the lines of...

=500000 - CHILDREN()

If they are not indented, you should be able to use a single circular reference within a column as long as you reference the entire column.

=500000 - SUM([Corporate Cost]:[Corporate Cost])

It should ignore the cell that the formula is in and pull all other values into the SUM function.

Gabriella Silvagnoli

It is indented, so I willput =500000 - CHILDREN()

But what goes in the parenthesis? every cost in the column?

is there a way to automate this, I am constantly adding in additional fees to the bottom of the row

Thanks!

Paul Newcome

Nothing goes in the parenthesis in this case. Leaving the parenthesis empty in the CHILDREN function indicates to use the CHILDREN of the cell the formula is in. AS long as the new rows being added are also indented underneath of this row, they will automatically be included.

Gabriella Silvagnoli

Hmm not sure what I am doing wrong I get the error message UNPARSEABLE.

Please note that all the columns in this row are indented under the same indent header, but this is not the main indent column

Gabriella Silvagnoli

This is what my full sheet looks like

Paul Newcome

Can you provide a screenshot of the formula within the sheet similar to the screenshot below?

Gabriella Silvagnoli

How do I find this?

Gabriella Silvagnoli

any word on this?

Genevieve P.

https://community.smartsheet.com/discussion/comment/281025#Comment_281025

Hi @Gabriella Silvagnoli

You can find the window with the formula spelled out by double-clicking on the cell that has the formula... the one that's currently returning an error.

You could also try wrapping the SUM function around the CHILDREN function:

=500000 - SUM(CHILDREN())

Paul Newcome

@Genevieve P Thank you. Apparently I was trying to do too many things at once.

@Gabriella Silvagnoli Genevieve is correct. There should be the SUM function wrapped around the CHILDREN function.

Genevieve P.

Haha I was wondering if there was a new way to reference Children rows that I was missing... thanks for verifying! 🙂

@Gabriella Silvagnoli Let us know if this works for you!

Gabriella Silvagnoli

hey, I appreciate both of your help, but this is still not working for me.

I keep getting the UNPARSEABLE error message

Genevieve P.

Hi @Gabriella Silvagnoli

Are you able to provide a screen capture of the sheet, and the formula open like Paul has above?