file size issue

[ed.ayers315]

I have a file in excel 2007 I designed with some table and form automation to show where employees are on disciplinary issues. I wanted to have this set up with a tab for each employee so it would be easy for the clerks to keep it straight and up to date. My problem is we have 157 employees and I am now at 104 tabs and have ran out of ram. I have 2 GB on my system.

Is there a way I could send a sample of what I have to someone and see if it can be reduced to one main page populated by a data base based on the employee selected.

 

[ChrisM]

If 104 out of 157 of your employees have discipline issues you have a lot of problems!!

It doesn't sound like you are setting up your file in the most efficient manner possible. As a generic rule, databases and database-like structures tend to work down, not across. Consider Excel 2003, 65,000 rows down, only 256 columns across.

Anyways, I would setup one sheet with employee data, one row per employee, have name, ID #, address, etc.

Then the second sheet would have ID #, a vlookup to return Name, Discipline Action, and Start, Stop Dates. This sheet only gets populated when an action occurs.

Then your clerks can use pivot tables or autofilter to quickly search the second sheet. They can view only actions for one employee, count those actions, sort by action type, count days suspended, etc.

 

[ed.ayers315]

Chris,

I forgot to thank you. The post helped me solve my issue!