Data validation bug

Hi all,
I've seen a couple posts that have pointed me in the right direction for a solution for Data Validation across Excel 2007-2010 however I haven't been able to get any of them to work for me.

I am trying to create a spreadsheet that a user enters some variables (Format Name) and then take the user entered values, make it unique, and then create a drop down list from the unique list. I have the spreadsheet doing just that, however the first time someone opens the spreadsheet they have to go to Data -> Data Validation and while having the final list's cell selected to get the list to display correctly. I've tried moving the list around to the same spreadsheet but I just ran into similar problems.

I've basically tried this: http://superuser.com/questions/59849...and-excel-2010 as a solution.
Any help would be apprecaited.

For sure (I re-read my post and I agree its vague).
So, in the 'A' column of the formats tab, id like a user to be able to enter any value. Any value from this column is placed into the Name Manager with this formula : =OFFSET(Formats!$A$2,0,0,COUNT(IF(Formats!$A$2:$A$999="","",1)),1) with the intent of removing any duplicate (i.e A,A,B,B would become A,B in the new list). Then in the 'Variables' tab, under Column F (Code_Type) I am attempting to make a drop down list using only the unique values from the list I just created. Here is where I run into the problem though. From my understanding, because I am referencing a column outside of the same tab, the Data Validation doesn't work. If I were to open this spreadsheet and click on Code_Type, only the first value from my list will appear. If I go into Data -> Data validation and simply hit Ok (without changing any options) then my entire list will appear. Basically the list will not populate unless I press the data validation button.

This spreadsheet needs to be shared with peers, so I am hoping to make it so that the list simply works and that no one else needs to click data validation for it to work.

Small example: If a user enters A,B,B,C,D,1,1,E into Format_Name, then in the Variables tab under Code_Type, id like the drop down list to display A,B,C,D,1,E.

Any help would be appreciated. Does this issue only occur if the formulas are in different tabs?

Hi G3th,

It looks like you already created a dynamic named range, so I think all that you need to do is put "=List" in the data validation source box instead of the formula.

Thanks,
DMG

If I put "=List" it will pull the information sans where I made it unique. This does put me in the right direction though and i'm sure i can figure out the rest from here. Thanks for the reply