how to generate a list of names based on two drop down box selections

[katie87]

I'm really new to <acronym title="visual basic for applications">VBA</acronym> and Excel, I know a few bits and I have created validation lists and a macro to rename the sheetname depending on what text is entered into a certain cell etc.

I don't really know where to begin with creating a macro to report back a list of sheet names onto the "overview/master" sheet if a certain cell on any of the other sheets contains a specific word. What is complicating this matter is I need it to reference a different cell on each sheet, depending on which options are selected from the two drop down boxes on the overview sheet. All in the same workbook btw.

In the most basic form, what I have created is an "OVERVIEW" sheet. This has two drop down boxes (using data validation lists) on it. It has two drop down lists, one contains the text Grade 1, Grade 2 etc) and the other drop down box contains levels e.g. Foundation, Intermediate, Expert etc. Each list has 5 entries.
The rest of the sheets contain a grid matrix 5 rows on the vertical axis (Grade 1-5) and 5 columns on the horizontal axis(levels, eg foundation/intermediate/expert.) Each sheet is the exactly the same template, same reference in the same cells etc. This will then be filled in when someone finishes grade 1 foundation, the box in the matrix that refers to that level would be filled with the text "completed" on that particular sheet. Each person has their own sheet so they will either say completed/in progress/not done depending on who has done what.

I need a macro that will say if the overview sheet C5 = "Grade1" AND E5 = "Foundation" (both the drop down boxes) then loop through all workbook sheets & look at cell E13 (the grade 1 foundation box) and if it says "completed" in that box on any of the sheets, then copy the sheetname into a box on the "overview" sheet in a list in cells I5-I11. so anyone who has completed that level will be listed on the overview sheet. I need it to work for any grade/level that is selected from the two drop down boxes.


What I have so far:

<code>Sub LoopThroughSheets()

If worksheets ("OVERVIEW") GOTO C5="Grade 1" And E5="Foundation" Then

' I want this bit to say if the overview worksheet cell C5 contains grade 1 and cell e5 contains foundation then</code>'
<code> =IF E13="completed", THEN ActiveSheet.Name = Range("I5:I11").Copy)
' I want this bit to then say if the word completed is present in cell E13 on any of the subsequent sheets it should copy the sheet name to the overview sheet in cells I5 to I11'


Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets


Next ws
End Sub</code>

 

[XLfriend777]

Re: based upon specific cells by pulling data from multiple sheets

Hi Katie87 - Welcome to the forum. In hopes of getting you started I think we could try to sneak up on this one incrementally. In your OVERVIEW sheet list the names of all the other worksheets in Cells I5-I11 (7 sheets if I read your instructions correctly). Then in Cell J5 of the OVERVIEW sheet place: =INDIRECT(I5&"!$E$13") and then fill down to J11. Report what you get and let us know if that is kinda what you are looking for. Hope this helps get things started.

 

[tonyyy]

katie87,

Welcome to the Board.

Assuming the "grades" are in Column 1 and the "levels" are in Row 1 for each of the grid matrix sheets, you might try the following...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim grade As String, level As String
Dim rw As Long, cl As Long
Dim LastRow As Long

