Conditional format sheet data

[DTE]

Hi,
I'd appreciate some advise on a formula to conditionally
format all records on my sheet (i.e. data in rows)when
this senario occurs:
1) When there is a date in Column AC for that row, and
2) When there is a N (for no)in AD for that row.
Also, there must be a date in AE for that row if Y (for
yes)is in AD.
I would like to apply the formula to the entire sheet to
get the effect of a 'dim out'when record is considered
complete.
Expert advise is appreciated as there are way too many
records to manually check for compliance and be certain of
future mass data input.

 

[Guest]

Select the range you want to format,do format>conditional formatting, formula
is

=AND(ISNUMBER($AC$2),$AD$2="N")

if you need the Y as well

=OR(AND(ISNUMBER($AC$2),$AD$2="N"),AND(ISNUMBER($AE$2),$AD$2="Y"))


Regards,

Peo Sjoblom

 

[DTE]

Thanks Peo but it did not take effect. Does this make any
better sense?
If there is a date in AC and a N in AD then format applies.
If there is a date in AC and a Y in AD then format applies
only when there is a mandatory date in AE (if blank no
format).

So basically;
AC = blank or date (no format)
AC = date (no format)
AC = date plus N in AD (format applies)
AC = date plus Y in AD format only if AE has a date.

This occurs over all rows independatly.

I'm still having trouble with this one!

Thanks

 

[Guest]

Try this amendment

=OR(AND(ISNUMBER($AC$2),$AD$2="N"),AND(ISNUMBER($AE$2),ISNUMBER($AC$2),$AD$2="Y"))

date (or number since excel dates are numbers, if you use text dates post
back) in AC2 AND N in AD2 format OR date in AC2 AND date in AE2 AND Y in AD2

to apply this over the whole sheet, click the select all button and use

=OR(AND(ISNUMBER($AC1),$AD1="N"),AND(ISNUMBER($AE1),ISNUMBER($AC1),$AD1="Y"))


regards,

Peo Sjoblom

 

[DTE]

That worked! Very, very helpful...Thank you!

-----Original Message-----
Try this amendment

=OR(AND(ISNUMBER($AC$2),$AD$2="N"),AND(ISNUMBER ($AE$2),ISNUMBER($AC$2),$AD$2="Y"))

date (or number since excel dates are numbers, if you use text dates post
back) in AC2 AND N in AD2 format OR date in AC2 AND date in AE2 AND Y in AD2

to apply this over the whole sheet, click the select all button and use

=OR(AND(ISNUMBER($AC1),$AD1="N"),AND(ISNUMBER ($AE1),ISNUMBER($AC1),$AD1="Y"))


regards,

Peo Sjoblom




.