Hi, 

Yes, this is called a dynamic data validation.  Check out this link dynamic data validation ad drop down list  which is a tip within the Sage Intelligence website.  Sage City should also have the tip.  

There is also one that is a bit more advanced here.

Hope this helps. 

Thanks, Sandra.  I know how to create the dropdown list.  My question is how to use the drop down list to affect the division being selected.  Each column (department) is using a reporting tree based on the department.  But I also need to specify the reporting tree based on the division selected.  I can't have two reporting trees in one formula, can i?

In my example above, if I select division 01 (Irvine) I need all the GL accounts for my Irvine division for my specified department.
If I choose division 02 - my GL account would need to change to 76000-02.

Oh, sorry, I didn't finish through the question. If I understand correctly what you need is the data validation list above that includes all your trees but taking it further by including a VLOOKUP formula.  This video i includes how to do that.  It demonstrates how you can drag each of your tree segments into a separate list, name them, add them as the validation drop down then use VLOOKUP that will work with your Report Trees field within the Sage Intelligence formula. Here is the video:  Take Advantage of Reporting Trees The X-Factor report writer series Start at the point of the video around 9:45.   .  Probably additional helpful info as well. 

I believe that was my option 1 in the original post.  I was hoping for an easier way to do that, but perhaps there is not.  I'll watch the video and thank you for the link!

Yes it is but maybe someone else can reply with a better suggestion.  There are many options with Excel!

Yes it is but maybe someone else can reply with a better suggestion.  There are many options with Excel!