0

Two sheets made with NL("Sheets") with same value

Jet Reports Historic Posts

August 09, 2012 13:16

None

Follow

I have two sheets in my workbook. Each has identical NL("Sheets";….)-functions, but different funcitons in the sheets. For that reason the name of the sheets will be the same, but the second sheet is given the name with "(2)" after.
For example:
After I run "Refresh" sheet 1 is called "2908" and sheet 2 is called "2908 (2)".

How can I set a different name for sheet 2?

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts August 09, 2012 14:47

  Hi,
  
  You can't specify different names for the sheets. NL(Sheets) will always create sheets with the names of the replicated values. If a sheet with that name already exists, the name will get the (2) after it because Excel doesn't allow 2 sheets to have the same name. If I were you, I would consider splitting this up into 2 reports. Is there a reason you need all these sheets duplicated twice in the same file?
  
  Also, just out of curiousity, what would you rename the 2nd series of sheets to, given that 2 sheets can't have the same name? If you're using NAV, we might be able to create a calculated field in your NL(Sheets) formula to modify the name that it is returning.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 10, 2012 07:24

  Hi and thank you for a quick reply!
  
  The customer want's it like this. Two sheets in one reports. I've tried putting it all in on sheet but they don't want't it that way.
  I want to rename the second sheet because the "(2)" doesn't like very nice. I would like to call it "2908 simple".
  Yes, I'm using NAV. What can I do?

• 0
  
  Jet Reports Historic Posts August 10, 2012 12:40

  What does your actualy NL(Sheets) formula look like? I can show you how to modify it if I know what the formula is.
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts August 13, 2012 06:22

  Hi!
  
  The formula is like this:
  =NL("Sheets";"Resource";"No.";"+Line Manager";$C$10;"Blocked";"No")
  This will give me "2029 (2)"
  I would like "2029 (simple)"
  
  Thank you in advance!

• 0
  
  Jet Reports Historic Posts August 13, 2012 14:52

  So the formula might look like this:
  
  =NL("Sheets";"Resource";"=NF(;""No."")&"" (simple)""";"+Line Manager";$C$10;"Blocked";"No")
  Then you'll need to get the actual value without the " (simple)" for filtering purposes. To do this, you could do something like this (assuming the previous formula is in cell C3):
  
  =LEFT(C3,LEN(C3)-9)
  Does that help?
  
  Regards,
  Hughes