Rafiki_Runner
New Member
- Joined
- Apr 25, 2018
- Messages
- 3
I am hoping somebody can suggest a simplified version of what I currently have.
I am tracking time spent on a project by other team members and have an extremely over complicated way of working out the costs.
I created a user form for inputting the hours to make it simpler for the team and avoid them being overwhelmed, however the data from that submits to a hidden sheet that contains columns right up to HB (a lot of columns!) This calculates all the costs, which are somewhat more concisely reported in a different tab.
I don't like it, but hey, if it aint broke, don't fix it.
but then things changed slightly...!
I have three different tasks, three different locations and four different grades of staff. I therefore have to calculate the costs depending on what was done, who did it and where they are based. i.e Consulting by a Senior Project Manager in the UK. or QC checking by a Junior Associate in India
Below is an example of the nested IF statements I have used. This calculates the cost (the metadata tab has the predefined hourly rates in a table and the L5 cell quoted is the number of hours submitted in the userform)
=IF($C5="US-Grade 2",$L5*Metadata!$B$12,(IF($C5="US-Grade 3",$L5*Metadata!$C$12,(IF('Timesheet (T&M)'!$C5="US-Grade 4",'Timesheet (T&M)'!$L5*Metadata!$D$12)))))
This calculates the cost for one of the tasks, for one location. I therefore have this repeated for the other two tasks, then all of that again for the other two locations. (then a whole load of columns to break those hours out dependant on the month the hours were worked, for reporting purposes - but that's a whole different headache!!)
As I say, it's untidy and most likely overly complicated, but it worked.
Now though, there have been some changes to the structures and we will have 6 grades of staff with 2 additional locations and perhaps further tasks with these broken down to a more granular level.
Is there any simple way of looking at three different fields 'Grade' 'Location' 'Task' and pulling the cost that should be associated with the three when combined?
Thanks all
I am tracking time spent on a project by other team members and have an extremely over complicated way of working out the costs.
I created a user form for inputting the hours to make it simpler for the team and avoid them being overwhelmed, however the data from that submits to a hidden sheet that contains columns right up to HB (a lot of columns!) This calculates all the costs, which are somewhat more concisely reported in a different tab.
I don't like it, but hey, if it aint broke, don't fix it.
but then things changed slightly...!
I have three different tasks, three different locations and four different grades of staff. I therefore have to calculate the costs depending on what was done, who did it and where they are based. i.e Consulting by a Senior Project Manager in the UK. or QC checking by a Junior Associate in India
Below is an example of the nested IF statements I have used. This calculates the cost (the metadata tab has the predefined hourly rates in a table and the L5 cell quoted is the number of hours submitted in the userform)
=IF($C5="US-Grade 2",$L5*Metadata!$B$12,(IF($C5="US-Grade 3",$L5*Metadata!$C$12,(IF('Timesheet (T&M)'!$C5="US-Grade 4",'Timesheet (T&M)'!$L5*Metadata!$D$12)))))
This calculates the cost for one of the tasks, for one location. I therefore have this repeated for the other two tasks, then all of that again for the other two locations. (then a whole load of columns to break those hours out dependant on the month the hours were worked, for reporting purposes - but that's a whole different headache!!)
As I say, it's untidy and most likely overly complicated, but it worked.
Now though, there have been some changes to the structures and we will have 6 grades of staff with 2 additional locations and perhaps further tasks with these broken down to a more granular level.
Is there any simple way of looking at three different fields 'Grade' 'Location' 'Task' and pulling the cost that should be associated with the three when combined?
Thanks all