2013 Excel How to Generate a List from a Conditional Formated SpreadSheet

[ksmith88]

I have been tasked with keeping up with Company Training Due dates and Generating a Spreadsheet, for 627 employees, to show visual reference for who has been Trained and who needed to be trained. I have conditional formatted the Cells with the dates to show 4 different Fields, 1 Greater than todays date is green, 2 Less than today Red, 3 Between today and January Blue, 4 between Today and 6 months from now. Now I have everything completed in my Spreadsheet but I am interested in trying to Generate a List that will filter the cells from the original Spread Sheet to another sheet that will only List the cells that are in yellow and blue. Which again represents the 3month due dates and the 6 month due dates. Any Idea on how to accomplish this goal and be able to possible search with any field in the spread sheet and generate a list.


The Spread Sheet is setup with Names of Employee there employee number, as well as a heading that is the Title of Training. I would like these fields to be part of the filtering process. So when I ask for a range of dates that the name of the employee along with what training is fixing to be due.

Any help would be greatly appreciated and attached is the file that I have prepared.

thanks in advance.

[Bernie Deitrick]

This file ahs a sheet where you can select the title and click a button to extract the values of interest. Give it a try -

Training Due Dates.xlsm

[ksmith88]

Okay I see what you have done and it looks good but I cant get it to accept when I select a title and then click the "CLICK ME AFTER SELECTING A TITLE" button.

What am I doing wrong?

[Bernie Deitrick]

Have you enabled macros? Make sure that your macro security level at least allows you to choose to enable macros:

TrustCenter.JPG

[ksmith88]

Okay so I think I have figured out the problem. However I am not sure how to fix it of course because this is completely out of my league lol. I noticed when I click on the titles to the side that it doesn't change the title on the list generator.

[Bernie Deitrick]

Oh - that list to the side feeds the Data Validation dropdown on cell C2, which then is used by cell E3 to find the column. Change the value in C2, then click the button.

[ksmith88]

I enabled my macros clicked the bottom button to enable and then clicked check box. Still only showing results for the Crane field.

[Bernie Deitrick]

After enabling macros, change the value in cell C2 using the DV dropdown - select, say, Aerial Lifts, and press Enter. Then click the button. When I do that, I get a list that extends down to row 341.

[ksmith88]

What about generating a complete list. See most of the guys end dates are all the same threw out all of the training courses. How could be take this list and generate a complete list that generate threw out the entire spread sheet with all the entries for each employee??

[Bernie Deitrick]

That is what your data list contains - the complete list. So you already have that....

[ksmith88]

Okay thanks.