Visual budget management

Mauk Janssen

Hello,

 

I'm working on a total overview of projects in a sheet, using different columns with start and end dates and with budget reserved en budget spend. 

The thing is that I would like to compare the budget reserved with the budget spend. So that when the budget spend is higher then the budget reserved it changes color. I thought that with the use of conditional formatting this was possible, but I can't find a function to refer to another cell. 

Is there a solution for this or could this be an upgrade from the original conditional formatting in a new version of Smartsheet?

 

Mauk Janssen

Pam Alakai

Hello, 

To compare the two budgets and return a color when the Spend Budget is higher, you will want to:

• Create a checkbox column 
• Insert an IF formula in cell 1 of the checkbox column. The formula will be set up like this: =IF([Budget Spend]1 > [Budget Reserved]1, 1) . This will check the checkbox automatically every time the budget spent is higher than the budget reserved. 
• Drag from the lower-right corner of the cell to copy the formula across contiguous cells in the sheet. As you copy, the formula will automatically change its respective cell references. 
• Now you can set up the conditional formatting rule for when the checkbox column is checked and choose the color to apply. 
• You can hide the checkbox column if you don't want it to be visible in the sheet  (Right click on the column name and select Hide Column).

The sheet will look like the screenshot below when the rules above are set up. 

Kind Regards, 

Pam 

Smartsheet Technical Support Specialist