find name; search column; if date found-return 3 data pts; move to next row

Good morning gurus,
I have a wonderful Training spreadsheet that works thanks to help from this forum. I am looking to make it better. It is 23 mb because of all the macros and formulas it has. I believe I can do things in 1 step where I have 3. This is one of them. I have searched the forums and thought I could figure this out but am unable to. Can you please help with a macro or formula that would look up a name from a drop down, have it search another sheet in column B (variable based on name chosen) and if date found, return B, C (variable based on name chosen) and A (fixed). It would analyze about 150 rows for a date present and return a consolidated report for the employee. An example is attached.
Thanks so much,
Cindy

Originally Posted by cdl

Good morning gurus,
I have a wonderful Training spreadsheet that works thanks to help from this forum. I am looking to make it better. It is 23 mb because of all the macros and formulas it has. I believe I can do things in 1 step where I have 3. This is one of them. I have searched the forums and thought I could figure this out but am unable to. Can you please help with a macro or formula that would look up a name from a drop down, have it search another sheet in column B (variable based on name chosen) and if date found, return B, C (variable based on name chosen) and A (fixed). It would analyze about 150 rows for a date present and return a consolidated report for the employee. An example is attached.
Thanks so much,
Cindy

Hi,

I'm re-attaching your workbook with some changes.
In my experience it's always easier to work with a database if it's kept as simple as possible. I've therefore re-arranged your existing data. It cuts down the number of columns and increases the number of rows, but more importantly similar fields, like Employee Name for instance, now appear in the same column.

I've added a simple Forms control to select the names, although in a real world system I much prefer using the ActiveX VBA controls.

HTH

Richard,
Thank you for your input. Seeing that you are the expert I am taking your advice and restructuring my "data" spreadsheet. I'm sure I will be posting again for now I will run into different problems I can't solve. On this one though I have tried to incorporate what you did to help me and am getting "error 400". One thing I could get rid of with this new structure was the Required column. Maybe that has something to do with it. Could you please take a look and help me out again.
Thanks,
Cindy

Hi Cindy,

Try the re-attached book. I think it was just over the 100k limit so I've zipped it up.

The problem was associated with merged cells. Data Filtering isn't happy when trying to cope with those, so if it should fall over again after you've added more data, just check the database columns for merged cells and clear them. You can do it all in one hit if you select all of columns A:C and then Format Cells.

Rgds

Richard,
Thank you for your help and tips. I've been playing with Advanced filtering and making some headway on my newly designed spreadsheet.
Cindy