Create searchable database of training for employees

[DLAWSON6]

Hello everybody,

I am trying to make a spreadsheet for my firm, and I am struggling mightily with how set up the formulas. The intent of the workbook is to create a searching database for our employees to find training opportunities they are interested in. I want the workbook to have two sheets. The second sheet will be my 'data' sheet. I will find training events, and in this second sheet I will place these training events in different rows, with details such as course type, date, credit hours, location, etc. in columns. Then on the first page, I have several "search" criteria, that have pull-down lists for several of those course details listed from the second page. Is there any way to have my first sheet set up, such that when certain pull-down list selections are made, the spreadsheet will filter the database of training opportunities on the second page to provide results for the employees on the first?. For example, if the employee selects "June" as the course month, the first page would pull all of the training opportunities in June from the second page and list those events with all of their respective details in the first sheet. Then, if the employee would also select 30-50 miles, the results would only be those events that were in June and withing 30-50 miles, etc. Is there any way to do this? I've attached a copy of my file for your review so you know what I am attempting. Any help or leads would be appreciated IMMENSELY! I have already spent significant time trying to figure out a way, all dead ends.

BONUS QUESTION: If there is a way to do the above mentioned task, is there anyway to filter out courses that have past dates (later than the current date) so that I don't have to go in and delete the old training events, but rather just have them not included in searches?

This seems very complicated to me, at least with my level of Excel experience, but maybe one of you can shed a light. THANKS A TON!

[dflak]

Here you go and it doesn't take VB, so you can even save this as an XLSX file if you wish.

This spreadsheet makes use of excel tables. http://www.utteraccess.com/wiki/Tables_in_Excel.

I "moved" your lookup tables to a lookup sheet and the information is in tables. Having the information in tables means that you can add and change data in the tables without having to adjust any lookups or formulas. You can hide this sheet if you wish.

I added some data validation to the main table.
- Type is looked up from the type table - take a look at how I used INDIRECT and the table name and column header. This makes it dynamic.
- Distance is a whole number greater than or equal to zero
- Sign Up date is grater than or equal to today
- Course begin is greater than or equal to sign up date
- Course end is greater than or equal to course begin date
- Professional Development Hours is a whole number greater than or equal to zero.

Since all of these items are in a table, all the validations copy down each time you add a new line.

I also added some helper columns:
- Distance - uses VLOOKUP with the TRUE flag to assign buckets
- Hours - uses VLOOKUP with the TRUE flag to assign buckets
- OK - is true if the sign up date is greater than or equal to today otherwise false.

Likewise, these formulas are copied down automatically as data is added.

I ditched your original sheet since it had merged cells. The only place merged cells should be used is for "cosmetic" purposes. They should not be used in cells that are column headers or contain formulas or cells that are referenced by other formulas. If you need a cell that wide, use a single cell and make it wider .

The report is nothing more than a pivot table.

There is a fixed filter on row 3 which is set to TRUE - only show those courses whose sign up dates are today or later. This row can be hidden.

At the top of the sheet are slicers to select the various buckets and the month. I suggest you do a search on slicers. They are easy to implement, and are very powerful in that you can use them to control multiple pivot tables at once. Besides they look cool . The slicer for the month is set to the course start date.

About the only bad thing with the slicers is that they show the distance and hour buckets in alphabetical order. If you want to show them in numerical order, you would have to create a couple of custom sorts which is another topic.

[DLAWSON6]

dflak,

Thank you very much! I had never heard of slicers before now, look pretty slick. I will remember your advice and suggestions on my next projects!! Thank you again for your time and effort!!