Hi
I have a workbook that creates a monthly task calendar in one of the
spreadsheets. in that sheet, there is a list of tasks in Column A, while
Column B contains the formula :
=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36, 14,0)),"",VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14 ,0))&""

where A3 is a date. The cells in Column B therefore appear either blank or
return a value.

I want to apply conditional formatting only to those cells in Column B that
return a value. The colour formatting will alert a staff member that they
have a task to complete on a certain day if it is shaded with 'their' colour.
I've fuddled around with variations including IF, ISBLANK and NOT(ISBLANK)
but to no avail. can anyone assist?

Thanks.

Why do you have this at the end of your formula: &""

Conditional Formatting
Formula Is: =B1<""

Biff

"Vibeke" wrote in message
...
Hi
I have a workbook that creates a monthly task calendar in one of the
spreadsheets. in that sheet, there is a list of tasks in Column A, while
Column B contains the formula :
=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36, 14,0)),"",VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14 ,0))&""

where A3 is a date. The cells in Column B therefore appear either blank
or
return a value.

I want to apply conditional formatting only to those cells in Column B
that
return a value. The colour formatting will alert a staff member that they
have a task to complete on a certain day if it is shaded with 'their'
colour.
I've fuddled around with variations including IF, ISBLANK and
NOT(ISBLANK)
but to no avail. can anyone assist?

Thanks.

You asked..."Why do you have this at the end of your formula: &""

Because the data populated by the function is a combination of two bits of
data from the other speadsheet (the name of the event and the date it occurs).

Thanks you for your replies, I will now give them a try.



"T. Valko" wrote:

Why do you have this at the end of your formula: &""

Conditional Formatting
Formula Is: =B1<""

Biff

"Vibeke" wrote in message
...
Hi
I have a workbook that creates a monthly task calendar in one of the
spreadsheets. in that sheet, there is a list of tasks in Column A, while
Column B contains the formula :
=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36, 14,0)),"",VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14 ,0))&""

where A3 is a date. The cells in Column B therefore appear either blank
or
return a value.

I want to apply conditional formatting only to those cells in Column B
that
return a value. The colour formatting will alert a staff member that they
have a task to complete on a certain day if it is shaded with 'their'
colour.
I've fuddled around with variations including IF, ISBLANK and
NOT(ISBLANK)
but to no avail. can anyone assist?

Thanks.

Try this ..

Assume your range is A1:D10

Select the range (with A1 active),
then apply CF using settings:

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

Condition 2, Formula is:
=AND(A1<"",NOT(ISERROR(A1)))
Format: Blue fill

OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote:
Hi
I have a workbook that creates a monthly task calendar in one of the
spreadsheets. in that sheet, there is a list of tasks in Column A, while
Column B contains the formula :
=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36, 14,0)),"",VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14 ,0))&""

where A3 is a date. The cells in Column B therefore appear either blank or
return a value.

I want to apply conditional formatting only to those cells in Column B that
return a value. The colour formatting will alert a staff member that they
have a task to complete on a certain day if it is shaded with 'their' colour.
I've fuddled around with variations including IF, ISBLANK and NOT(ISBLANK)
but to no avail. can anyone assist?

Thanks.

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

They have any errors trapped in the formula:

=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36 ,14,0)),"",........

Biff

"Max" wrote in message
...
Try this ..

Assume your range is A1:D10

Select the range (with A1 active),
then apply CF using settings:

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

Condition 2, Formula is:
=AND(A1<"",NOT(ISERROR(A1)))
Format: Blue fill

OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote:
Hi
I have a workbook that creates a monthly task calendar in one of the
spreadsheets. in that sheet, there is a list of tasks in Column A, while
Column B contains the formula :
=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36, 14,0)),"",VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14 ,0))&""

where A3 is a date. The cells in Column B therefore appear either blank
or
return a value.

I want to apply conditional formatting only to those cells in Column B
that
return a value. The colour formatting will alert a staff member that they
have a task to complete on a certain day if it is shaded with 'their'
colour.
I've fuddled around with variations including IF, ISBLANK and
NOT(ISBLANK)
but to no avail. can anyone assist?

Thanks.

"T. Valko" wrote:
They have any errors trapped in the formula ..

