Excel: Need to add some functionality to a spreadsheet, possible, How?

[Airbiskit]

Let me see if I can explain this clearly.

I have a sheet that lists all our employee names in column A. In column B to L are headed by a type of skill. So against each employee they will have a "Yes" under their relevant skills. Some have the same skills.

The second sheet is a timetable as such. Dates for the current month along the top and the name of the employees again in Column A. Under each date, where applicable, the employee will be using his skill and this is booked into the cell using a 2 letter code.

Now if we have 5 employees with say a "football" skill and I have used 3 employees up in the timetable already, is there a way of finding out the remaining 2 employee names with that skill who are free to be booked in? This would need to be done by date and skill. So I am looking for anyone who is free on this date with this skill.

Any help or suggestions are truely welcome

Thanks

Steve

 

[SkipVought]

Hi,

First off, your database has a glaring flaw, as it is not normalized. You might consider getting familiar with database fundamentals

http://r937.com/relational.html.

Both of your sheets have this characteristic, it seems. It will make processing the data extremely difficult. Your layout is how it might be REPORTED; definitely NOT how it should be store for analysis, processing and reporting.

Skip,

_[red][/red]

 

[Airbiskit]

Thanks Skip,

The information I need to process is name, skill and date.

If I had one sheet that showed name in column A and all the skills across from say Column B to H. In Each field with a skill was a true or false value.

In sheet 2 tha same layout but this time a date would be entered in for when the skill was used against the name.

This would work better? I can right queries in Access to give me information on unused skills on certain dates, but how would I do this excel?

Thanks

Steve

 

[Rofeu]

I think that Skip is going for something like this:

Sheet1
A        B         
Name     Skill     
PersonX  Football   
PersonX  Baseball
PersonY  Football
etc.

Sheet2
A          B        C         
Date       Name     Skill     
01/01/2007 PersonX  Football   
01/02/2007 PersonX  Baseball
05/03/2007 PersonY  Football
etc.

You could then reasonably easy build a query to find the persons whose skills are available on a certain date.

Cheers,

Roel

 

[SkipVought]

"I can right queries in Access to give me information on unused skills on certain dates, but how would I do this excel?"

Using MS Query to get data from Excel faq68-5829

Skip,

_[red][/red]

 

[Airbiskit]

Thanks chaps, I will work through these solutions.