NEED HELP creating a drop-down based on selection from first drop-down on dynamic list

Hi!

I have a file with 3 tabs - The first tab (template) has a drop-down based on the funds list from tab 2 (Funds) that has an external oledb connection and updates automatically when the file is opened.
My 3rd tab has several different IDs related to each fund in tab 2 and also has an external connection and updates automatically.

What I need and can't figure out how to do is in my main tab (template) create a drop-down based on the fund selected in the first drop-down that pulls the IDs that are related to that specific fund based on list on tab 3 (ID).

I'm attaching the file to make it easier to understand. I also wrote notes next to each tab to explain a little better.

I've set this up for you in the attached file. I've rearranged your data in the ID sheet by copying your IDs to different columns from row 2, and used the Legal Entity in row 1. I then set all these up as named ranges, using the entry in row 1 as the name (note, though, that names cannot contain spaces, so in Name Manager the spaces have been replaced by an underscore).

Then in B4 of the Template sheet I set up Data Validation based on a List, with this location:

=INDIRECT(SUBSTITUTE($A$4," ","_"))

Note the spaces are changed to underscores, the same as the named ranges. Now when you select a particular Legal Entity in A4, the values available in B4 will change.

Hope this helps.

Pete

Hi Pete,

Thanks for the response. The only thing is, is that I simplified the list.. There are 50+ different funds and growing and the IDs are also increasing. Plus I assume that if a new fund gets added to the main list it wouldn't create a new table for it, with the IDs below, like the ones you created, right?

What you say is correct - this was just to show you how it is done, using the file you provided.

Hope this helps.

Pete

I understand. Thank you for your help. But I need it to be dynamic and update automatically when new funds are added to the list. But I think that there's no way to do that. =/