I have a report that uses a range of rows as a filter. Sales numbers are copied & pasted into column B starting at B6, and then the report is run on those numbers. The number of rows can vary.
I currently have this that works:=NL("join",B6:B23,"|")
The cell that contains that formula is used as a filter. Right now I have to edit the last row each time I get a new set of numbers. If the last row is 50, B23 is changed to B50.
I would like for the user to be able to enter the last row in a cell and then use a variable or a cell reference in place of B23. Say the user enters 50 in cell C3. Is there a way to reference that cell in the formula above?
Something like this:=NL("join",B6:=C3,"|")
That of course is incorrect syntax.
Thanks for any help.
David
Date
Votes
7 comments
-
Jet Reports Historic Posts Official comment David,
Be a little more clear about what you mean by "will not accept parameters". The way people typically set up a Jet report that uses +Values or +Lock is to set up an options sheet which does not contain any Jet formulas other than NL(Lookup) and then enter parameters on that sheet. In report mode, when you enter a parameter on the options sheet, you then have to re-run the report in order for that parameter to affect the Jet functions (since those functions get converted to values in report mode with +Values or +Lock).
Is the problem you are experiencing that you enter a value on the options sheet and then re-run the report and the value gets changed back to its original value? Possibly this could be because you have other Jet formulas on your options sheet like your NP(Join) which are causing Jet to treat this sheet just like your report sheets. You are only allowed to use NL(Lookup) formulas on an options sheet; any other Jet formulas will cause Jet to treat that sheet like a report sheet. Is this the case?
Regards,
Hughes -
Jet Reports Historic Posts Hi David,
I think what you're looking for is the Excel Indirect function. The Indirect function takes a reference string like "C3" and gets the value from that cell. So if cell C3 holds the address of the last cell in your filter range ("B23" in this case), I could use this formula:=NP("Join",INDIRECT("B6:"&C3),"|")
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Thanks, Hughs. I'm still having a problem. I had to slightly modify your suggestion. The below code works if a value is entered in C3 while in Designer mode. If a change is made to C3 while in Report mode the report ignores it. After the report runs, C3 has the value that was entered in Design mode instead of the value entered in Report mode. The report uses another filter, and that filter has the same problem. I have not seen that before. I even redid the report from scratch. It will not accept filters in Report mode.
=NP("Join",INDIRECT("B6:B" & C3),"|")
Here is the code that is run. Options!E6 is the other filter. Options!H3 contains the above Join.=NL("Rows","Sales Line",,"No.",Options!$E$6,"+Document No.",Options!$H$3,"Document Type","Order","Quantity","<>0")
Does anyone know why it will not accept filters in Report mode? All of my other reports will. I just checked a few to make sure. -
Jet Reports Historic Posts Hi David,
I'm assuming you have either +Lock or +Values in cell A1 of one of the sheets in this report. This means all Jet formulas get changed to their values in report mode, which means that your NP(Join) formula will get converted to its value when the report runs and will not update when you change the value in cell C3 unless you re-run the report. Probably your other reports do not have +Values or +Lock in cell A1. Is this the problem?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
You are correct. I am using +Values. When I remove that I am able to change filters in report mode.
All of my other reports use +Values, and I am able to enter parameters in report mode. This is the only report where I have separated Jet functions. I have one function accessing the other using a cell reference as in my code posted above. Is that the reason why I can't enter parameters in report mode? I'm not sure if combining the two functions will make a difference. I will have to figure out how to embed the NP function into the NL function if that might be the solution. I might have to make the user a designer if there is no other solution.
Thanks for your help. -
Jet Reports Historic Posts Thanks, Hughes. Yes, that is the case. I have the NP function on the Options sheet. I thought that might be the problem but had to move on to other things. I'll see if I can get it on the Report sheet. I'll have to experiment some on the syntax with the NP(JOIN) to get it to reference the Options sheet.
-
Jet Reports Historic Posts It was an easy solution. The formula below is now on my Report page instead of the Options page.
=NP("join",INDIRECT("Options!B6:B" & Options!E5),"|")
Thanks again
Please sign in to leave a comment.