True that's what the OP posted, but we can treat the suggestion as generic
then <g, just in case the formulas range does have any untrapped error
returns.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Thank you very much for your assistance. Worked a charm. If I could trouble
you with an extended question on a similar problem, I would be very grateful
for any assistance.

Column B's formula returns an event and a date. Depending on the task, and
the type of event, a different staff member is assigned to complete the task.
I would like to create a conditional format for each task which will do a
wildcard search on the text in B1.

So condition 1 might be: find any text in B1 containing "*ABC*", "*HIJ*" or
"*QRS*", then pink.
Condition 2 would be : find any text in B1 containing "*DEF*", "*KLM*", or
"*TUV*", then yellow

Fortunately, condition 3 would be the formula you provided in my earlier
query!

I've searched the discussion groups (and learned A LOT - now wouldn't that
be a great function?), but despite trying variations of SEARCH, AND, IF and
OR, I'm no further a-head for all my head scratching (So to speak).

Many thanks & curtsies.




"T. Valko" wrote:

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

They have any errors trapped in the formula:

=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36 ,14,0)),"",........

Biff

"Max" wrote in message
...
Try this ..

Assume your range is A1:D10

Select the range (with A1 active),
then apply CF using settings:

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

Condition 2, Formula is:
=AND(A1<"",NOT(ISERROR(A1)))
Format: Blue fill

OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote:
Hi
I have a workbook that creates a monthly task calendar in one of the
spreadsheets. in that sheet, there is a list of tasks in Column A, while
Column B contains the formula :
=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36, 14,0)),"",VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14 ,0))&""

where A3 is a date. The cells in Column B therefore appear either blank
or
return a value.

I want to apply conditional formatting only to those cells in Column B
that
return a value. The colour formatting will alert a staff member that they
have a task to complete on a certain day if it is shaded with 'their'
colour.
I've fuddled around with variations including IF, ISBLANK and
NOT(ISBLANK)
but to no avail. can anyone assist?

Thanks.

Try this:

Condition 1
Formula Is:

=OR(ISNUMBER(SEARCH("ABC",B1)),ISNUMBER(SEARCH("HI J",B1)),ISNUMBER(SEARCH("QRS",B1)))

Condition 2
Formula Is:

=OR(ISNUMBER(SEARCH("DEF",B1)),ISNUMBER(SEARCH("KL M",B1)),ISNUMBER(SEARCH("TUV",B1)))

If you have a whole bunch of these then it will probably be better to list
them somewhere on your sheet and then use something like this:

F1 = ABC
F2 = HIJ
F3 = QRS
F4 = DEF
F5 = KLM
F6 = TUV

Then:

Condition 1
Formula Is: =MATCH(B1,F1:F3,0)

Condition 2
Formula Is: =MATCH(B1,F4:F6,0)

Biff

"Vibeke" wrote in message
...
Thank you very much for your assistance. Worked a charm. If I could
trouble
you with an extended question on a similar problem, I would be very
grateful
for any assistance.

Column B's formula returns an event and a date. Depending on the task,
and
the type of event, a different staff member is assigned to complete the
task.
I would like to create a conditional format for each task which will do a
wildcard search on the text in B1.

So condition 1 might be: find any text in B1 containing "*ABC*", "*HIJ*"
or
"*QRS*", then pink.
Condition 2 would be : find any text in B1 containing "*DEF*", "*KLM*", or
"*TUV*", then yellow

Fortunately, condition 3 would be the formula you provided in my earlier
query!

I've searched the discussion groups (and learned A LOT - now wouldn't that
be a great function?), but despite trying variations of SEARCH, AND, IF
and
OR, I'm no further a-head for all my head scratching (So to speak).

Many thanks & curtsies.




"T. Valko" wrote:

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

They have any errors trapped in the formula:

=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36 ,14,0)),"",........

Biff

"Max" wrote in message
...
Try this ..

Assume your range is A1:D10

Select the range (with A1 active),
then apply CF using settings:

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

Condition 2, Formula is:
=AND(A1<"",NOT(ISERROR(A1)))
Format: Blue fill

OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote:
Hi
I have a workbook that creates a monthly task calendar in one of the
spreadsheets. in that sheet, there is a list of tasks in Column A,
while
Column B contains the formula :
=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36, 14,0)),"",VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14 ,0))&""

