2 excel questions in 1 day!

DrPhil

Outstanding Member
Joined
Jul 19, 2006
Messages
29,378
Reaction score
20,034
Points
7,907
Location
Ireland
Practically need a sub forum at this stage...

Apologies. I have googled this but it's not quote catching my drift.

I want to apply conditional formatting to a cell based on the result of another cell.

Example:
Column 1 - name
Column 2 - 1st payment
Column 3 - 2nd payment

2 rows, column 1 they're merged as both are for the same name.

Name---------01/10/20--------01/11/20
Name-------------£500---------------£250

The easy part is getting the dates on the first row to format, red for overdue, orange for due soon, green once the payment is complete and I remove the date and write "complete"

But I want that colour to apply to the amount too. In other words once the payment on Oct 1st is overdue both the date cell and the £500 go red. And when it's done and I change 01/10/20 to "complete" the figure stays but both cells go green.

Advice appreciated as always!
 
Can you just put the same formula you are using in the conditional formatting on the dates, in the cells with the £500 and £250 but reference the cell with the date?
 
I'll check in work but I don't think that works. It seems that it will format all of row B based on B1 for example, but I want to format C2 based on C1.

I'll check when I get to a PC.

Did you put in the right formula? It should fill the formula down so each row is based on its own value. If you watch the video it's quite good.
 
Like this - making rows where age > 30 yellow

1599761727938.png
 
Sorry if I'm being thick, but that still doesn't do what I want. It will colour an entire column or row etc. But I don't want that.

To explain:
20200914_161830.jpg


Apologies for the quick setup and picture rather than screen shot.

That selection is set so that if a date has expired, it goes red. If the date is within the month, it goes orange, within 30 days pale orange. If the cell says "complete" it goes green.

I want to format so that when a cell goes green, red, orange etc the single cell below will do the same. Not the full row or the full column, just B3 to match B2 or F9 to match F8 etc.
 
Sorry if I'm being thick, but that still doesn't do what I want. It will colour an entire column or row etc. But I don't want that.

To explain:
View attachment 1365546

Apologies for the quick setup and picture rather than screen shot.

That selection is set so that if a date has expired, it goes red. If the date is within the month, it goes orange, within 30 days pale orange. If the cell says "complete" it goes green.

I want to format so that when a cell goes green, red, orange etc the single cell below will do the same. Not the full row or the full column, just B3 to match B2 or F9 to match F8 etc.

The example was for colouring rows - if you want a cell in column n row m to impact column n row m+1 then it should be a case of updating the formula to reflect that.
 

The latest video from AVForums

TV Buying Guide - Which TV Is Best For You?
Subscribe to our YouTube channel
Back
Top Bottom