Multiple IF Condition

[prkhan56]

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"))

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 a * 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

 

[Anne Troy]

Rashid, this is NOT a good layout. Get * out of your dates. Excel thinks it
is not a date anymore, and it is text.
In computers, * is "variable" and it is NOT good to use it for a symbol.
Here is a sample for "is blank":

=if(isblank(C2),"",B2*C2)

That should help you to learn it.
*******************
~Anne Troy

www.piersontech.com
www.mrexcel.com

 

[prkhan56]

Hi Anne,
Can u give me a better suggestion... how can I implement
=if(isblank(C2),"",B2*C2)
in the followng
=IF(AND(ISNUMBER($C2),$C2>=(TO­DAY())),"Active",IF(SEARCH("#"­,$H2),"Closed")
I can use any other symbol instead of *.. Can u suggest a better
formula

Thanks for your time