+ Reply to Thread
Results 1 to 4 of 4

Create name list from multiple sheets based on drop down selection

Hybrid View

  1. #1
    Registered User
    Join Date
    03-27-2020
    Location
    texas
    MS-Off Ver
    2013
    Posts
    2

    Post Create name list from multiple sheets based on drop down selection

    Hello everyone! PLEASE HELP!!

    In the attached sample spreadsheet I have created a drop down list in "DET TOTAL L2" based on the categories (row 2) in the other 5 sheets (HQ,FC,LCHR,EW,SIG). I am looking for a formula that will compile the names from each sheet that match the criteria of the dropdown list into column M. I was thinking INDEX/MATCH but not sure. Thank you in advance!

    Kelly Weaver
    Attached Files Attached Files

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Create name list from multiple sheets based on drop down selection

    Hi Kelwea, see the attached sheet with macro. Hope that helps.
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long, j As Long, k As Long, ws As Worksheet, ws1 As Worksheet
    Set ws1 = Sheets("DET TOTAL")
    k = 2
    
    ' Check if multiple cells changed, and if so, exit macro
    If Target.Cells.Count > 1 Then Exit Sub
    
    ' Check if worksheet change involved cell L2
    If Not Intersect(Target, ws1.Range("L2")) Is Nothing Then
    
        ' Clear contents of M2:Mx
        lastrow = ws1.Range("M" & Rows.Count).End(xlUp).Row
        ws1.Range("M2:M" & lastrow).ClearContents
        
        ' Assign column # based on drop-down selection (from layout of sheets HQ, FC, etc.)
        Select Case ws1.Range("L2").Value
            Case "ME"
                j = 2
            Case "TDY"
                j = 3
            Case "PCS"
                j = 4
            Case "Leave"
                j = 5
            Case "TW1"
                j = 6
            Case "TW2"
                j = 7
            Case "TW3"
                j = 8
            Case Else
                Exit Sub
        End Select
                
        ' Loop thru sheets
        For Each ws In ThisWorkbook.Worksheets
            
            ' Check for sheet name
            If ws.Name = "HQ" Or ws.Name = "FC" Or ws.Name = "LCHR" Or ws.Name = "EW" Or ws.Name = "SIG" Then
                
                ' Loop thru rows 3-50
                For i = 3 To 50
                    
                    ' Check if cell value in current row of specific column (j) equals 1
                    If ws.Cells(i, j).Value = 1 Then
                    
                        ' If so, set 'DET TOTAL'!Mk value to the value in that row, column A
                        ' where k starts at 2 and increments whenever a 1 is found
                        ws1.Cells(k, 13).Value = ws.Cells(i, 1).Value
                        k = k + 1
                        
                    End If
                
                Next i
                
            End If
            
        Next ws
        
    End If
        
    End Sub
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    03-27-2020
    Location
    texas
    MS-Off Ver
    2013
    Posts
    2

    Re: Create name list from multiple sheets based on drop down selection

    Thank you so much for the response. That seems to work great, however my work disables macros and we are not able to use them on my work computer. Is there a formula set that can do this same thing?

  4. #4
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885

    Re: Create name list from multiple sheets based on drop down selection

    I highly doubt any formula will do this for you. It would likely take a series of formulas to gather the needed info from multiple sheets, and then more to organize them into a list.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. "VBA code to hide/show rows based on multiple selection of drop down list.
    By NASANASA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-02-2020, 05:07 PM
  2. [SOLVED] Create a dependant drop down list based on multiple entries with the same name
    By heidzhaydz in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-15-2019, 04:12 AM
  3. [SOLVED] create a drop down which allows multiple selection and change content of table based on th
    By melody10 in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 08-16-2015, 06:12 AM
  4. Replies: 1
    Last Post: 06-25-2015, 07:29 AM
  5. [SOLVED] Returning data across multiple sheets based on drop down list selection
    By rooboyz in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-29-2014, 02:41 AM
  6. macro for printing sheets based on drop-down list selection
    By carrollkm in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-26-2013, 10:36 PM
  7. Multiple results to display based on selection made from drop down list
    By scoot_eruk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-16-2012, 05:03 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1