Change Data Validation List Based On Value In Cell

[jeff]

When selecting a Leader it places the members in the Range C5:C10.....is there a way to have dropdown lists (DV) in the Range C5:C10 of the members names for each Leader....this way i can select the same name in the Range C5:C10 as many times as i need

Maybe there is a better way also to accomplish this

Thanks

Jeff

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[patel]

I'm sorry, I did not understand your goal, can you attach your desired result ?

[jeff]

Please see attached....in cell B5 & B13 when i select a Leaders name i want his Members to be available in range C5:C10 & C13:C18......if i change the leaders name i then want his members to be visible with a Dropdown List of all the members belonging to the Leader....So if i change from Chris (B5) to Dave (B5) i want Daves members to be visible with the dropdown list of members for Dave

Hope this helps

[Ingolf]

Hi,

You can use INDIRECT function in Data Validation. See attached.

[alansidman]

Look at post 2 in this thread; http://www.excelfox.com/forum/f2/excel-selection-of-data-based-on-the-data-in-another-cell-1502/

[jeff]

sorry for late responce

This example works fine but is it possible that when a Leader is selected it also places his members in the range with the DV list of his members also for that Leader....currently when a Leader is selected it places his members in the DV list but does not change the names in the range from the previous leader

Thanks

Jeff

[aju.thomas]

please find the code for updating the excel cells
'copy paste the code in sheet1



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$5" Then
Update
End If
End Sub


insert module and paste the code in module


Option Explicit

Sub Update()
Dim i As Integer
Dim c As Range
Set c = Range("c5")
For i = 1 To 6
Range("c" & i + 4).Value = Range(Mid(c.Validation.Formula1, 2)).Cells(1, i).Value
Next i
End Sub

[jeff]

very good aju

What about if i want to add B13 and members to the code?

Can the ranges where the Data Validations are situated be protected so that only the Data Validation lists are accessible to be changed...currently the names can be deleted

Thanks again

Jeff

[jeff]

bump

[aju.thomas]

Apologies for late response
if you are planing to add additional members, you will have to include rows in the templete ("sheet1") and Lists sheet.
you will have to change the Name reference and Target.address.



Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$5" Or Target.Address = "$B$13" Then
Update
End If
End Sub





Option Explicit

Sub Update()
Dim i, r_cnt, cnt As Integer
Dim c As Range
Set c = Range(ActiveCell.Offset(0, 1).Address)
r_cnt = ActiveCell.Row - 1
cnt = Range(Mid(c.Validation.Formula1, 2)).CountLarge
For i = 1 To cnt
Range("c" & i + r_cnt).Value = Range(Mid(c.Validation.Formula1, 2)).Cells(1, i).Value
Next i
End Sub

[jeff]

Cheers Aju

I attached workbook.In the workbook i now want the groups to go across the sheet instead of down the sheet.I have empty cells grouped where i will go across the sheet.They are 5 x 2 groups..i need help please altering the code in module 1.I can fix the sheet code

Thank you

JEFF

[jeff]

I got it to work
Thanks
Jeff

[Ingolf]

Jeff,

Selecting a leader in column B and in adjacent column appear members of that leader, then whay is needed validation for members? (in column C)

[jeff]

Ingolf

It allows me to use those names in any order or multiple times......

I have changed Reg`s name to Steve....it gives a error in code.....i can fix this error by changing the name in the Name Manager and sheet "Lists" which fixes the problem....is it possible to give a Messagebox warning saying the name needs changing in the Name Manager and sheet "Lists" instead of bringing up a error
Or maybe there is another solution
Regards

Jeff

[jeff]

bump