Excel spreadsheet planning question.....

[compugoddess]

Hello! I have a spreadsheet with the following information that gets updated on a monthly basis. I'm trying to be as efficient as possible to avoid moving things around too much when updating it. Each section will vary as far as how many lines it has.

I need the following information:
Department code
Department Description
Department Quota
Positions filled
Positions Open
what the opening is "worth" (ie: 1, .4, .7, etc)
Reason for opening (new position or name of person who left the postion)
when they left
why they left
who filled the position
when they filled it
2 comments fields (1 for leaving employee, one for the department in general
the percent of openings per dept
the total quota of all departments
the total openings of all departments
the total percentage of openings for all departments
number of agency staff employeed by each department

also, i need to show positions that are new highlighted in purple, positions that have been filled highlighted in green, keeping the positions in green together, and be able to easily remove old information without having to cut and paste and reformat the worksheet.

As you may be able to tell, I'm currently working with a worksheet designed by someone else, and it just seems that there has to be a more efficient way to do it. I seem to have a mental block on this. Any suggestions would be greatly appreciated, even if they just jolt my mind in the right direction..... I thought about using a pivot table or multiple sheets to enter info then pull it together, but I just can't seem to get what I want.

Thanks! Have a great day!

Di

 

[naiku]

Have you not thought about using Access instead? You can then just set up your tables to hold data and just use a form for inputting/changing/deleting records and/or information.

A lot of the data you mention can be put into drop down lists so that when you add anything new its just a case of selecting an item from the list, calculations will be no problem either and you can setup your own reports to be printed whenever you need them

Hope thats some help, just my opinions

Naiku

 

[compugoddess]

Hello, Naiku, and thank you for your reply.

I have thought about using Access. However, this is how they've been doing it in Excel and you know how change can be! I thought I'd explore my options there first.

I think I will start playing around with a database as well, just to see what they think.

Thanks again!

Di

 

[naiku]

No problems, hope that you can get it all set up how you want it. And remember change is a good thing!!

Naiku