Trying to calculate tasks in Excel

Question

Hello, I was going to ask my question over at that "Experts" place, but they wanted me to pay $12.95! Fo that price I could pay some college wiz kid to do this for me, and have it done quicker!

Ok, this should be an easy one, but for some reason my brain is freezing up when I try to do it.

I have a spreadsheet with three sheets in it. The first has the tasks completed in it:

Created Time           CreatedUserName ServiceDetailName            BillingCnt
7/1/2010 6:25:17 AM Employee Name   Tape Management                30
7/1/2010 8:15:33 AM Employee Name   Information Request Detailed    1
7/1/2010 8:18:24 AM Employee Name   Hard drive                      1
7/1/2010 8:25:42 AM Employee Name   Information Request Brief       2
7/1/2010 10:33:17 AM   Employee Name    Scratch Tape Count             2
7/1/2010 2:07:59 PM Employee Name   Hard drive                      1
7/1/2010 2:18:36 PM Employee Name   I/O module                      1

The next 2 pages have information regarding the value of each task as well as internal descriptions:

Task Type     Task Detail  Task Description  Unit of Measure (in Minutes)
INC            Hard Drive       Swap Drive       60
MAC            Tapes Managment  Change Tapes     1

There are about 30 tasks per page. I want to somehow take the billing count from page one and multiply it by the unit of measure. Then I have a cell set up where I want the total displayed in total minutes then another cell where it is in hours and minutes.

I have a sample spreadsheet but I can't see any place to attach it.

Answer 1

hmm ... not really a very normalized design ... I would expect that at least one column in sheet 1 should match one column in sheet 2

Either introduce Task Type in sheet 1 or match [Sheet1].ServiceDetailName with [Sheet2].TaskDetail - that's the closest match I could find, though in [1] you write "Tape Management" whereas in [2] you write "Tape*s* Management". As long as the input data stays that way the best is to give it to an assistant ;-)

so here's my suggestion:

• one master table of task types (like your [2])
• create a named range across [2].TaskDetails (except header) and use this range as cell validation in [1].ServiceDetailName. Don't allow manual input
• create another named range across [2].TaskDetails expanded to include column "UOM"
• in [1] use a =VLOOKUP() function to match the UOM into the detail table
• in a further column multiply BillingCnt times UOM to get minutes
• to display minutes in hh:mm in the next column you divide minutes by 1440 (60*24) to get "fraction of day" and format it with custom string "[h]:mm"

Hope that helps

Good luck - MikeD

-