I posted the following in worksheetfunctions.. but I understand this NG
is not active so posting it here again.

Hello All,

I am using Windows XP/Office 2003 and have the following problem:


I have a worksheet with 8 Columns as follows:
A B C D E
F G H
S# Ref. No Date CODE Company Problem Status ClosedDate


I wish to test two Cells viz. Column C (Date) and Column H
(ClosedDate).

If the date entered in Date is greater than or equal to Today(), the
value displayed in Column G will be "Active"

I have entered the following formula in G column which works ok except
it shows VALUE when copied down the column.. how can I modify the
following formula to display a " " (blank) if there is no value in
Column C (Date)

=IF(AND(ISNUMBER($C2),$C2>=(TODAY())),"Active",IF(SEARCH("#",$H2),"Closed"))

Also I wish to add another IF to the formula which should test the Date
in Column H and if the date entered is preceded by another symbol ~ for
eg ~20-03-05 then Column G should display "Cancelled"

My intention is to have "Active in Column G when the first entry is
made in Column C (Date). Later on Column G (Status) should show
Cancelled (if the date is preceded by a #) or Cancelled (if the date is
preceded by a ~)


Can anybody give me a clue or better suggestions please

TIA

Rashid