Is there a way to conditional specify the sheets as the "what" of an NL function? My users want the ability to run a report and at run time determine weather they want the data sperated by sheets or just. I have tried nl(if(condition,"Sheets","Rows")…." but it always seems to do the rows…..
Date
Votes
1 comment
-
Jet Reports Historic Posts Hi Tim,
You would have to have 2 different NL functions, one for sheets and one for rows like this:=NL("Sheets",IF(<Some Condition>,"Customer",{"SheetName"}),"Name",…) =NL("Rows","IF(<Some Condition>,{""},"Customer"),"Name",…)
This assumes you are using the Customer table. The NL(Sheets) function here will always replicate sheets, but I am specifying an array with just a single value if the condition is false (that single value will become the name of the sheet if the condition is false). Similarly, the NL(Rows) function will always replicate, but when the condition is true, I'm just replicating an array of a single empty value which means the cell will just be blank. Then you could have an Excel IF function which gets the value of the either the NL(Sheets) or NL(Rows) function depending on whether the value of the NL(Rows) function is blank.
The reason you have to do something like this is that when Jet is replicating, it searches the worksheet for functions starting with =NL("Rows" and =NL("Sheets" so you can't have a formula determine whether the function is NL(Rows) or NL(Sheets).
Regards,
Hughes
Please sign in to leave a comment.