Creating custom formula to auto-display negative number (cost credit)

Sam Henley

My company is moving to Smartsheet to consolidate what we normally keep in multiple locations. One of these locations is Excel, where we track project spending and budgets.

I've figured out how to use cross sheet referencing to calculate costs and credits and have them show on the master budget tracker. It was a bit of a chore (compared to just clicking a cell/range/etc on another tab/sheet within the same spreadsheet), but I worked it out.

For the formula we use to cross sheet reference, we us IF & SUMIF to designate construction codes and apply debits and credits to each code in a master budget tracking sheet. In excel, we created a custom cell formula (#,##0.00) that automatically displays a negative number for the credits column.

The rest of the formula follows basic IF/SUMIF logic and returns the desired results every time. Sweet.

I do not see that there is a function to have values in a column automatically display as negative when a value is input as shown below in Excel. Here, 200 or 360 is typed and automatically displays as a deduction.

I reformatted my normal Excel formula to compensate for the change to SS syntax and have made everything else work exactly as it does in Excel, the only exception being that the credits instead show a positive number that is then deducted through the formula. Sweet enough. However, the higher ups want to be able to look at a value in the credits column and see that it's reflected by either ( ) or a negative. I'll change the formula accordingly if I can get these values to display as described.

I would LOVE for this to be something super simple that I've just overlooked. I was asked to take over SS integration within our company, but not before all of the training time was used by the people who passed this off to me. Still, the community has been most helpful and I'm hoping this is a "DUH" moment for me.

Thanks in advance for any help!

MedaUser

Hi Sam,

If your cross-sheet formula is working correctly by pulling the numbers you need based on the cost code, have you tried placing a "-" in front of your cross-sheet formula, so it will provide the negative value? If so, and your formula is referencing cell in the current sheet within the same row, then you can convert the formula to a column formula [Right-click a cell with the formula > select Convert to Column Formula]. This will lock the column down, but all of the cells will automatically convert to a negative value (assuming all outputs are pulling positive values).

Please let me know if I'm totally off on the setup. Happy to keep working through this with you.

Travis C, PMP

Smartsheet Leader with 5+ years of SS experience

If you were happy with my answer, please upvote and mark my response as answered. 

Paul Newcome

The only way to get it to change from a positive number entry to being stored as a negative would be through the API, the premium add-on Bridge, or a similar third party app.

What you can do though, is leave the data entry column as it is but then use a "display" column that has a formula to output the desired format.

="(" + Credits@row + ")"

MedaUser

Hi Sam,

If your cross-sheet formula is working correctly by pulling the numbers you need based on the cost code, have you tried placing a "-" in front of your cross-sheet formula, so it will provide the negative value? If so, and your formula is referencing cell in the current sheet within the same row, then you can convert the formula to a column formula [Right-click a cell with the formula > select Convert to Column Formula]. This will lock the column down, but all of the cells will automatically convert to a negative value (assuming all outputs are pulling positive values).

Please let me know if I'm totally off on the setup. Happy to keep working through this with you.

Travis C, PMP

Smartsheet Leader with 5+ years of SS experience

If you were happy with my answer, please upvote and mark my response as answered. 

Paul Newcome

The only way to get it to change from a positive number entry to being stored as a negative would be through the API, the premium add-on Bridge, or a similar third party app.

What you can do though, is leave the data entry column as it is but then use a "display" column that has a formula to output the desired format.

="(" + Credits@row + ")"

Sam Henley

Thanks for the help, gentlemen! I tried both suggestions and was successful with both methods. I'm plugging along now, and my spreadsheets are both reconciled and look pretty for the bosses! Haha! It seems so simple in retrospect, but such is the way of things when you're thrust in blind, I suppose.

Thanks again to you both!

Sam Henley

Thanks for your help, gentlemen! I tried both of your suggestions and was successful with both methods. I'm now plugging away at my conversions and the end is in sight! Now, everything is reconciled AND looks pretty for the bosses! Haha! It seems simple enough in retrospect, but such is the way of things when you're thrust in blind, I suppose.

Thanks again to you both!