VLOOKUP that can insert dropdown list

Hi,

I can't seem to figure out how to generate a drop down list with V lookup.

My situation is like this.:

In sheet1 I have three colums: Projectcode, Project, Location
The projectcodes generally correspond with only 1 project and 1 location, so I've created an array in sheet2 with the projectcodes and their corresponding projects and locations.
Like this, I can enter a projectcode (say in A1) in sheet1 and with VLOOKUP the corresponding project (in B1) and location (in C1) are automatically entered. This works perfectly.

Now here comes the problem, there are a few projectcodes (5 out of 25) that actually cover 2 or more locations. So instead of 1 value in sheet1 C1, I would like a dropdown list in C1, based on the array I made in Sheet2.

In sheet2 I made the following array:
The first column lists all the projectcodes
The second column lists the projects
The third column is a list of single values for location or dropdown lists of locations based on column 4-15. I have created the dropdown lists manually with data validation - list - range:column 4-15
Colums 4-15 represent all the possible locations, when a project code corresponds with the location (of one particular column), the value in that column is the name of the location. If the project code does not correspond with the location the cell is left empty.

Since I am only using 25 projectcodes, it is not a problem to create the dropdown list manually in sheet2.
But in Sheet1, I will likely need over a thousand entries, so I need a uniform formula that returns single values for a location (20 out of 25 cases so), but also dropdown lists if needed (those 5 out of 25).

Sorry to not have an example posted, I hope it is clear anyway.
Can anyone guide me?

Charlotte88,
See if the attached sample sheet holds the answer you are seeking. There are two tabs in the workbook.
The lists are on one tab - the drop down on another.
Select a project code from the drop down, and the project and location appear in the next two column via VLOOKUP formula

Dear ufopilot,

Thank you for your reply. Maybe I wasn't clear in my explanation, but the example you sent me is what I described as my current situation, I already have this in my excel.
What I'm looking for is a way to create a dropdown list for location of say, projectcode 2230. Suppose project code 2230 covers two areas, Chicago and New York and it depends on the user whether the new entry is for Chicago or New York. I want my user (who will only have access to sheet2) to be able to select from a dropdown list if his specific entry covers either Chicago or New York.

At the same time, for projectcode 2231, I just want Vegas to pop up, so here I don't need a dropdown list. I am looking for a way to capture both the 2230 and 2231 in one formula.

Could you help me with this?

I am sorry, I mean sheet3 (so the second sheet) instead of sheet2 .

Charlotte88,
I hope I understood this correctly,
I have edited the workbook, to give up tp 7 locations and 7 projects to any project code.
This list can be extended. I have read your reply again, but have the feeling I have missed the point.
Nevertheless, I will get you what I have, and perhaps this will help in going forward for what you need.
A sample sheet with what you have, and what it should look like when you are done would be helpful.
I work better with the before/after samples

I am leaving for the night, but will try to get back if you have anything new posted that I may be able to help with.
You know there is a solution out there - you may even develop it yourself - don't give up

Dear ufopilot,

Thank you for your reply. I think I understand your confusion, I'm sorry, I think I should have added the context of my question.
What I am making in excel is an index for 750GB of existing documents. Each document belongs to a certain projectcode-project-location and the index will also contain information on the type of document, user, client etc.

In the index users can use sheet1 to enter all the different documents with a hyperlink. 1 hyperlink per row. Per row more information will be added on that document, like projectcode-project-location-user etc.

Now to get back to the example you send me. Your sheet 3 is exactly what I need, but I need it compressed into 1 row. So when I user enters the projectcode of a project with 2 possible locations, a dropdownlist appears in the row instead of two line underneath each other.

Does this make more sense?

Charlotte88
I don't know how to make a dropdown list appear when a new row is started, but I think I have most of the rest figured out
I had to insert certain helper columns ( cells in pink ) which can be autofilled as needed (this can most likely also be done with a macro - code)

There are some very smart people on this forum who can most likely get what you need. I am still learning about codes, and would not want to lead you in the wrong direction there.
Hope this helps - I'm sure it can be improved on also. You can Hide the pink cells/columns