Have cells inherit background color

So, I have a helper sheet that tracks Budgeted, Committed and Actual spend for an individual project based on the end date from a project sheet. The sheet is laid out where a total of 24 months have their own columns in chronological order from left to right. There are four rows for Budgeted, Committed, Actual and the last uses a formula to determine what data to display depending on the contents of the other three. We'll call this row Color.

What I want to happen is for the background color of each cell in the color row to change based on if it used data from the Budgeted, Committed or Actual row. All the solutions I've seen use conditional formatting ,which I can't get to work, or helper columns, which I'd like to avoid because I'd need so many.

Any help is appreciated.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide a screenshot for reference?

    thinkspi.com

  • The top row is the one that I need to change the color of, the third row is Budgeted, fourth is committed and fifth is actual. I want the top row to change color depending on which of the other three rows its value came from.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Do you use the blue row for anything or is it just a separator?

    thinkspi.com

  • It's just a separator.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Joseph Nabors

    I hope you're well and safe!

    Unfortunately, I can't see another way to do it except by using helper columns.

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    To get the conditional formatting working you will definitely need helper columns. One for each (so 24 total).


    To avoid helper columns, you could use a formula to output text on that blue row that denotes which row it came from. Would that be an option? You could then leverage conditional formatting on that row as opposed to the row with the actual numbers.

    thinkspi.com

  • Paul,

    I did try that with a separate row, the problem is that the most important row is the numbers row. I have dozens of these for individual projects and the top row ends up in a report where they are all grouped by type.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    In that case then you are going to need to use helper columns.

    thinkspi.com