I have a excel 2003 spreadsheet with a list containing order number, date,
status. I want to create a function to give a value in a new field for all
order numbers if one of the statuses is 'on hold'

My data would be like below
Order# Date Status
1 7/1/2005 Received
1 7/2/2005 Closed
2 7/1/2005 Received
2 7/2/2005 On Hold
2 7/5/2005 Closed

In the new field I would like all that lines for order 2 to have a "Yes"
value in the new field because one of the status was On Hold

say your data is in the range A2:C6 and you want "Yes" on column D then on
cell D2 type the formula below and fill all the way down to cell D6

=IF(ISERROR(MATCH(1,($C$2:$C$6="On Hold")*($A$2:$A$6=A2),0)),"","Yes")
array entered(ctrl+shift+enter)

"Jdull" wrote in message
...
I have a excel 2003 spreadsheet with a list containing order number, date,
status. I want to create a function to give a value in a new field for
all
order numbers if one of the statuses is 'on hold'

My data would be like below
Order# Date Status
1 7/1/2005 Received
1 7/2/2005 Closed
2 7/1/2005 Received
2 7/2/2005 On Hold
2 7/5/2005 Closed

In the new field I would like all that lines for order 2 to have a "Yes"
value in the new field because one of the status was On Hold

This formula will _only_ put a "Yes" on orders "On Hold" and still not
"Closed".

=CHOOSE(1+SUMPRODUCT((A7=$A$3:$A$7)*(($C$3:$C$7="O n
Hold")+($C$3:$C$7="Closed")*3)),"","Yes","","","")

Hope it can be of use
Ola Sandström


Also see encl. zip-file:
http://www.excelforum.com/attachment...tid=3572&stc=1


+-------------------------------------------------------------------+
|Filename: Book5.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3572 |
+-------------------------------------------------------------------+

--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=384573