Hi All,
Not sure if this is possible but gettting confused trying to make it work. Any help would be most appreciated.
As per the formula below I'm essentially asking Jet to return information based on the result of another cell which is populated with a year. So if the year selected is either 2011 or 2012 then run the first query, if it's anything else run the other query. It has to be done this way as the info is in two separate tables.
When I have the NL query on it's own (for instance just looking at the 2011/2012 tables) it returns all the information I would expect but when I put the IF statement in and the NL query relating to the GL30000 table it only ever returns one line of data. That data relates to the selected period/year so that part seems to be working but rather than returning all the data I only get one line.
=IF(OR(Options!$D$8=2011,Options!$D$8=2012),'Detail Enquiry'!NL("Rows","GL20000","DEX_ROW_ID","TRXDATE",">="&StartDate,"TRXDATE","<="&EndDate,"Company=",Options!$D$10,"ACTINDX",'Detail Enquiry'!$A$4),NL("Rows","GL30000","DEX_ROW_ID","TRXDATE",">="&StartDate,"TRXDATE","<="&EndDate,"Company=",Options!$D$10,"ACTINDX",'Detail Enquiry'!$A$4))
Paul
Date
Votes
2 comments
-
Jet Reports Historic Posts Sorry - think I've solved this myself. The IF statement needed to reside with the NL query. The below formula now seems to return multiple lines, not 100% sure why though……………
=NL("Rows",IF(Options!$D$8<2011,NL("Filter","GL30000","DEX_ROW_ID","TRXDATE",">="&Kurt,"TRXDATE","<="&Courtney,"Company=",Options!$D$10,"ACTINDX",'Detail Enquiry'!$A$4),NL("Filter","GL20000","DEX_ROW_ID","TRXDATE",">="&Kurt,"TRXDATE","<="&Courtney,"Company=",Options!$D$10,"ACTINDX",'Detail Enquiry'!$A$4))) -
Jet Reports Historic Posts You are correct! I'm glad you figured it out. Basically, the reason it doesn't work with the NL(Rows) embedded inside the IF function is that we search the worksheet for =NL("Rows" when looking for replicators to expand. So if the replicator is embedded in another function, we don't find it and don't expand it. Does that make sense?
Regards,
Hughes
Please sign in to leave a comment.