Copying Conditional Formatting with Relative References
I have a spreadsheet that is a training matrix. In Row 7 I have the latest revision dates of each document listed across Row 6. Down the left of the page, I have the associates’ names and then their last training date for each document is out to the right of their name. I have the cells conditionally formatted to turn red if the revision date is changed in row 7 to a newer date than their last training. (Cell Value < D$7 shades the cell red and it applies to =$D$8:$AF$30).
In the past I would put my conditional formatting in cell D8, click the Format Painter when on the first cell (D8) and then paste it to cells E8:AF8. Then I would select D8:AF8, click the Format Painter and select D9:AF30 to paste it into the rest of my spreadsheet. In the past when I pasted, it would update each column to the next letter (column) across the page. Then each cell would then be referring to E7, F7, G7, H7, etc…. So basically, each cell had to refer to Row 7 in its particular column to see if the cell needed to turn red to indicate needed retraining. Now when I try to “copy & paste” the formatting all of the cells still refer to cell D7, from my original formula instead of updating to the correct column since there is no $ in front of the column letter. I can’t just copy and paste the cell since each person has different training dates. The only thing that has changed since this process worked is they updated our Office 365 earlier this week. Anyone have any ideas? I’ve attached a dummy sample.
I have a spreadsheet that is a training matrix. In Row 7 I have the latest revision dates of each document listed across Row 6. Down the left of the page, I have the associates’ names and then their last training date for each document is out to the right of their name. I have the cells conditionally formatted to turn red if the revision date is changed in row 7 to a newer date than their last training. (Cell Value < D$7 shades the cell red and it applies to =$D$8:$AF$30). In the past I would put my conditional formatting in cell D8, click the Format Painter when on the first cell (D8) and then paste it to cells E8:AF8. Then I would select D8:AF8, click the Format Painter and select D9:AF30 to paste it into the rest of my spreadsheet. In the past when I pasted, it would update each column to the next letter (column) across the page. Then each cell would then be referring to E7, F7, G7, H7, etc…. So basically, each cell had to refer to Row 7 in its particular column to see if the cell needed to turn red to indicate needed retraining. Now when I try to “copy & paste” the formatting all of the cells still refer to cell D7, from my original formula instead of updating to the correct column since there is no $ in front of the column letter. I can’t just copy and paste the cell since each person has different training dates. The only thing that has changed since this process worked is they updated our Office 365 earlier this week. Anyone have any ideas? I’ve attached a dummy sample. Read More
