mr_crowley
New Member
- Joined
- Feb 18, 2007
- Messages
- 7
I'm about to have an operation on my spine that will wipe me out of work for up to 6 months so I am looking at enhancing my Excel knowledge. Invested in quite a few VBA and Formula books so I can do some indepth reading and practicing as VBA is totally new to me.
The workbook I'm dealing with at the moment is based around KPI (Key Performance Indicators) for the job roles my employees undertake. The corporate system is superb but I want a way of costing the effects on my laptop and will enable me to plan operational adjustments both short and long term.
My worksheet is structured as follows.
Sheet 1 "Factors"
Sheet 2 "Input"
Sheet 3 "Performance"
Sheet 1 - this is simply for user information only - plays no other use
Sheet 2 - contains around 90 15-200 rows of data across 6 columns
1. Employee Name
2. Contracted Hours
3. Task Name
4. Task Hours
5. Task Cost
6. Total Cost
Sheet 3 - Is the final KPI sheet that compares target and actual costs (Mon-Sat) and (Sun)
The target hours are pre-determined by our corporate system from my "Factors"
So now my requirements.
I wish to be able to take the information from "Input" into the relevant cell within "Performance" with the simplest option
e.g.
Name..........Cont Task..............................Task Hr.....Task £.....Total £
Joe Bloggs.....20..Legal Checks.......................5 ...........25.00
..........................Layout Implementation.........5............25.00
..........................Merchandising ....................10...........50.00....100.00
This is repeated with around 40 "Tasks" across 90 employees.
I need the formula to to find all "Legal Checks" which could be with 6 or 7 employees, then take SUM the hours and place in the relevant cells within the "Performance" Sheet. This needs to be the same for all "Tasks" and then wil also need to do the same for the "Task Costs" for each "Task".
The clever part
needs to be that if the "Task" changes it automatically transfers the data.
At the moment I have a costing sheet all in 1 worksheet but it is very basic and results in stupid "Calculator" style sums that mean a lot of unecessary adjustments every time I have a change in personnel or responsibilities (eg =D55+D78+D84+D88+D93+D102+D106+D116+D119+D122)
Hope that makes sense.
Am I right in thinking I am going to have to go down the "LOOKUP" routes???
I'll try and post screenshots of what my 2 sheets look like so you can get the idea (will just be a snapshot not as per the sticky)
[/list]
The workbook I'm dealing with at the moment is based around KPI (Key Performance Indicators) for the job roles my employees undertake. The corporate system is superb but I want a way of costing the effects on my laptop and will enable me to plan operational adjustments both short and long term.
My worksheet is structured as follows.
Sheet 1 "Factors"
Sheet 2 "Input"
Sheet 3 "Performance"
Sheet 1 - this is simply for user information only - plays no other use
Sheet 2 - contains around 90 15-200 rows of data across 6 columns
1. Employee Name
2. Contracted Hours
3. Task Name
4. Task Hours
5. Task Cost
6. Total Cost
Sheet 3 - Is the final KPI sheet that compares target and actual costs (Mon-Sat) and (Sun)
The target hours are pre-determined by our corporate system from my "Factors"
So now my requirements.
I wish to be able to take the information from "Input" into the relevant cell within "Performance" with the simplest option
e.g.
Name..........Cont Task..............................Task Hr.....Task £.....Total £
Joe Bloggs.....20..Legal Checks.......................5 ...........25.00
..........................Layout Implementation.........5............25.00
..........................Merchandising ....................10...........50.00....100.00
This is repeated with around 40 "Tasks" across 90 employees.
I need the formula to to find all "Legal Checks" which could be with 6 or 7 employees, then take SUM the hours and place in the relevant cells within the "Performance" Sheet. This needs to be the same for all "Tasks" and then wil also need to do the same for the "Task Costs" for each "Task".
The clever part
needs to be that if the "Task" changes it automatically transfers the data.At the moment I have a costing sheet all in 1 worksheet but it is very basic and results in stupid "Calculator" style sums that mean a lot of unecessary adjustments every time I have a change in personnel or responsibilities (eg =D55+D78+D84+D88+D93+D102+D106+D116+D119+D122)
Hope that makes sense.
Am I right in thinking I am going to have to go down the "LOOKUP" routes???
I'll try and post screenshots of what my 2 sheets look like so you can get the idea (will just be a snapshot not as per the sticky)
[/list]
