Multiple Criteria in one cell.

[cwinnyk]

I am in the process of converting multiple linked Lotus 123 R5 spreadsheets into Excel 2002. After conversion, Excel doesn't like most of the criteria used for for the DBASE function. I've been able to fix some of it, but one problem in particular I am having is with the use of the AND operator in the criteria. For example, in Lotus:

+DATE RECOG<2#AND#+DATE RECOG>-6

means DATE RECOG has to be both less than 2 and greater than -6 to be true.

In Excel, it converts to this:

=AND(DATE_RECOG<2,+DATE_RECOG>-6)

DATE RECOG (not DATE_RECOG) is the column heading in that criteria column. My question is: Is there a way to utilize the AND operator when specifying criteria? I know I could create another DATE RECOG column to store the 2nd value if I wanted to, but then I would have to go around to all the spreadsheets and update the criteria range, which would be highly time consuming. Any suggestions?

Thanks in advance for your help.

Chris

 

[is1ma1il]

can you send the spreadsheet to is1ma1il@hotmail.com for me to look at?

 

[Wray69]

=IF(AND(DATE_RECOG<2,DATE_RECOG>-6),&quot;TRUE&quot;,FALSE&quot

That should work for you.

Cheers,

Wray

 

[Wray69]

By the way if they are not already, you will need to make those columns named ranges. To do this select the column go to INSERT/NAME/DEFINE

Wray

 

[cwinnyk]

Wray69. Thanks for the advice. Unfortunatly that doesn't work either. Excel seems to get mad at me whenever I put the column heading in any of the rows below it, and then my functions that call that criteria return zero, which is incorrect. I had to fix alot of criterias that worked in Lotus, but not in Excel. For example. One of my criteria columns had the heading of HOURS. In lotus, the next row would be:

+Hours>0

In Excel this didn't work, because it got converted to:

=Hours>0

If I remove the &quot;=Hours&quot; part, however, it works fine.

 

[Zathras]

Put two columns in the criteria range with the title DATE RECOG in each. Then in one, put one criterion and in the other, the other. For example:
[blue]

D1: 'DATE RECOG
E1: 'DATE RECOG
D2: '<2
E2: '>-6

[/color]

 

[cwinnyk]

Hi Zathras. Unfortunatly, we are trying to avoid splitting it up into different cells, although this problem is frustrating me to the point where i may just have to do that anyway. Wray69, I think you are on the right track, actually. This is where I'm at:

=AND(DATE RECOG<3,DATE RECOG>-6)

Unfortunatly, this returns true all the time, even if its less than 3 and greater than -6. I took out DATE RECOG from the first row of my criteria and now at least my DBASE function is actually returning data, albeit its the wrong data. This is the function that refers to the criteria...

=DSUM($DBASE,AW$7+1,CHRIS)