Use Value in Column B if Exists

[Cantcount]

This sheet will have 3 columns.
Column 1 is 12 cells with Last Years monthly values. The cells have a light pink background.
Column 2 is 12 cells with This Years monthly values. The cells have a light green background.
Column 3 starts out as a mirror of Column 1 including the background color (until column 2 is updated).

Obviously you can’t put in this years monthly data until each month completes.

As you enter new (this years) monthly data into column 2, it updates column 3 with that data and the background color of column 2 for that month.

Example, This is now July:
Column 3 will use Jan-June from this year (column 2) with a light green background. July-Dec will still have last years monthly values and light pink background, as new values for those months do not yet exist.

How would I code the cells in column 3 to pull the values and colors as described? I’m guessing an “if exists” type formula, but I really don’t know.

Thanks

[EarnestAl]

Something like the attached? It uses IF to fill the rolling column and conditional formatting for colours.
RollingTotalColoured.ods (17.9 KB)

[Cantcount]

That is EXACTLY what I want. Sometimes I don’t explain things well. You must have understood what I wanted and nailed it. THANK YOU!!!

[LeroyG]

See sample file: Use Value in Column B if Exists.ods (11.9 KB)

[Cantcount]

Solar Spreadsheet.ods (43.0 KB)
I am close, but not quite there.

The first cluster of cells is manually entered Last Years data.

The third cluster of cells mirrors the first cluster of cells until data is manually entered into second cluster of cells. The third cluster shows a running total of This Years months so far, and the rest of the months from Last Year to get a 12 month running total. I think all of the cell formulas are working correctly.

At first, the 3rd cluster of cells will have the same background color as the 1st, but when Cluster 2 cells are updated (added data), the corresponding cell in cluster 3 should also update with the color of cluster 2 too.

I tried to use your example for the Conditional Formatting, but it looks a I did not get that part right.

I uploaded my sheet. Hopefully you will see the issue and be able to fix it.

THANK YOU!

[LeroyG]

@Cantcount

you will see

Menu Format - Conditional - Manage:
Actual:


After some conditions deleted:


I created a new cell style from F5 (This Year, you can see the cell styles with F11) .

Edited both conditions:

Solar Spreadsheet LeroyG.ods (52.5 KB)

LibreOffice Help about Conditional Formatting.

[Cantcount]

Thank you Leroy! My electric company credits me for solar production that I do not use for one year pooled over the last 12 months. As a new month is added, the year ago month drops off. This spreadsheet helps me keep track of that and maybe not be afraid to lower the air conditioner thermostat if my credits show available.

Thanks again, I really appreciate your help!