Data Validation List and Vlookup Combined

Hi Guys.. I have a similar problem. I have Names in one column and their role titles in 2nd. I want a formula to select Names in 1st column and it should return titles in 2nd column but If I select "Select by Role" in 1st column then it should give me drop down list of role titles in 2nd column. Can you help me with formula

Attach a sample workbook with some data and what you expect as a result.

Go Advanced -> Manage Attachments -> Upload

here it is

I have a list of Names and "Select by Role" in column A. If i select any name from Column A then it should give his title automatically and if i select Select By Role in column A then it should give me a drop down in Column B with all the available Roles.

You cannot reference other workbooks for Data Validation criteria ("Names").

Hi Guys. I dont have any macro work book. I need one to build a formula into this. I will reference the names and titles from another sheet in the same workbook. What I need is, If I select a particular name in column A from the drop down list then it should auto populate their corresponding titles in Column B. And if I select "Select By Role" from drop down in Column A then I should get an option in Column B as a drop down for the available titles. I currently just have created a drop down in column A with the names and "Select By Role".

Try re-upload your file with table that used by data validation

Here is the file with a separate tab that I used for Data Validation. Will this help.

I have posted the file with the new tab that I used as data validation. Also I could find the formula that would work but as soon as I select "Select by Role" and select a title in other column, I am unable to toggle back and select a Name in first column which will give me a title in 2nd column.
=IF((VLOOKUP(C2,'Names'!A:B,1,1))="SELECT BY ROLE", 'Names'!B:B, VLOOKUP(C2,'Names'!A:B,2,0))

What I need is, If I select a particular name in column A from the drop down list then it should auto populate their corresponding titles in Column B. And if I select "Select By Role" from drop down in Column A then I should get an option in Column B as a drop down for the available titles. I currently just have created a drop down in column A with the names and "Select By Role".

You will need VBA to do this.

I dont have any others selection to be made. I need like this. If I select a particular name in column A then I it should look up its title in column B. I select "Select By Role" in column A then I would be able to get a drop down of the roles of those available names in column A. There wont be any additional roles. It will be the same roles that I need.

Yes but what would be the code.

Hi John. Thank you so much for working out this for me. But I guess this is not exactly what I wanted. If I select by Name its giving me a corresponding role name but I also want Select By Role in the same drop down n column C and in the next column D I want all the available role names in the drop down. If I then select any role name in column D it should NOT give me all or any corresponding name for that role name in Column C. Column C should still be having "Select By Role" as selected. Is this achievable.

I am sure it is but I believe the solution provides what you want (your solution is not intuitive with regard to column headings).

Logically, you either want to know the the role(s) of a given person OR a list of people for a given role.

To me, nothing else makes sense!

If I then select any role name in column D it should NOT give me all or any corresponding name for that role name in Column C.

So why select a role if nothing is to result?

There is another process that I then need to undertake based on this selection. If I select a name in Column A it will give me a role in Column B and further it will do a vlookup to get their salary. If I dont know the name, I will select "Select By Role" in column A and then in column B I should be able to select from all the available roles and then based on this selection, I will taking an average of salaries for persons having the same role. The sample I gave is just for 5 people but I have a list of 10,000 people which I need to sort. If i get to this stage then I will be able to get further. Is this something that can be achieved.

What is the difference between selecting a role in column B or in column A?

Whether required or not the names are required, you have a list of ALL people who can perform that role. So if "System Engineers" is the selected role, then you get a list of people who are "System Engineers".

I will taking an average of salaries for persons having the same role

so having the name is no inconvenience as it does not impact the calculation of the average salary of a given role.

It appears to me that you making something relatively simple more difficult.

Irrespective of my view, ask a moderator to move this thread to the VBA/Macro thread and I suggest to post a file showing exactly how you want this to work.