Conditional formatting help reqd!

[Jester]

Right, hope someone can guide me here....?

I an using CF to highlight a cell if the date in it is more than a year old:

I'm using: Conditional formatting => New rule => 'use a formula to determine which cells to format'

The formatting rule being "=A1<=TODAY()-365"
Then select the cell format etc


What I want to do is to add another condition so that the cell is only formatted if a certain value is found in an adjacent cell, eg if A4 contains 'PAID' then A1 should be formatted if over a year old. If A4 is clear, then A1 should not be formatted.


Hope that makes sense!

I'm happy to approach this in a completely different way if there is one but I don't really have room on the sheet to enter formulae, hence using CF.


Thanks a bunch!

[Surprised]

You should be able to use AND as the formula

[Surprised]

Try this:
Say the date is in col A and paid is in Col B and starts in row 3

In C3 put this formula:
=IF(AND(B3="Paid",A3<=TODAY()-365),1,0)

The CF for col A is
=C3

[Jester]

Thank you - will try it tomorrow

[Surprised]

In fact you can put that formula in the CF formula box and it works to so no need for any extra coloumn

[Jester]

In fact you can put that formula in the CF formula box and it works to so no need for any extra coloumn

Now that's what I wanted!

[unc.si.]

You won't need the If statement if you put it into the conditional formatting wizard. Excel does that bit for you.

[Surprised]

You won't need the If statement if you put it into the conditional formatting wizard. Excel does that bit for you.

I don't have the wizard

[unc.si.]

Oh OK. Wouldn't know how to do it without using the conditional formatting button to add a rule.

[Surprised]

Oh OK. Wouldn't know how to do it without using the conditional formatting button to add a rule.

I had a look around

Conditional Sum Wizard add-in

This add-in is no longer included with Excel 2010. In earlier versions of Excel, you could use the Conditional Sum Wizard to help you write formulas that calculate the sums of values that met specified conditions. In Excel 2010, you can write conditional formulas by using functions that are designed to help with that task, such as the SUMIF and SUMIFS functions.

Formulas that were generated by this wizard will continue to work in Excel 2010. You can edit them by using other methods.

[unc.si.]

Looks like I'm just living in the past then

[Surprised]

Yep.
2010 is the future

[unc.si.]

I'm on Excel 2013.

Your quote relates to conditional sum not conditional formatting.

[Surprised]

So I see .
But I still don't have Conditional Format wizard and admit I never heard of conditional sum wizard. I just type formulas.

How do you access the wizard?

[unc.si.]

'Wizard' might be a misnomer (I tend to call anything that doesn't just involve typing in a formula yourself a wizard).

I just mean the conditional formatting button on the home ribbon, ie conditional formatting / new rule, then 'use a formula'. The 'use a formula' then brings up a box to type in, but it effectively does the 'IF' bit for you. You just enter the formula that you want evaluating (without the need for the IF statement) and then hit the format button to set the formatting if that statement is true.

Don't know any other way of doing it without writing some vba code (which would just be duplicating the code that excel uses for the conditional formatting button).

Only mentioned it because if jester used the conditional formatting button and included the IF statement he'd (probably) get an error.

[unc.si.]

Oh - I've never heard of a conditional sum wizard either. Can't imagine anyone would need a 'wizard' to help with a sumif or sumifs statement. Probably why they scrapped it

[WilBert]

'Wizard' might be a misnomer (I tend to call anything that doesn't just involve typing in a formula yourself a wizard).

I just mean the conditional formatting button on the home ribbon, ie conditional formatting / new rule, then 'use a formula'. The 'use a formula' then brings up a box to type in, but it effectively does the 'IF' bit for you. You just enter the formula that you want evaluating (without the need for the IF statement) and then hit the format button to set the formatting if that statement is true.

Don't know any other way of doing it without writing some vba code (which would just be duplicating the code that excel uses for the conditional formatting button).

Only mentioned it because if jester used the conditional formatting button and included the IF statement he'd (probably) get an error.

That's the way I do it too, mess around with if formulas for 5 hours to get it to work stick it in the conditional formula box then sit back with some satisfaction looking at the work of art before realising I'm going to have to work late to finish my proper job.

[Surprised]

Oh OK.
Then I have it. Yeah...you don't need the IF part if it's just one condition as IF is the rule but if checking multiple conditions then you need to put in the full formula (IF+AND in this case). Multiple IFs or IFORs can just be done with a bunch of rules

[Surprised]

Oh - I've never heard of a conditional sum wizard either. Can't imagine anyone would need a 'wizard' to help with a sumif or sumifs statement. Probably why they scrapped it

I have a feeling it was used with named ranges and may have been of limited use to some especially if all they could do was add up a list of numbers. Probably more trouble than it was worth.

[WilBert]

I'd also have no physical and mental control of myself - I'd have to make the cell orange 2 weeks before it becomes a year old.

[unc.si.]

Oh OK.
Then I have it. Yeah...you don't need the IF part if it's just one condition as IF is the rule but if checking multiple conditions then you need to put in the full formula (IF+AND in this case). Multiple IFs or IFORs can just be done with a bunch of rules

You just need the AND(condition 1, condition 2, etc)

[Surprised]

I'd also have no physical and mental control of myself - I'd have to make the cell orange 2 weeks before it becomes a year old.

Or keep refreshing the spreadsheet to see the exact moment the cell changes colour.

[Surprised]

Oh OK.
Then I have it. Yeah...you don't need the IF part if it's just one condition as IF is the rule but if checking multiple conditions then you need to put in the full formula (IF+AND in this case). Multiple IFs or IFORs can just be done with a bunch of rules

You just need the AND(condition 1, condition 2, etc)

OK. I didn't bother checking that. Still works with the IF but just checked without it and it didn't format

[Jester]

The thread's gone viral!


And I haven't even had a chance to try it yet....... Tonight!

[Jester]

In fact you can put that formula in the CF formula box and it works to so no need for any extra coloumn

This works well thank you


Just one small thing.........
If the date cell is blank the CF thinks it is more than a year ago and formats the cell. I'm not sure if this is a good thing or not, but can I stop it happening if I want to?

[Surprised]

In fact you can put that formula in the CF formula box and it works to so no need for any extra coloumn

This works well thank you


Just one small thing.........
If the date cell is blank the CF thinks it is more than a year ago and formats the cell. I'm not sure if this is a good thing or not, but can I stop it happening if I want to?

Just put a date in that's less than a year?

[Jester]

Hahaha - didn't think of that!!

Rather I meant how to stop a blank cell being highlighted?



I actually think it's good the way it is - highlights if a date has been missed. Just in case it gets confusing to others that I asked.

No worries if it can't be done.

[Surprised]

I'm sure it can be done.
May need to add a second rule or make adjustments to the formula.
I'll look tomorrow

[Rob]

Using Surprised's example formula, it would become:

=IF(AND(B3="Paid",NOT(ISBLANK(A3)),A3<=TODAY()-365),1,0)

[Jester]

Thank you!