0

Using the Sheets Replicator

Jet Reports Historic Posts

November 30, 2016 23:00

Answered

Follow

Hello,
I had a question about the Sheets replicator in Jet. Is it possible to have multiple ‘sheets’ functions on the same workbook? I am trying to report on multiple companies and multiple departments within those companies. Is it possible to have Jet create a tab for each department in the company? For example, If I have 5 companies (A,B,C,D,E) and 5 departments in each company (1,2,3,4,5) the tabs I want Jet to produce are:

A1, A2, A3, A4, A5, B1, B2 … E3,E4,E5.

Is this something that can be done in Jet Express?

Thanks,
Jack

• Facebook
• Twitter
• LinkedIn

6 comments

• 1
  
  Harry Lewis October 16, 2018 19:31

  Hello Bob -

  The example function in that cell was a way of taking two pieces of information and creating a single value from them, which could then be used for creating unique sheet names.  In the example, those values were A1, A2, A3, B1, B2, C1, C2, C3, C4, D2, and D4.

  You mentioned that your combined value does not work in other formulas.  

  I interpret that as meaning that, not only do you want to create individual sheets for multiple departments in multiple companies, but that you also then want to use an individual department and company name as filters in other functions on the sheet.

  To accomplish that, you would need to have those individual values available on each sheet (not just as a combined value in the sheet name).

  Here's how I would go about that:

  I would use the technique above to create my static list of company and department names...

  

  This gives me my static list with a hyphen between the company name and the department name:

  

  The hyphen (or some other character that I *KNOW* will NOT be in a company or department name) is important.

  Now, I can copy that list to my report.

  

  Now, I can build a Jet function and some Excel logic to get the sheets I need and make sure I have the department name and the company name in separate cells (thanks to that hypen between the two):

  

  So that, when I run the report, I get this:

  

  Edited by Harry Lewis October 16, 2018 19:32
• 0
  
  Jet Reports Historic Posts November 30, 2016 23:06

  Hello Jack -
  
  The NL("Sheets") function is available in Jet Professional.
  
  Jet Express supports the NL("Table") function and the GL() function.

• 0
  
  Jet Reports Historic Posts November 30, 2016 23:51

  To answer your question in a more in-depth manner:
  
  Here are my assumptions:
  - using Jet Professional
  - using Dynamics NAV
  - getting the company names from the Company table
  - gettting the department names from the Dimension Value table
  
  I cannot think of a way to do this using a single NL(Sheets) or NL(Filter) function.
  
  The easiest way I can think of would be to use some NL("Rows") functions to create the list.
  
  
  Copy that list to a static worksheet.
  
  
  Then, create an NL(Sheets) function that uses that static list:
  
  
  I hope that helps.

• 0
  
  Jet Reports Historic Posts December 05, 2016 16:43

  Thanks for the response, HPDeskJet.
  
  I am actually using Jet Express for Dynamics GP. Although NL(Sheets) is unavailable, GL(Sheets) is a usable function but does not seem to allow replication from a static list. Based on this, I don't think it will be possible using Jet Express. Thanks for the help!

• 0
  
  Bob Thorp October 16, 2018 17:32

  I'd like to make this work, (Multiple sheets for multiple departments in multiple companies) but don't understand the function of E7 (=C7&D7)  in hte first screen shot, nor the format of static list (B3-B12).

  I can get the list of Companies and Departments just fine, but C7&D7 returns CompanyDepartment (CP-Corporate141) which does nto work in other formulas, so what have I missed.

  I'm using JET Professional 2018 18.1.18149.1 and Dynamics NAV 2016.

  Thanks, Bob T 

• 0
  
  Bob Thorp October 17, 2018 11:31

  Thank you so very much Harry!  Your solution works great and does exactly what I wanted.