VBA Help? List Generation

[ineedhelp37]

Hi,

I am trying to figure out a way to create a list in one sheet depending on the value of cells in another. I have in "Master List" sheet Column A the names of projects, and in that same sheet in columns I:L I have names of people involved. What I want to do is in another sheet (called "Bandwidth"), if I type up a name in cell M3, I want a list of projects that person is working to fill up (as many projects as that person has) in I11:I30. I tried using formulas, but I think I need to VBA which I am only somewhat familiar with.

Thanks!

 

[JoeMo]

This is code for the sheet Bandwidth. Assumes data on Master List begins in A1 (can be a header). Install the code as follows:
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R As Range, Vin As Variant, Vout As Variant, i As Long, j As Long, ct As Long
Set Target = Target(1)
If Not Intersect(Range("M3"), Target) Is Nothing Then
    Application.EnableEvents = False
    Set R = Range("I11:I30")
    R.ClearContents
    With Sheets("Master List").Range("I1:L" & Sheets("Master List").Cells(Rows.Count, 1).End(xlUp).Row)
        Vin = .Value
        ReDim Vout(1 To UBound(Vin, 1))
        For i = 1 To UBound(Vin, 1)
            For j = 1 To UBound(Vin, 2)
                If Vin(i, j) = Target.Value Then
                    ct = ct + 1
                    Vout(ct) = Sheets("Master List").Range("A" & i).Value
                End If
            Next j
        Next i
    End With
    If ct > 0 Then
        ReDim Preserve Vout(1 To ct)
        R.Resize(ct).Value = Application.Transpose(Vout)
    End If
    Application.EnableEvents = True
End If
End Sub

Whenever a change is made to the name in cell M3 on the sheet Bandwidth, the code will run and list out the projects the named person is working on.

 

[ineedhelp37]

Hi!

Thank you so much for a quick reply. This works great, except it returns the data for the row above the name. Is there a way to change that?

Many Thanks!

 

[JoeMo]

Hi!

Thank you so much for a quick reply. This works great, except it returns the data for the row above the name. Is there a way to change that?

Many Thanks!

I can change that, but you need to post your layouts for both the Master list and Bandwidth sheets so I can see where the names are. Using the description from your initial post as best I can understand it, the code I posted works perfectly for me.

 

[ineedhelp37]

Thank you!! Below are the layouts.
The other problem is that when one name is repeated in the same row in I:L, it repeats the name of the project. Is there a way to make it so that it only prints the name of the project once?

Bandwidth

[/URL][/IMG]

Master List

 

[JoeMo]

Posting images is not very helpful, especially for the Master List which is showing nothing on my browser. Additionally, the information on the Bandwidth sheet is quite different than what I envisioned from your initial post. Below are two links that provide free means to post layouts that can be copied/pasted from a browser window. I would suggest using one of these to post the layouts of both sheets along with a clear description of what you want to accomplish.

Excel Jeanie link: Download
MrExcel HTML Maker link: http://www.mrexcel.com/forum/2545970-post2.html

 

[ineedhelp37]

Sorry about that, I am quite new at this. I am trying to accomplish the following: If I write a name in M3 of "Bandwidth", and if it matches the name in any of the columns I:L in "Master List", I would like the corresponding Project name in Column A in "Master List" to be pasted in column I of "Bandwidth" (after row 14). However, if the name entered in M3 of "Bandwidth" matches more than one column in a specific row of "Master List", I would like the Project name to be posted only once in column I in "Bandwidth". If possible, it would be great to also paste the corresponding entries also for column B:D (SBU, Mandate, and Tier) in "Master List" to Column J, M, and N in "Bandwidth". Below are the layouts, thank you again for helping and teaching!

Master List:

Excel 2010ABCDEFGHIJKL1ProjectSBUMandateTierComments on ProcessTasks/Action Items Uploaded to Jive Calander?8/3/2015 13:25Responsible PartyTeam LeadSupportSupport28/3/2015 13:24[CENTER][COLOR=#161120][B]Master List[/B][/COLOR][/CENTER]

Bandwidth:

Excel 2010ABCDEFGHIJKLMNO2Person Overview3NameINPUT NAME4Responsible Party on 0Projects5Team Leader on0Projects6Support on0Projects7Total Projects1Project8Total CompletedProjects9Total DeadProjects10TOTAL ACTIVEProjects1112Projects13Project NameSBUMandateTier1415161718192021222324252627282930[CENTER][COLOR=#161120][B]Bandwidth[/B][/COLOR][/CENTER]