Sorry Benny, I'm stumped on that one.
BTW, Leo's formula is a little trimmer than mine & therefore a little easier
to use. Suggest you adopt it, once you figure out your other issue
"Benny" wrote:
Duke,
I just found out that the worksheet that I was triying to use your formula
on is originated from an "external data" using Excel to query a database in
our server at work. And it doesn't work on it. But if I copy the results
(without the column headings) and paste them in a clean worksheet, it works.
I didn't know that. Do you know how to work around that?
"Duke Carey" wrote:
Benny - are you sure the cells you are testing are D2 and R2? The formulas I
provided are looking at precisely those 2 cells. If you have your data in
other cells, then all you'd get is a "No" with the first one and a "Not
Defined" with the second one.
Double check please, because both formulas work fine in my spreadsheet
Duke
"Benny" wrote:
Mr. Duke Carey,
I tried and with the first formula all I get is "No".
The second formula returns "Not Defined".
Should I create a column for each situation?
Thanks for your help.
"Duke Carey" wrote:
This formula returns "yes" for either of the two cases in Situation #1, and
"No" for all other cases
=IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes","No")
If you only want "No" to come up in the circumstance you describe in
Situation #2, then we need to amend the formula to this
=IF(OR(AND(NOT(ISERROR(MATCH(D2,{1,2,3,4},FALSE))) ,R2=1),AND(NOT(ISERROR(MATCH(D2,{1,2},FALSE))),R2= 2)),"Yes",IF(AND(NOT(ISERROR(MATCH(D2,{5,7},FALSE) )),NOT(ISERROR(MATCH(R2,{1,2},FALSE)))),"No","Not Defined"))
"Benny" wrote:
Need help with the following 2 situations involving data contained in 2 fields
to create a Yes or No field according to the following:
Situation #1
IF cell D2={1,2,3,4} and cell R2=1 then =Yes
And also if cell D2={1,2} and cell R2=0 then is also = Yes
Situation #2
Cell D2={5,7} and cell R2={0,1} then = No
--
Benny
|