Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Visual budget management

Options
Mauk Janssen
edited 12/09/19 in Archived 2017 Posts

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

Comments

  • Pam Alakai
    Pam Alakai Employee
    edited 11/16/17
    Options

    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 

    Screen Shot 2017-11-16 at 9.18.47 AM.png

  • Mauk Janssen
    Options

    Hello Pam, 

     

    Thank you for your quick response and solution!

     

    With Kind Regards, 

    Mauk 

  • Pam Alakai
    Pam Alakai Employee
    Options

    You are very welcome Mauk! Happy to help.  

    Best,

    Pam 

    Technical Support Specialist 

This discussion has been closed.