Dynamic Dropdowns

Can anyone help.

I need to create a dynamic drop-down list


I have a sheet with codes for every project:
Code.PNG

And the main table that have to be populated, so I have created a list with all projects with Data-validation and based on the project I have created another list with unique codes for every project
so I first select the project from a DropDown and based on the project code a can select only the codes of that project.DataEntry.PNG

I know how to do that with indirect function but if a want to ad a new project with new codes, this is where I don't know what to do.

DropDownsample.xlsx

See the attached: row 2 of Data Entry.

Unfortunately the Project Names/Project codes are "reserved" names so you cannot have a named range called "A01" for example. I added a suffix of "_x" in the name manager and as a consequence the INIRECT in the corresponding data validation also was modified.

Hope this sufficient to get you started.

Thanks John,

i Have use the indirect and the substitute function, but when i enter a new project with its codes, i have to redo the data validation from the beginning and some times it doesn't work
i have read this article about dynamic drop down http://chandoo.org/wp/2014/02/13/dyn...ns-that-reset/ , but It does't work in my case.

You need a dynamic ranges in your named ranges.

For "Project" set the "Refers to" in Data Validation to

=OFFSET(Code!$A$2,0,0,COUNTA(Code!$A:$A)-1,1)

If now add other projects they will show in the list.

yes, this is true for every new project but it will not affect the code that refers to the new project, the drop down on the column F, want work

Yes it will. I renamed the projects XXA0 .... so that the INDIRECT references were to A2 (etc) and added a new project XXD05 with a new corresponding list project codes. That worked fine. I just need the dynamic list to ensure that XXD05 appeared in Column A. When selected in had the right project codes in F.