Our business is structured where we have multiple locations in the US and we have our Hierarchy setup as West & East. We Use Dynamics GP 2010 and I’ve created a P&L report in Jet, and on my options tab I have a NL lookup formula that brings the option for the user to choose either All, West or East (Options from first table). But how do I create another NL Lookup on the second line that if the User chooses West from the first option, the Second NL Lookup will refer to the Second Table and list those locations as an option (WA100, OR100, ID100). And if the user chose All on the first option, it would populate all the locations as shown in the first line of Second Table.
SEE ATTACHMENT for Table Examples.
Date
Votes
4 comments
-
Jet Reports Historic Posts Official comment Okay I think I got it figured out. Try this formula in E6:
=NL("Lookup",NP("Split",VLOOKUP(D5,Hierarchy!B4:C11,2,FALSE),"|"),"Location")
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Can you attach an example report with the actual options sheet on it? That might be easier to modify. It's hard to construct an example of what you need to do with just "First Table" and "Second Table".
Regards,
Hughes -
Jet Reports Historic Posts Hopefully the attached example gives you a better explaination.
This Lookup I want to only have the options available based on the option picked in Cell D5. So in this case the user picked WEST in D5 then D6 should only allow the user to pick WA100 or WA300 or WA400. As you can see in this example I choose West in D5, but choose a location from the EAST in D6. -
Jet Reports Historic Posts Thank you that was exactly what I was trying to do. Thank you for your assistance. :D
Please sign in to leave a comment.