Hello,
I am working to find a solution on a jet question. I am working out of two tables in the the same company.
One has company code, the other has the company code location description
I have created a formula that replicates a sheet for each company code but I want the sheet name to be the company code location. I was thinking that I could link the two. My function to pull the company code is:
=nl("rows","G/L Entry","Global Dimension 2 Code","Fund No.","32039","Global Dimension 2 Code","*","Company=","CONS_MC","Global Dimension 2 Code","<>@@")
One that returns the description from a different table for the company codes my function is:
=nl("First","Dimension Value","Name","Code",$C3,"Company=","CONS_MC")
I was thinking i could link the two in a =nl(sheets to pull the description in the worksheet name but have been playing around with it and have not been able to make it work.
Thanks!
Tom
Date
Votes
8 comments
-
Jet Reports Historic Posts hi,
you can use the "=NL(Sheets)"-Function also with a range of cells.
So maybe there is a way to build rows with the company-code and the company-name, an build the sheet, depending on the excel cells.
f.e.:
b4=companycode"A" c4=companyname"A"
b5=companycode"B" c5=companyname"B"
b6=companycode"C" c6=companyname"C"
b7=companycode"D" c7=companyname"D"
and then
"=NL("Sheets";$C$4:$C$7)
Does that help?
regards
jetsetter -
Jet Reports Historic Posts Hey Jetsetter,
Thanks for the response.
I created an "nl rows" function that returned my list of office names. The =nl("sheets" formula does not seem to be working properly however.
What happens that it returns the first tab correctly but then no additional tabs are created.
Not sure where to go from here but I'll keep trying…
Thanks!
Tom -
Jet Reports Historic Posts Hi,
NL(Sheets) gets expanded before NL(Rows). So you can't make your NL(First) formula into an NL(Sheets) formula because when the NL(Sheets) is expanded, there would only be 1 row (which is why you see only 1 sheet created).
You could do something like this to pull the dimension value names as the sheet names and link the formula to the G/L Entry:=NL("Sheets","Dimension Value","Name","Code","<>''","Company=","CONS_MC","Link=","G/L Entry","Global Dimension 2 Code","=Code","Fund No.","32039")
Does something like that work for you?
Regards,
Hughes -
Jet Reports Historic Posts HI Hughes,
Thanks for getting back to me on this!
The formula worked perfectly.
I'll let you know if I come across any exceptions.
Tom -
Jet Reports Historic Posts Hi,
glad to found this post because I didn't know why it doesn't work => notice: sheets before rows :)
Is there any possibility - that I can't see at the moment - to us only a part of a company name for the sheets?
i.e. the companies are named
IT1001 Company1
IT1002 Company2
DE1003 Company3
But I want the sheets with IT1001, IT1002, DE1003.
I'm thankful for any hint. -
Jet Reports Historic Posts hi,
have you tried to work with the excel-formula (=LEFT(***;**))?
maybe that will work.. something like this: =LEFT(NL("Sheets";Table;Field;**whatever**);6)
i haven't tried it and i'm not sure, if it will work…so be carefull ;)
regards
jetsetter -
Jet Reports Historic Posts Hi jetsetter,
thanks for your input. I tried this before but unfortunately it doesn't work.
Best regards
cat -
Jet Reports Historic Posts hi,
i couldn't stop thinking about that and figured this out (VBA-Macro):
Before you have to formate your companyname in cell (eg) B4, like "=Left(B3;6) (in B3 is your NL;"Sheet"-Function)
Yu should run the macro after jet-refreshing is finished.
Sub NewName()
Sheets(1).Select
B = ActiveSheet.Index
For Each w In Worksheets
Sheets(B).Activate
n = [b4].Value
On Error Resume Next
w.Name = n
B = B + 1
Next w
Sheets(1).Select
End Sub
should work… ;)
pls give a feedback….
regards
jetsetter
Please sign in to leave a comment.