Set ws1 = ThisWorkbook.Sheets("Overview")
If Not Intersect(Target, ws1.Range("C5")) Is Nothing _
    Or Not Intersect(Target, ws1.Range("E5")) Is Nothing Then
        ws1.Range("I5:I11").ClearContents
        grade = ws1.Range("C5").Value
        level = ws1.Range("E5").Value
    For Each ws2 In ThisWorkbook.Worksheets
        If ws2.Name <> "Overview" Then
            rw = ws2.Columns(1).Find(What:=grade, After:=Cells(1, 1), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
            cl = ws2.Rows(1).Find(What:=level, After:=Cells(1, 1), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Column
            If LCase(ws2.Cells(rw, cl)) = "completed" Then
                LastRow = ws1.Cells(Rows.Count, "I").End(xlUp).Row + 1
                If LastRow < 5 Then LastRow = 5
                ws1.Cells(LastRow, "I").Value = ws2.Name
            End If
        End If
    Next ws2
End If

End Sub

The code should be pasted into the "Overview" worksheet module.

The code will execute whenever a selection is made in C5 or E5 on the Overview worksheet.

Cheers,

tonyyy

 

[katie87]

Thank you to both @tonyyy and @goesr you have no idea how much I appreciate your help on this!!

Neither of those is doing anything when I paste it in, so I think I'm going to try and make it simpler and have a separate button for each grade/level and see if I can just use the same bit of code for each button but change the cell it is referencing.

The overview sheet is going to have a grid running from B2-F6. So I will create one button in each cell (e.g. b2=grade 1 foundation, b3=grade 3 intermediate, c2=grade 2 foundation etc so 25 buttons in total) so when I click on one particular button (B2 for instance) it will go through all the sheets and look at cell E13 and if it says completed, then it will list the sheet name somewhere on the overview sheet (lets say it will create a list of names in column H for instance) I can then alter the code slightly so each button references the corresponding cell on the rest of the worksheets. Hopefully then it will be slightly less code to write!

If you could point me in the right direction of where I should start, that would be great as the previous code wouldn't let me assign a macro button and wasn't doing anything when I changed the dropdown boxes.

 

[katie87]

thank you for your input, if you have time to advise further, that would be great.

 

[tonyyy]

Neither of those is doing anything when I paste it in, so I think I'm going to try and make it simpler and have a separate button for each grade/level and see if I can just use the same bit of code for each button but change the cell it is referencing.

Adding numerous buttons won't make the coding easier; just more tedious. (My opinion.)

...go through all the sheets and look at cell E13...

It would be helpful if you post sample data from one of the sheets. It would tell us which column contains the "grades" and which row contains the "levels."

 

[katie87]

Adding numerous buttons won't make the coding easier; just more tedious. (My opinion.)



It would be helpful if you post sample data from one of the sheets. It would tell us which column contains the "grades" and which row contains the "levels."

Thanks tonyyy, sorry for not being clearer, here is an image of the template page, hope that provides you with more information:

Imgur: The most awesome images on the Internet

Each box will be filled in with one of the three drop down options on each individual person/sheet so we need to list the name of the person/sheet on the overview page if that person has "completed" next to the level we are selecting from the dropdown boxes on the overview sheet:

Imgur: The most awesome images on the Internet

So if on the overview sheet we select grade 3 + intermediate on the drop down box for example, then the merged box I5-11 will show the list of names for each person who has "completed" showing in that box (G12 on the template) and will not list the name unless it says "completed" on their sheet. The name can either come from cell C3 on the template or the sheet name itself

Hope that makes sense?

 

[tonyyy]

Thanks, katie87, for posting the images.

Tweaked the code to look for "grade" and "level" in the proper column and row...

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws1 As Worksheet, ws2 As Worksheet
Dim grade As String, level As String
Dim rw As Long, cl As Long
Dim LastRow As Long

Set ws1 = ThisWorkbook.Sheets("Overview")
If Not Intersect(Target, ws1.Range("C5")) Is Nothing _
    Or Not Intersect(Target, ws1.Range("E5")) Is Nothing Then
        ws1.Range("I5:I11").ClearContents
        grade = ws1.Range("C5").Value
        level = ws1.Range("E5").Value
    For Each ws2 In ThisWorkbook.Worksheets
        If ws2.Name <> "Overview" Then
            rw = ws2.Columns(4).Find(What:=level, After:=Cells(9, 4), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
            cl = ws2.Rows(14).Find(What:=grade, After:=Cells(14, 5), LookIn:=xlFormulas, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column
            If LCase(ws2.Cells(rw, cl)) = "completed" Then
                LastRow = ws1.Cells(Rows.Count, "I").End(xlUp).Row + 1
                If LastRow < 5 Then LastRow = 5
                ws1.Cells(LastRow, "I").Value = ws2.Name
            End If
        End If
    Next ws2
End If

End Sub

And again, the code should be pasted into the "Overview" sheet module and will execute whenever a selection is made in cells C5 or E5 on the Overview sheet.

 

[katie87]

Thanks Tonyyy,

I inserted it into the overview sheet code and when I make a selection in C5 (or in E5) it gives me error 91: object variable or with block variable not set.
When clicking debug it highlights the lines:

rw = ws2.Columns(4).Find(What:=level, After:=Cells(9, 4), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

 

[tonyyy]

katie87,

The error indicates the code is not finding a match between the selection (in C5 or E5) and the data sheets. Do the items in the validation lists match exactly with the levels and grades in the data sheets? Please be sure to check capitalization and blank spaces.