View Full Version : Excel capabilities

15-07-2009, 07:18 AM
I'll try and explain this mess as clear as I can. I'm an intern and working on a project where I'm supposed to add a new worksheet to an existing labor budget spreadsheet. On this new spreadsheet a project manager will enter an employee name, organization#, organization name, pay status, and the number of hours they forecast the given person will work in a certain month.

This part isn't so bad but the next step I'm having trouble figuring out its feasability.

I need to aggregate the data from the first worksheet into another worksheet for the finance department. The catch is that while on the first worksheet the data was entered for specific employees, this other worksheet will need its data to be grouped by organization numbers. I will need a cell in the new worksheet to go to the other worksheet find every instance of a organization number then calculate the sum of the hours worked by the people that make up that org. It also needs to differentiate "exempt vs non exempt" pay status and the name of the organization they belong to (in my case it will need to seperate BGE from CEG employees). The expectation is is that the spreadsheet should be able to detect all of the various org#s on the preceeding worksheet and aggregate the labor hours for a given month automatically as well as distinguish different pay types and different organization names. Since the project manager could enter the data out of order I can't rely on any set ranges or IF statements referencing specific cells.

I'm sorry if this is unclear, this was just plopped on my desk but I'm really finding the expectations unreasonable. Any help would be much appreciated.

15-07-2009, 08:43 AM
I have a limited understanding of spreadsheets but I believe that they are unsuited to this task. The tool I have used in the past to do this type of thing is Delphi but Visual Basic would do it as well. I appreciate that this is probably not an option for you but the spreadsheet thing is not going to work well if at all.

15-07-2009, 10:15 AM
Try the VLOOKUP() function, for starters.

kahawai chaser
15-07-2009, 11:36 AM
I think pivot tables may help, where you could enter data based on organization names and/or numbers. But I think those names/groups need to be recurring/re-entered in their cells for pivot tables to work effectively - i.e. then can group them as a whole to apply calculations and any graphs. Then summarize/calculate ( by selected categories). But you may need two sets (or more) of pivot tables - one for the labor budget and another for your new spreadsheet/finance etc.

Try pivot tables with the wizard and drag/drop the category "fields", and if you drop the sum or the average fields - then can right click that field (when it's in the table) to display further calculations (including ratio's of sub groups - If I recall). There are various layout/structures (by dragging across/down in the pivot table) for the overall pivot table's appearance to find what's suitable - if at all suitable. But worth a try as it's easy to initially set up with the wizard, and their are numerous online tutorials to help out...

kahawai chaser
15-07-2009, 11:56 AM
But if you have hundreds of entries (or the potential of hundreds/thousands), then as noted by McGinty 48, you will end up wrestling with multiple data sets. I found this out in a previous job, where we had company names, date categories, data, Report No.'s, employee names, supplier's names, etc. In the end we switched to Microsoft 97 Access (customized) - a relational database to handle 100's of entries per week. It became to tedious/unwieldy in Excel - but that's were I started - with Pivot tables.

15-07-2009, 12:25 PM
Hi, I think the important sentence you mention is "Since the project manager could enter the data out of order I can't rely on any set ranges or IF statements referencing specific cells."

Could you give an example please. Do you mean rows may be missed (which isnt a problem) or do you mean the column where, for example, you enter Apples (say Col A) is really the total for Oranges (Col B) and visa versa (which is a problem)?

A pivot table would seem the answer but you can also used advanced forms of lookups such as SUMPRODUCT which enables you to sum or count based on multiple criteria.

If the data is all over the place then regardless of whether you use Excel, Access or SQL you will have trouble.