• New Patreon Tier and Early Access Content available. If you would like to support AVForums, we now have a new Patreon Tier which gives you access to selected news, reviews and articles before they are available to the public. Read more.

2 excel questions in 1 day!

DrPhil

Distinguished Member
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!
 

imightbewrong

Distinguished Member

DrPhil

Distinguished Member

Lobsterboy1

Established Member
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?
 

imightbewrong

Distinguished Member
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.
 

imightbewrong

Distinguished Member
Like this - making rows where age > 30 yellow

1599761727938.png
 

DrPhil

Distinguished Member
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.
 

imightbewrong

Distinguished Member
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

Is 8K TV dead? Philips OLED+907, Pioneer LX505 AVR plus B&W 700 S3 Reviews & Visit + AV/HiFi News
Subscribe to our YouTube channel
Support AVForums with Patreon

Top Bottom