Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Change Data Validation List Based On Value In Cell

  1. #1
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12

    Change Data Validation List Based On Value In Cell

    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
    Attached Files Attached Files
    Last edited by DocAElstein; 10-02-2023 at 12:46 PM.

  2. #2
    Member
    Join Date
    Jun 2013
    Posts
    93
    Rep Power
    12
    I'm sorry, I did not understand your goal, can you attach your desired result ?

  3. #3
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12
    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
    Attached Files Attached Files

  4. #4
    Member
    Join Date
    Jul 2012
    Posts
    55
    Rep Power
    13
    Hi,

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

  5. #5
    Senior Member alansidman's Avatar
    Join Date
    Apr 2012
    Posts
    125
    Rep Power
    13

  6. #6
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12
    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
    Attached Files Attached Files

  7. #7
    Junior Member
    Join Date
    Aug 2013
    Posts
    18
    Rep Power
    0
    please find the code for updating the excel cells
    'copy paste the code in sheet1
    Code:
    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
    Code:
    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

  8. #8
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12
    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
    Attached Files Attached Files

  9. #9
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12
    bump

  10. #10
    Junior Member
    Join Date
    Aug 2013
    Posts
    18
    Rep Power
    0
    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.

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

    Code:
    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

Similar Threads

  1. Data Validation With Dynamic List Of Sheet Names
    By TomyLee in forum Excel Help
    Replies: 2
    Last Post: 08-19-2013, 02:40 AM
  2. Replies: 2
    Last Post: 07-28-2013, 09:29 AM
  3. Replies: 4
    Last Post: 07-27-2013, 01:34 PM
  4. How To Lock Or Protect Cell Using Data Validation
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-06-2013, 01:57 AM
  5. Replies: 1
    Last Post: 05-03-2013, 04:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •