Dependent List: select option or type text

[mazcarate]

I would like your help with a timesheet.
I followed this article to limit the choices in a drop-down list, so that it only shows items related to the selection in a previous cell. http://www.contextures.com/xlDataVal13.html
Now, I need to add 3 new columns. Filling out the first column affects the options shown in the second column. Then, the option of the second column affects the options shown in the third column and so on. My problem is that the first two columns should also accept values that are not in the drop-down list.
I'll try to explain myself.
The 1st column is called Project. It should have the option of selecting Admin or typing the project name.
The 2nd column is called Assignment Name.
• If the user selected Admin in the 1st column then it should display the Admin Name options.
• If the user DID NOT select Admin in the 1st column then it should be able to type the assignment name.
The 3rd column is called Assignment Duration.
• If the user selected an Admin Name option in the 2nd column then it should display the Admin Duration options.
• If the user DID NOT select an Admin Name option in the 2nd column then it should display the Assignment Duration options, regardless of what they type for Project Name and Assignment Name
The 4th column is called Assignment Type.
• If the user selected an Admin Duration option in the 3rd column then it should display Admin Type options.
• If the user DID NOT select an Admin Duration option in the 3rd column then it should display the Assignment Type options.
The 5th column is called Assignment Task.
• It should show only the options related to the selection of Assignment Type.
The formula I use was for Data Validation:
Assignment Type cells: =ValidationLists!D1:D7DEX(TypeColumn,MATCH(E60,TaskColumn,0)))
Assignment Task cells: =OFFSET(TypeStart,MATCH(D60,TypeColumn,0)-1,1,COUNTIF(TypeColumn,D60),1)
I’m trying to figure out is how to combine an IF NOT with the Data Validation because I don't what the names or how many for project name and assignment name will be. Is that even possible?

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[MarvinP]

Hi,

Your question sounds like it is a Cascading Dropdown List question. ??

http://www.bluepecantraining.com/por...ists-in-excel/
or
https://support.office.com/en-us/art...3-ACABACB76605

[mazcarate]

Simply Cascading list does not work. I'm trying to attached a file but the Go Advanced button is not letting me. The situation is that the user should be able to type or select. If the user typed an assignment name then it should show a drop-down list. If the user selected the Administration option for assignment name then it should display a different drop-down list.

[MarvinP]

Hi mazcarate,

Click on the "Insert Image" icon and select your file and then click just below that. See if that works for you to add an attachment/example.

[mazcarate]

Sample book with comments about how it should work
Dependent List.xlsx

[JohnTopley]

Where/what are Assignment names?

And "Project" is anything other than "Administration"?

[MarvinP]

I'm still trying to understand this problem...
You have lots of Named Ranges with many duplicates, that is bad.

Are you trying to use Validation Lists and have it NOT give an error if the word isn't in the list? Is that the main problem?

I think you also want to add a new word to a list if it isn't already there..??

Have you seen or heard of Dynamic Named Ranges, which will be part of your answer.

http://www.ozgrid.com/Excel/DynamicRanges.htm
http://www.contextures.com/xlNames01.html

Is VBA allowed to try to answer your question?

[mazcarate]

That is the problem. I don't have a list of Projects or Assignment names because they will be different for each user and they will be created as the year goes by.

As for the name ranges, they are leftover from other articles I was following. I forgot to delete them.

[JohnTopley]

We need examples of where Project/Assignments "fit": make up some dummy data.

[mazcarate]

Thank you very much for all your help.
Dependent List.xlsx

[mazcarate]

This is a better example of what I'm trying to do. My problem is that I don't know the name of the Projects or the Assignment Names. Each user should be able to input that information. I'm trying to make it easier to fill out and reduce user error that's why I would not want to give them access to the ValidationList worksheet.
Example cascading lists.xlsx

[mazcarate]

I was able to do it with a =IF(ISNUMBER(MATCH formula

I created two lists for the options and use this formula: =IF(ISNUMBER(MATCH(A6,ValidationLists!A:A,0)),listAssignmentAdmin,listAssignment)
If the A6 cell matches ValidationLists!A:A then if would should listAssignmentAdmin; if it does not match (0) then it shows listAssignment.