I have a purchase order template. When I run the report, I gives me multiple sheets based on the vendor category codes on the purchase lines. It works fine, but i want to add something else:
on the sheet replicator i have this formula: nl("sheets","Purchase Line","Vendor Category Code","Document No.",option!D5)
With this formula, the sheet name will be the "Vendor Category Code".
On one of the cell, I have the PO No.; and I want to add label to the PO no of each sheet.
So, for the first sheet, that cell will be: POXXXX A, the next one will be POXXXX B and so on.
How I could get that A, B, C things. I have tried using NL("sheets",{"A","B","C","D"}), but i get only the first value which is A.
Thank you.
Date
Votes
5 comments
-
Jet Reports Historic Posts Official comment Found the solution:
On a separate sheet ("ListSheet"), cell C3 = nl("rows","purchase header","vendor category code","no.",'option'!D5) which will give me the list of unique "vendor category code" from a specific PO header ('option'!D5 is the PO Number entered by the user).
and on cell D3 = char(row()+62). Since char(65) = A and so on, I added 62 on the formula which on row 3.
This will give me a list of alphabet A, B, C according to how many rows I have from the first formula on cell C3.
On the report sheet, on a hidden row, I use this formula to replicate the sheets: cell C1 = NL("sheets","purchase header","vendor category code","no.",'option'!D5).
and on the cell that has the PO Number, I use the following = 'option'!D5&vlookup(C1,'ListSheet'!$C$3:$D$100,2,false).
The result will be:
first sheet: VendorCategoryCode1 with cell for PO NUmber = PO123A
second sheet: VendorCategoryCode2 with cell for PO NUmber = PO123B
third sheet: VendorCategoryCode3 with cell for PO NUmber = PO123C
Thank you. -
Jet Reports Historic Posts I'm trying to understand what you are looking for. Does this give you the correct names?
=NL("Sheets","Purchase line","=NF(,""Document No."")&"" ""&NF(,""Vendor Category Code"")")
Caveat: the cell that you build your sheet names with will from now have the same content as the sheet name. if that cell is used in a later filter, it will no longer work. You may have to do a =LEFT or = RIGHT first to split the value. -
Jet Reports Historic Posts Maybe I did not explain it well, but that's not what I want.
I managed to get everything I needed, except for one small thing:
Let's say, the PO no. is PO1234, and the Vendor Category Code are: ABC, DEF, GHI.
For now, I have 3 sheets with the name: ABC, DEF, GHI. And on cell B2 for each sheet, I have the PO No. which is PO1234.
What I want is to have on cell B2 instead of only the PO1234, it will have PO1234 A for sheet 1 and PO1234 B for sheet 2, and PO1234 C for sheet 3 and so on.
Thanks. -
Jet Reports Historic Posts Can you please upload your report?
-
Jet Reports Historic Posts Here is the file.
I found a solution using VBA, but I want to know if there is a way to solve this using jetreports.
Thank you.
Please sign in to leave a comment.