where A3 is a date. The cells in Column B therefore appear either
blank
or
return a value.

I want to apply conditional formatting only to those cells in Column B
that
return a value. The colour formatting will alert a staff member that
they
have a task to complete on a certain day if it is shaded with 'their'
colour.
I've fuddled around with variations including IF, ISBLANK and
NOT(ISBLANK)
but to no avail. can anyone assist?

Thanks.

Ooops! I forgot that you were looking for a substring:

If you have a whole bunch of these then it will probably be better to list
them somewhere on your sheet and then use something like this:

F1 = ABC
F2 = HIJ
F3 = QRS
F4 = DEF
F5 = KLM
F6 = TUV

Then:

Condition 1
Formula Is: =MATCH(B1,F1:F3,0)

Condition 2
Formula Is: =MATCH(B1,F4:F6,0)


Make those formulas:

Condition 1
Formula Is: =OR(ISNUMBER(SEARCH(F1:F3,B1)))

Condition 2
Formula Is: =OR(ISNUMBER(SEARCH(F4:F6,B1)))

Biff

"T. Valko" wrote in message
...
Try this:

Condition 1
Formula Is:

=OR(ISNUMBER(SEARCH("ABC",B1)),ISNUMBER(SEARCH("HI J",B1)),ISNUMBER(SEARCH("QRS",B1)))

Condition 2
Formula Is:

=OR(ISNUMBER(SEARCH("DEF",B1)),ISNUMBER(SEARCH("KL M",B1)),ISNUMBER(SEARCH("TUV",B1)))

If you have a whole bunch of these then it will probably be better to list
them somewhere on your sheet and then use something like this:

F1 = ABC
F2 = HIJ
F3 = QRS
F4 = DEF
F5 = KLM
F6 = TUV

Then:

Condition 1
Formula Is: =MATCH(B1,F1:F3,0)

Condition 2
Formula Is: =MATCH(B1,F4:F6,0)

Biff

"Vibeke" wrote in message
...
Thank you very much for your assistance. Worked a charm. If I could
trouble
you with an extended question on a similar problem, I would be very
grateful
for any assistance.

Column B's formula returns an event and a date. Depending on the task,
and
the type of event, a different staff member is assigned to complete the
task.
I would like to create a conditional format for each task which will do a
wildcard search on the text in B1.

So condition 1 might be: find any text in B1 containing "*ABC*", "*HIJ*"
or
"*QRS*", then pink.
Condition 2 would be : find any text in B1 containing "*DEF*", "*KLM*",
or
"*TUV*", then yellow

Fortunately, condition 3 would be the formula you provided in my earlier
query!

I've searched the discussion groups (and learned A LOT - now wouldn't
that
be a great function?), but despite trying variations of SEARCH, AND, IF
and
OR, I'm no further a-head for all my head scratching (So to speak).

Many thanks & curtsies.




"T. Valko" wrote:

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

They have any errors trapped in the formula:

=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36 ,14,0)),"",........

Biff

"Max" wrote in message
...
Try this ..

Assume your range is A1:D10

Select the range (with A1 active),
then apply CF using settings:

Condition 1, Formula is: =ISERROR(A1)
Format: White font (to mask the error returns, if any)

Condition 2, Formula is:
=AND(A1<"",NOT(ISERROR(A1)))
Format: Blue fill

OK out
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Vibeke" wrote:
Hi
I have a workbook that creates a monthly task calendar in one of the
spreadsheets. in that sheet, there is a list of tasks in Column A,
while
Column B contains the formula :
=IF(ISERROR(VLOOKUP(A3,'calendar~data'!$B$5:$O$36, 14,0)),"",VLOOKUP(A3,'calendar~data'!$B$5:$O$36,14 ,0))&""

where A3 is a date. The cells in Column B therefore appear either
blank
or
return a value.

I want to apply conditional formatting only to those cells in Column
B
that
return a value. The colour formatting will alert a staff member that
they
have a task to complete on a certain day if it is shaded with 'their'
colour.
I've fuddled around with variations including IF, ISBLANK and
NOT(ISBLANK)
but to no avail. can anyone assist?

Thanks.