Dynamically Extracted Rows Based On Cell Data

kn4thx

New Member
Joined
Apr 7, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,563
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.
Rich (BB code):
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
 

Forum statistics

Threads
1,136,790
Messages
5,677,745
Members
419,718
Latest member
ALWP

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top