I'd like to be able to create sheets either based on the Dimension one (Department Code), or on a preset range on the options page. When I entered this formula with the If function and two NL Sheets functions, Excel faults and closes. Is there a work around to be able to accomplish the same?
=IF($D$2=2,NL("Sheets","G/L Entry","Department Code","0",$L$19,"Department Code",Dept,"G/L Account No.",$L$8,"Fund No.","*","Posting Date",$L$5),NL("Sheets",Options!$D$121:$D$122))
Hi Bob -
The Jet replicating functions [ NL(Rows), NL(Columns), and NL(Sheets) ] cannot be enclosed within an Excel IF() function.
You would need to use Conditional Replication... by placing your IF() within the NL(Sheets) function.
Thanks Harry! The link to Conditional Replication was very helpful.
I modified the formula, but Excel does not accept it as written. =NL("Sheets",if($D$2=2,("G/L Entry","Department Code","0",$L$19,"Department Code",Dept,"G/L Account No.",$L$8,"Fund No.","*","Posting Date",$L$5),(Options!$D$121:$D$122)))
I'm not sure what is incorrect since Excel shows the formula as =NL("Sheets",if($D$2=2,(TRUE),(FALSE)))
What do I have worng?
Hi Bob -
Here is a simplified version of what you are attempting.
Here's my report where by both my table name and field are "conditionalized" (ya gotta love making up words):
When I run my report withe a value of 2, I get the list from the specified range:
When run with a value of 1, I get the desired field:
Your situation is obviously more complex, and you would have to conditionalize the filters, as well.
Hi Harry, I think I have followed your format, but Excel does not accept the formula. It appears that I just don't have my parenthesis in the correct places, but grouping look correct. Can you tell what I have wrong? =NL("Sheets",if($D$2=2,("G/L Entry","Department Code","0",$L$19,"Department Code",Dept,"G/L Account No.",$L$8,"Fund No.","*","Posting Date",$L$5),if($D$2=1,Options!$D$121:$D$122),""))
Hi Bob -
Each parameter of the NL(Sheets) function would need to have an IF() function included:
=NL("Sheets",IF($D$2=2,"table name",range),IF($D$2=2,"field name",""),IF($D$2=2,"filter field",""),IF($D$2=2,"filter","")...
So the first part of your function would look like:
=NL("Sheets",if($D$2=2,"G/L Entry",Options!$D$121:$D$122),IF($D$2=2,"Department Code",""),IF($D$2=2","Company=
,""),IF($D$2=2,$L$19,""),IF($D$2=2,"Department Code",""),IF($D$2=2,Dept,"")...
Think of it this way...
The NL function has these parameters:
NL( WHAT, TABLE, FIELD, FilterField1, Filter1, FilterField2, Filter2, etc.)
You function needs to say:
Hi Harry, I have enter the formula and it is accepted but renders a blank. When I use the JET Function wizard to evaluate the formula it shows the Filter Field "G/L Account No. has an empty filter argument which is not allowed. I'm confused as it shows the G/L account range, 10000..98999, in the formula. So, now what do I have wrong? Hey, I really appreciate your help with this as it has not been intuitive to figure out.
=NL("Sheets",IF($D$2=2,"G/L Entry",Options!$D$121:$D$122),IF($D$2=2,"Department Code",""),IF($D$2=2,"0",""),IF($D$2=2,$L$19,""),IF($D$2=2,"G/L Account No.",""),IF($D$2=2,$L$8,""),IF($D$2=2,"Fund No.",""),IF($D$2=2,"*",""),IF($D$2=2,"Posting Date",""),IF($D$2=2,$L$5,""))
Hi Bob -
What version of Jet Reports are you running?
(by the way, if you are always going to use * for filtering your Fund, there's not reason to filter it)
JET Professional 2018 18.1.18149.1 on our server which has more horsepower than my laptop which is running
JET Report 2019 19.3.18339.1.
Let's run a quick test before I aim you to Jet Tech Support...
What happens if you try to enter your function in Excel on your laptop?
Sorry for the delay, but I was working from home and wanted to confirm this is still an issue when I'm in the office with the best connection to our server. The formula shown below still returned a blank. After removing the filters for G/L Account No., Fund and Posting Date, the formula still returned a blank. I added a filter for Department <>’’ and that portion of the formula is now working as expected!!!
=NL("Sheets",IF($D$2=2,"G/L Entry",Options!$D$121:$D$122),IF($D$2=2,"Department Code",""),IF($D$2=2,"0",""),IF($D$2=2,$L$19,""),IF($D$2=2,"G/L Account No.",""),IF($D$2=2,$L$8,""),IF($D$2=2,"Fund No.",""),IF($D$2=2,"*",""),IF($D$2=2,"Posting Date",""),IF($D$2=2,$L$5,""))
Now, I'd like to make the Options!$D$121:$D$122 option dynamic. I'll submit a new post for it.