Hi,
I'm returned data from two tables and then using an IF(AND formula to determine if a value from table 1 is between two values from table 2 but my IF(AND formula is returning false every time as if is not reading the Jet data cells as numbers. I can get my IF(AND formula to work if I hard code my numbers but not is I use Jet. I have trying both NL and NF to replicate my table data.
My IF(AND formula is =IF(AND(C4>=Sheet2!$F$4,C4<=Sheet2!$E$4),"TRUE","FALSE")
I'm trying to determine if the value from table 1, C4, is between the values from table 2, Sheet2 F4 and Sheet2 E4.
Any help out there?
Thanks
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi,
So it probably depends on what fields you are retrieving in those cells. What tables/fields are you actually retrieving? One possibility is that one or more of these fields are Code fields in NAV. Code fields may look like numbers in NAV, but since they can store numbers and letters, they are actually stored as strings. Could this be the case?
Regards,
Hughes -
Jet Reports Historic Posts Hi Isteele,
I think your problem is still solvable, even though the fields you retrieve are Code fields just like what Hughes said.
The key is to use the VALUE() Function in Excel, where it will convert your string into number. So your formula would look like this:=IF(AND(VALUE(C4)>=VALUE(Sheet2!$F$4),VALUE(C4)<=VALUE(Sheet2!$E$4)),"TRUE","FALSE")
Does it help ?
Andy
Please sign in to leave a comment.