Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Populate Data in Non-Adjacent Cells (From Dropdown)

  1. #1
    Join Date
    27th March 2013
    Posts
    2

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th March 2013
    Posts
    127

    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

  3. #3
    Join Date
    27th March 2013
    Posts
    2

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th March 2013
    Posts
    127

    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

    VB:
    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.


    VB:
    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 1
    Last Post: May 14th, 2011, 07:51
  2. Populate Cells From Dropdown List
    By irfannaseem in forum EXCEL HELP
    Replies: 1
    Last Post: February 18th, 2007, 14:37
  3. Populate data to a dropdown list.
    By lcpx in forum Excel and/or SQL Help
    Replies: 3
    Last Post: February 25th, 2006, 04:08
  4. Replies: 4
    Last Post: September 7th, 2005, 06:13
  5. Replies: 2
    Last Post: August 4th, 2004, 01:14

Bookmarks

Posting Permissions

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