Follow-up List

[carlomscalisi]

I attached photos. I have a spreadsheet with multiple rows containing criteria to be met or unmet, and then columns pertaining to persons who have either met or unmet the criteria in the rows. What I would like to do is create a follow-up sheet that autopopulates any unmet items. The autopopulated list would be dynamic, meaning that if there are no unmet items, the list would be blank, but as items are marked as unmet, they would be sequentially added to the follow-up sheet. This dynamic is what I don't understand, and I'm not sure if it's possible without VBA or not. Any suggestions would be greatly appreciated!

 

[James006]

Hello,

If you do not mind, a very general remark ...
The "Unmet List" you need is already available in your Main sheet with the AutoFilter ...

 

[carlomscalisi]

Hello,

If you do not mind, a very general remark ...
The "Unmet List" you need is already available in your Main sheet with the AutoFilter ...

Hi James,

Thanks for your remark. Unfortunately, I don't believe that's what I'm looking for. First of all, to use AutoFilter I would need to do this for each person (column). Second of all, I don't want to rely on the end-user to do any sorting. The spreadsheet would be used by many people with varying levels of familiarity with Excel. On the Follow-up sheet, I would just like to pull the unmet items into the list, but in a dynamic, sequential way.

 

[James006]

Hi,
In an attempt to make your life easy, you could insert a Pivot Table ... which can get automatically refreshed ...

 

[carlomscalisi]

Hi,
In an attempt to make your life easy, you could insert a Pivot Table ... which can get automatically refreshed ...

Hey James,

How would that work, exactly? The follow-up sheet would need to include the name of the person (column) and the item that's unmet (row), in addition to another column header. I'm not sure how to do a PivotTable that would pull this information, but would love your assistance!

 

[James006]

Hi,

My recommendation is to dive into following link (with a video) designed by the QUEEN of Pivot Tables : Debra Dalgleish

3 Steps to Create Excel Pivot Table - Analyze Data

How to create Excel pivot table in 3 steps, to summarize and analyze Excel data. Short video, written steps, screenshots, Excel workbook to download

www.contextures.com

 

[carlomscalisi]

Hi,

My recommendation is to dive into following link (with a video) designed by the QUEEN of Pivot Tables : Debra Dalgleish

3 Steps to Create Excel Pivot Table - Analyze Data

How to create Excel pivot table in 3 steps, to summarize and analyze Excel data. Short video, written steps, screenshots, Excel workbook to download

www.contextures.com

Hey James,

I appreciate your assistance, but I'm afraid that you may not understand what I'm asking. I don't want a table that can be filtered or sorted. I want a separate sheet that will pull data from the table based on certain criteria within the table, i.e. whether a condition is met or not met.

I was able to mimic this, but it's not very efficient. Essentially, in the follow-up sheet that I want to auto-populate, I created cell references to the original sheet using IF statements. That is, if the cell on the original sheet contains "met", the cell in the follow-up sheet will return a 0. However, if the cell on the original sheet contains "not met", the cell in the follow-up sheet will return a certain value, in this case the person's name. Then, I used a macro assigned to a "Refresh" button that first unhides all rows, and then hides the rows that contain a 0, meaning that the criteria was met, and leaving only those that were not met. To my surprise, this actually works, but the macro is very slow.

I'm attaching some photos in case you have any insight.