Dynamically Extracted Rows Based On Cell Data

[kn4thx]

Good Morning,

I am trying to set up a spreadsheet that populates results on a separate worksheet based on cell data. I attempted to search for this solution, and I am sure it is out there, but do not know enough about what I am looking for to apparently find it.

Here's the scenario:

I essentially want a checklist on one page, and a populated report of findings with generically populated descriptions on another. If I have 100 items in a checklist, and 23 are selected, the report sheet should only list those 23 automatically and then pull descriptions from a hidden table alongside them.

Here's a basic layout:

Sheet A - Checklist of 20-40 rows with a Yes/No dropdown next to each. Let's say Column A is the Data Vali drop down and Column B is the Checklist item

Sheet B - Needs to dynamically create a list of the items marked YES; as items are checked and unchecked the list should update accordingly.

Sheet C Extra - I am going to create a behind the scenes table which links a description based on each Checklist Item. When an item from the Sheet A Checklist is populated on Sheet B, the unique description that goes with that unique checklist item will also populate.

Example:

Checklist:
---------------
Yes Butter
No Lettuce
No Ice
Yes Candy



Report:

Item Of Concern Reason For Concern

1. Butter | The butter was found to be bad, please buy more butter.
2. Candy | The candy was melted



The example of course is just generic but that's the idea. I can do everything except automatically extract the checklist items meeting the YES criteria and have it dynamically populate on Sheet B as dropdowns are changed. That's the one part I am lost on.

Any help is appreciated.

Thank you

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet A and click 'View Code'. Paste the macro into the empty code window that opens up. Change the destination sheet name (in red) to suit your needs. Close the code window to return to your sheet. Select "Yes" in column A. This is a preliminary suggestion that will copy column B to the destination sheet. Without knowing exactly what your actual workbook looks like, it's hard to make a definitive suggestion.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Target = "Yes" Then
        Sheets("Sheet B").Cells(Sheets("Sheet B").Rows.Count, "A").en(xlUp).Offset(1) = Target.Offset(, 1)
    End If
End Sub