Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Is it possible for a two way List box for data validation?

    Hi Excel Experts,

    I am tracking the productivity of my employees and i have prepare a report for the same. with the list under data validation with the list of employees. I have also created graphs for the report, so each time i select the employee the report changes and so does the chart.

    However, the charts are on a different sheet, so each time i need to check the graph for another employee i need to go back to the report sheet and change the name and return to the graphs sheet.

    Is it possible for me to have a drop down list in the sheet with the graphs and when i select the name the reports change and so does the graph and vice versa. Basically a drop down list that can work two ways.

    Is this possible?

    Regards
    Baiju

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Yes - you would use code (Worksheet_Change events) to assign the selected value from one sheet to the other sheet.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Rory,

    As you suggesting that i write a macro. Im not good at macros.

    I am attaching a excel file with two sheets one has the report the other the graph. I also have a drop down in both the sheet. can you help me with this.

    Thanks
    Baiju
    Attached Files Attached Files

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    See attached. There are change events in the chart and report sheets:

    Chart sheet:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       On Error Resume Next
       If Not Intersect(Target, Range("A1")) Is Nothing Then
          ' disable events to avoid infinite loop
          Application.EnableEvents = False
          Sheets("Report").Range("A1").Value = Me.Range("A1").Value
          Application.EnableEvents = True
       End If
    End Sub
    Report sheet:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
       On Error Resume Next
       If Not Intersect(Target, Range("A1")) Is Nothing Then
          ' disable events to avoid infinite loop
          Application.EnableEvents = False
          Sheets("Chart").Range("A1").Value = Me.Range("A1").Value
          Application.EnableEvents = True
       End If
    End Sub
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    2 Star Lounger
    Join Date
    Jul 2006
    Location
    Bangalore, India
    Posts
    180
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Rory.. this is great you have made my job easy

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you create the list for selecting the employee with a FORMS - combobox instead of validation, you can add one to the chart as well as the sheet and not use code at all and have them linked to the same cell so each will change the other...

    Steve

Posting Permissions

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