Hi All,
Me again!!
Currently trying to get the below NL formula to work but it's not. STARTMONTH refers to a named range within the worksheet that holds a date that can change as an input is changed. If I change STARTMONTH to an actual date the formula works and returns the info I want but as soon as I used the named range instead it falls over. I find this odd as other references to named ranges have always been fine.
=NL("Rows","GL20000","TRXDATE","TRXDATE",">=STARTMONTH","Company=",Options!$D$10,"ACTINDX",Data!$A$4)
Any help anyone could offer would be most appreciated.
Regards
Paul
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi Paul,
You have the named range quoted in this formula. Since it's quoted, Excel will treat "STARTMONTH" as the actual filter value rather than getting the value from it as a range. You might want something more like this:=NL("Rows","GL20000","TRXDATE","TRXDATE",">="&STARTMONTH,"Company=",Options!$D$10,"ACTINDX",Data!$A$4)
Now this still may not work since the date from the STARTMONTH range will probably get converted to an Excel serial date (basically a large number). To get around this, you can use either the Excel TEXT function or the Jet NP(DateFilter) function like this:=NL("Rows","GL20000","TRXDATE","TRXDATE",NP("DateFilter",STARTMONTH),"Company=",Options!$D$10,"ACTINDX",Data!$A$4)
The NP(DateFilter) formula will construct a date filter that looks like the following (assuming the value in STARTMONTH is 12/31/2011): "12/31/2011.." which is the same as ">=12/31/2011". Does this help?
Regards,
Hughes -
Jet Reports Historic Posts Hughes,
The first solution has worked a treat thank you!! Been puzzling over that most of the afternoon and you've solved it in minutes. Many thanks!!
I obviously need to be more careful in the construction of my formulae within Jet. I'm sure this is something I'm going to use multiple times within Jet so you've saved me plenty of time there.
Paul
Please sign in to leave a comment.