Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Populate Data in Non-Adjacent Cells (From Dropdown)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Populate Data in Non-Adjacent Cells (From Dropdown)

    Hello all,

    I'm trying to figure out how to populate data in non-adjacent cells from information that is stored on another sheet. Sheet 1 is essentially setup to be visually pleasing to the user, but the data stored behind it is on another sheet stored in a more logical manner. I would like to auto-populate the nicely formatted sheet with information from sheet 2 when an item from a drop-down list is selected. I have found quite a few ways to do this using offset, etc. but in my case the values will be pulled into cells that are spread around the design sheet in no set order. Any guidance would be greatly appreciated.

    Thanks!

  • #2
    Re: Populate Data in Non-Adjacent Cells (From Dropdown)

    Got an example file?

    If i understand right:

    1. Use a sheet change event with a if (cell with dropdown changes only) then trigger a marco on sheet two which takes the values from sheet 2 and places them in sheet1.

    2. Use a Macro to cut and paste the values once the values have updated pre copying to sheet1.

    3. You could simply link the cells from sheet 1 to sheet 2 for example and then do then fancy stuff on sheet 2 when the drop down changes, away from the pretty user output sheet.
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Gurus: Feel free to comment and modify my code if you can see ways to improve it, make it simpler and faster. We are all here to learn.

    Users: Don't forget to leave thanks and feedback on the code or formula so your helper knows it worked.

    Thanks

    Comment


    • #3
      Re: Populate Data in Non-Adjacent Cells (From Dropdown)

      Thanks for the quick response! I haven't used excel in this way in quite some time, so I wasn't sure the most efficient manner to go about setting up the macros. I mocked up a quick example of the information that I'm talking about for you to take a look at. The data is associated by row in sheet 2, but as you can see on sheet 1 the cells are not aligned in a linear fashion. Most of the googling I did for trying to figure this out kept turning out information on VLOOKUP, but it doesn't look like that will work in this situation. Even if you could get me pointed in the right direction it would be much appreciated. Thanks again.
      Attached Files

      Comment


      • #4
        Re: Populate Data in Non-Adjacent Cells (From Dropdown)

        Hi sorry for the delay. Something to get you started.

        Paste this in Sheet1 in VBA excel

        Code:
        Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
            If Target.Address = "$B$1" Then
               
            On Error Resume Next
                Application.EnableEvents = False
                    Target = Target * 2
                    Grab_Data
                Application.EnableEvents = True
            End If
            
        End Sub
        This is the main COde to go in a module.


        Code:
        Sub Grab_Data()
        Application.ScreenUpdating = False
        'VBA_Wizard
        '27 March 2013
        'Version 1
        'Some Starting Code, grabs the data from a table based on an input
        'from a drop down list and inputs the variables associated with that list to
        'Sheet1
        
            Dim Software As String
            Dim a, i As Double
            
            Software = Worksheets("Sheet1").Range("Software").Value 'Grab Name from dropdown - Define name "Software"
            
            With Worksheets("Sheet2").Range("a1").CurrentRegion 'Grabs the table on SHeet2
                a = .Value: 'sends data to array a
                
                With CreateObject("Scripting.Dictionary") 'reference to array
                            
                    For i = 2 To UBound(Application.Transpose(a), 2) 'gets the length of the table starts at row 2
                        If InStr(1, UCase(a(i, 1)), UCase(Software)) > 0 Then 'looks for a match
                         
                        'what you want to do with found data              'Don't forget to define the names for each cell where the Values are to go.
                                  
                         Worksheets("Sheet1").Range("P").Value = a(i, 2) 'Price
                         Worksheets("Sheet1").Range("PD").Value = a(i, 3) 'Procurment Date
                         Worksheets("Sheet1").Range("SD").Value = a(i, 4) 'Sunset Date
                         Worksheets("Sheet1").Range("AG").Value = a(i, 5) 'Approved Group
                         Worksheets("Sheet1").Range("TC").Value = a(i, 6) 'Technical Contact
                         
                        'what you want to do with found data
                        
                        Else
                        End If
                    Next
                End With
            End With
            
        Application.ScreenUpdating = True
        End Sub

        File attached
        Attached Files
        -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        Gurus: Feel free to comment and modify my code if you can see ways to improve it, make it simpler and faster. We are all here to learn.

        Users: Don't forget to leave thanks and feedback on the code or formula so your helper knows it worked.

        Thanks

        Comment

        Trending

        Collapse

        There are no results that meet this criteria.

        Working...
        X