hi,
i have a database of 4 companies. Say each company has its own set of customer posting group codes so i would like to combine all customoer posting group codes into an excel, having each code in a worksheet. If there are 20 codes across 4 companies, i would have 20 sheets generated. How can i acheive this?
I was able to populate 20 codes into a worksheet with NL("Rows"). however when i wanted to change to NL("Sheets") it just showed me 1 sheet.
Experts please advise
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Hi Hughes,
fully understood.
Thanks for the advise.
LH -
Jet Reports Historic Posts Hi,
I think you could do this with an NP(Union) function like this:=NL("Sheets",NP("Union",NL("Filter","Customer Posting Group","Code","Company=","Company1"),NL("Filter","Customer Posting Group","Code","Company=","Company2"),NL("Filter","Customer Posting Group","Code","Company=","Company3"),NL("Filter","Customer Posting Group","Code","Company=","Company4")))
You could split out the 4 NL(Filter) functions into separate cells (although you CAN'T split out the NP(Union) function from the NL(Sheets)). Does that help?
Regards,
Hughes -
Jet Reports Historic Posts Hi Hughes,
your formula works absolutely fine if i have 4 fixed companies. However this database my add new companies in future. I would like it to be more dynamic to combine all customer group codes for N companies.
LH -
Jet Reports Historic Posts Hi LH,
It is sort of possible to do this dynamically with NL(Rows) although even that solution is not very optimal, but in the context of NL(Sheets) it's definitely not possible. NL(Sheets) gets expanded before NL(Rows) or NL(Columns). Thus we can't use NL(Rows) to make a big list of the customer posting groups and then use NP(Union) to combine that list and reference it with NL(Sheets). The sheets would get expanded before the rows, so this wouldn't work. I'm afraid for now you're stuck with a static list of companies.
Regards,
Hughes
Please sign in to leave a comment.