VLOOKUP & Dynamic drop down lists

P

PNY80

New Member
Joined
Jun 7, 2013
Messages
20
Hello all,

After searching like crazy, I am stuck as to how to create a dynamic drop down list using VLOOKUP.

In one workbook:
  • I have an input worksheet where employees enter their data and the data gets transferred to a master-list via button (macro). Every employee row in the master-list has their manager's ID "linked" (via an org list and VLOOKUP)
  • As a manager, there is a separate manager worksheet that a) validates the manager's ID and b) builds the drop down list of their team only (this is where I get into some trouble).

Is there a way to build the drop down list based on the managers ID, matched to the data that is in the master-list?

Please help!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
PNY80 said:
Hello all,

After searching like crazy, I am stuck as to how to create a dynamic drop down list using VLOOKUP.

In one workbook:
  • I have an input worksheet where employees enter their data and the data gets transferred to a master-list via button (macro). Every employee row in the master-list has their manager's ID "linked" (via an org list and VLOOKUP)
  • As a manager, there is a separate manager worksheet that a) validates the manager's ID and b) builds the drop down list of their team only (this is where I get into some trouble).

Is there a way to build the drop down list based on the managers ID, matched to the data that is in the master-list?

Please help!
Click to expand...


Hi, I am from Cambodia.
I will give you the Example how to do this.
please follow my step.
1-please copy my data click Cell A1 and paste it
[TABLE="width: 271"]
<tbody>[TR]
[TD]Manager-ID[/TD]
[TD]B1[/TD]
[TD]B2[/TD]
[TD]B3[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]B[/TD]
[TD]F[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD]C[/TD]
[TD]G[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D[/TD]
[TD]H[/TD]
[TD]L[/TD]
[/TR]
</tbody>[/TABLE]
2- Name the B Column (B1:B1048579) as B1Col, Name C Column as B2Col, Name D Column as B3Col.
3- Name B2 as "B1_", C2 as "B2_", and D2 as "B3_"
3- Copy the following in to E1 and F1
[TABLE="width: 143"]
<tbody>[TR]
[TD="width: 79"]Manager-ID[/TD]
[TD="width: 64"]Member
[/TD]
[/TR]
</tbody>[/TABLE]
4- Select Cell E2 then go to Data => Data Validation (Select "List") and pass "=offset($A$1,1,0,counta($A:$A)-1,1)" to the source: then click "ok" (After click "Ok", please select any the Manager ID in Cell E2)
5- Select Cell F2 then go to Data => Data Validation (Select "List") and pass "=OFFSET(INDIRECT(SUBSTITUTE($E2," ","")),1,0,COUNTA(INDIRECT(SUBSTITUTE($E2," ","")&"_Col")),1)" to the source: then click "ok"

Enjoy it!
 
Last edited:
Upvote 0
sophea_tum said:
Hi, I am from Cambodia.
I will give you the Example how to do this.
please follow my step.
1-please copy my data click Cell A1 and paste it
[TABLE="width: 271"]
<tbody>[TR]
[TD]Manager-ID[/TD]
[TD]B1[/TD]
[TD]B2[/TD]
[TD]B3[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]B[/TD]
[TD]F[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD]C[/TD]
[TD]G[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D[/TD]
[TD]H[/TD]
[TD]L[/TD]
[/TR]
</tbody>[/TABLE]
2- Name the B Column (B1:B1048579) as B1Col, Name C Column as B2Col, Name D Column as B3Col.
3- Name B2 as "B1_", C2 as "B2_", and D2 as "B3_"
3- Copy the following in to E1 and F1
[TABLE="width: 143"]
<tbody>[TR]
[TD="width: 79"]Manager-ID[/TD]
[TD="width: 64"]Member[/TD]
[/TR]
</tbody>[/TABLE]
4- Select Cell E2 then go to Data => Data Validation (Select "List") and paste "=offset($A$1,1,0,counta($A:$A)-1,1)" to the source: then click "ok" (After click "Ok", please select any the Manager ID in Cell E2)
5- Select Cell F2 then go to Data => Data Validation (Select "List") and paste "=OFFSET(INDIRECT(SUBSTITUTE($E2," ","")),1,0,COUNTA(INDIRECT(SUBSTITUTE($E2," ","")&"_Col")),1)" to the source: then click "ok"

Enjoy it!
Click to expand...


Hi,

please ignore the all of above steps. some mistake are found. so please follow in the following:

1-please copy my data click Cell A1 and paste it
[TABLE="class: cms_table, width: 271"]
<tbody>[TR]
[TD]Manager-ID[/TD]
[TD]B1[/TD]
[TD]B2[/TD]
[TD]B3[/TD]
[/TR]
[TR]
[TD]B1[/TD]
[TD]A[/TD]
[TD]E[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]B2[/TD]
[TD]B[/TD]
[TD]F[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]B3[/TD]
[TD]C[/TD]
[TD]G[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]D[/TD]
[TD]H[/TD]
[TD]L[/TD]
[/TR]
</tbody>[/TABLE]

2- Name the B Column (B1:B1048579) as "B1_Col", Name C Column as "B2_Col", Name D Column as "B3_Col".
3- Name B2 as "B1_", C2 as "B2_", and D2 as "B3_"
4- Copy the following in to E1 and F1
[TABLE="class: cms_table, width: 143"]
<tbody>[TR]
[TD="width: 79"]Manager-ID[/TD]
[TD="width: 64"]Member[/TD]
[/TR]
</tbody>[/TABLE]

5- Select Cell E2 then go to Data => Data Validation (Select "List") and paste "=offset($A$1,1,0,counta($A:$A)-1,1)" to the source: then click "ok" (After click "Ok", please select any the Manager ID in Cell E2)
6- Select Cell F2 then go to Data => Data Validation (Select "List") and paste "=OFFSET(INDIRECT(SUBSTITUTE($E2&"_"," ","")),0,0,COUNTA(INDIRECT(SUBSTITUTE($E2&"_"," ","")&"Col"))-1,1)" to the source: then click "ok"

Enjoy it!
 
Last edited:
Upvote 0
Hi sophea_tum,

Thanks for the reply, unfortunately it does not work as expected.

In the manager's tab, the drop down should show all team members for that manager only. Because of the org, the same manager may be listed more than once, so when the manager's ID is identified, their team's names will be listed in the drop down.

Please advise!
 
Upvote 0
PNY80 said:
Hi sophea_tum,

Thanks for the reply, unfortunately it does not work as expected.

In the manager's tab, the drop down should show all team members for that manager only. Because of the org, the same manager may be listed more than once, so when the manager's ID is identified, their team's names will be listed in the drop down.

Please advise!
Click to expand...

Hi,

I am a bit difficult for your deeply task.
the sample that i have show means that: There are 3 Managers-ID (B1, B2, and B3)
Manger B1 has there member name like (A,B,C,D) or you can add more member
Manger B2 has there member name like (E,F,G,H) or you can add more member
Manger B3 has there member name like (I,J,K,L) or you can add more member

at the cell E2 you can select Manager B1,B2, or B3, then its member will able to be selected (Cell F2) according to the Manager selected in Cell E2.
Ex: Cell E2 you select B2, then in Cell F2 there are only E,F,G and H to be selected. if you choose B3 from cell E2 then Cell F2 show only I,J,K and L to be selected.

if you don't mind, you can send that file to my mail than i can have a look.
Thanks.
 
Upvote 0
Hi sophea_tum,

Here is a sample of what I am trying to accomplish:

data sheet (hidden):
[TABLE="width: 394"]
<tbody>[TR]
[TD]manager name[/TD]
[TD]employee name[/TD]
[TD]data1[/TD]
[TD]data2[/TD]
[TD]data3[/TD]
[TD]data4[/TD]
[/TR]
[TR]
[TD]manager1[/TD]
[TD]a[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager1[/TD]
[TD]b[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager2[/TD]
[TD]c[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager2[/TD]
[TD]d[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager1[/TD]
[TD]e[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager3[/TD]
[TD]f[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager1[/TD]
[TD]g[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager2[/TD]
[TD]h[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager3[/TD]
[TD]i[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
</tbody>[/TABLE]

manager sheet:
If I am manager 2, my drop down should show only employees c, d, h

Please let me know if I can provide additional clarification.
 
Upvote 0
PNY80 said:
Hi sophea_tum,

Here is a sample of what I am trying to accomplish:

data sheet (hidden):
[TABLE="width: 394"]
<tbody>[TR]
[TD]manager name[/TD]
[TD]employee name[/TD]
[TD]data1[/TD]
[TD]data2[/TD]
[TD]data3[/TD]
[TD]data4[/TD]
[/TR]
[TR]
[TD]manager1[/TD]
[TD]a[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager1[/TD]
[TD]b[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager2[/TD]
[TD]c[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager2[/TD]
[TD]d[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager1[/TD]
[TD]e[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager3[/TD]
[TD]f[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager1[/TD]
[TD]g[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager2[/TD]
[TD]h[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]manager3[/TD]
[TD]i[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[TD]test[/TD]
[/TR]
</tbody>[/TABLE]

manager sheet:
If I am manager 2, my drop down should show only employees c, d, h

Please let me know if I can provide additional clarification.
Click to expand...


Hi,

Sorry for late respond because I am too much busy.

please check the private inbox i have drop you the link for download as the sample.
please do it row by row.

thanks.
 
Upvote 0
PNY80 said:
Is there a way to build the drop down list based on the managers ID, matched to the data that is in the master-list?

Please help!
Click to expand...

Take a look at this, seems to do what you want.

Select a manager from drop down in D1 and then see the employees in drop down in E1.

Although, with the change event macro, I cannot figure how to have your Managers and emplyees on a seperate sheet and the drop downs on another.

Maybe one of the pros will look in on it, if this is what you want.

https://www.dropbox.com/s/184li21v836ausl/Drop Down VBA list maker DBox.xlsm?dl=0

Howard
 
Upvote 0
Hi Howard and Sophea,

Thanks very much for your replies and help! I was able to solve via a different forum.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Drop-down list (dynamic content)
Replies
0
Views
352
clandestino800
C
FrumpyJones
  • Question Question
Is there a way to stop COPY/PASTE but still allow manual entries and use of drop down lists.
Replies
5
Views
146
FrumpyJones
FrumpyJones
P
  • Question Question
How to create a multiple drop-down lists in Excel using Developer?
2 3
Replies
29
Views
395
PetarTen
P
J
  • Question Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
635
juliecooper255
J
M
  • Question Question
VBA vs Formulas/Data Validation for Multiple Dependent Dropdown Lists
Replies
3
Views
1K
MissDB101
M

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top