Conditional format sheet data
 

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.

Select the range you want to format,do formatconditional 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($A E$2),$AD$2="Y"))


Regards,

Peo Sjoblom

"DTE" wrote:

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.

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

-----Original Message-----
Select the range you want to format,do formatconditional
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" wrote:

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.


.

Try this amendment

=OR(AND(ISNUMBER($AC$2),$AD$2="N"),AND(ISNUMBER($A E$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" wrote:

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

-----Original Message-----
Select the range you want to format,do formatconditional
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" wrote:

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.


.

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



"DTE" wrote:

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

-----Original Message-----
Select the range you want to format,do
formatconditional
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" wrote:

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.


.


.