I have a sheets function which returns a new sheet for each Fund Number (Fund No.). But I would like the automatically generated sheet name to be more than just the fund number. For example, if a fund no. is ABC, I would like the automatically generated sheet to read "ABC AP Total" not just the usual "ABC". Does anyone have any ideas on how to accomplish this? I have tried renaming the original sheet but, as you all already know, the existing sheet is removed when the Jet Report is generated.
Thanks in advance for any ideas!
Date
Votes
3 comments
-
Jet Reports Historic Posts Hi DMDG,
If you are using a connection to NAV (Navision) you can use this syntax to create the custom sheet names:
=NL("Sheets","Customer","=NF(,""No."")&"" AP Total""")
For this example I used the Customer Table and returned the customer number and added your text to the end. The trick is to use the NF() inside the Field parameter of the NL() and just append it with the text you need. Please note that this will also modify the value returned in the cell of the report so when you have other functions linking to this cell, you will have to remove the "AP Total" text from it so your other filters work correctly.
Does this help? -
Jet Reports Historic Posts This definitely helps! Now let me throw a curve at you. Instead of targeting a NAV table, what if I want to target a list (range) of cells that I have in the spreadsheet? Can I still have the additional text? If so, where do a place it?
Example: Cells A1-A5 on Sheet 1 have a list of Values. The Sheet function is currently set as follows:=NL("Sheets",Sheet1!$A$1:$A$5)This will return a new sheet for each value in my customized list. But since there is no field parameter where can I place the additional text? -
Jet Reports Historic Posts Suggestion and its the one I use all the time as I have some 90 branches to deal with and no one remembers the branch numbers and I have to create myriad reports with multiple sheets
instead of using the Fund no use the name to create sheet then do a first on appropriate table to get the No ( filter by Name) . This gets yoru sheets named by name rather than number
cella NL(Sheets, Source, Name, etc) cellb Nl(First, Source , Number, Name,cella) and do all the required filtering on cellb
As long as you can get from name to master key No. or if you wanted in your case to do a vlookup and put descritption on column 1 and code in second column that works as well
ie cellb becomes vlookup (Sheet1, $A$1:$B$5,2 , cella)
Please sign in to leave a comment.