Have cells inherit background color

Joseph Nabors

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.

Paul Newcome

Are you able to provide a screenshot for reference?

Joseph Nabors

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

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

Joseph Nabors

It's just a separator.

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!

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.

Joseph Nabors

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

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