Hi,
I'm trying to create a timesheet in excel so I can get a better handle on how many hours each of my employees are spending on a particular client's business. Each employee has a worksheet within the overall workbook and they then select from a drop down list on an hourly basis the client they have been focused on. The drop down list is updated as and when new clients / projects commence.
On a worksheet at the back of this workbook I am trying to tally up the hours spent against each client by week. This involves a formula which needs to pick up the occurance of certain words across certain ranges of cells. Here is an example of the formula I am using to pick up work on one particular client across the course of a week ("Rumble" is the word I am looking to pick up).
=SUM(LEN(Anna!B2:H25)-LEN(SUBSTITUTE(Anna!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Bob!B2:H25)-LEN(SUBSTITUTE(Bob!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Dionne!B2:H25)-LEN(SUBSTITUTE(Dionne!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Emily!B2:H25)-LEN(SUBSTITUTE(Emily!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Helen!B2:H25)-LEN(SUBSTITUTE(Helen!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(James!B2:H25)-LEN(SUBSTITUTE(James!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Martin!B2:H25)-LEN(SUBSTITUTE(Martin!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Ric!B2:H25)-LEN(SUBSTITUTE(Ric!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Simon!B2:H25)-LEN(SUBSTITUTE(Simon!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Will!B2:H25)-LEN(SUBSTITUTE(Will!B2:B25,"Rumbles","")))/LEN("Rumbles")
This is being entered as an array formula.
The problem is that this is working for "Rumble" but when I try to copy the formula into other cells and try to get it to search for other words it no longer does.
Is this the right forumla to be replicating? Is there an easier way?
Thank you for your help in advance.
Will
I'm trying to create a timesheet in excel so I can get a better handle on how many hours each of my employees are spending on a particular client's business. Each employee has a worksheet within the overall workbook and they then select from a drop down list on an hourly basis the client they have been focused on. The drop down list is updated as and when new clients / projects commence.
On a worksheet at the back of this workbook I am trying to tally up the hours spent against each client by week. This involves a formula which needs to pick up the occurance of certain words across certain ranges of cells. Here is an example of the formula I am using to pick up work on one particular client across the course of a week ("Rumble" is the word I am looking to pick up).
=SUM(LEN(Anna!B2:H25)-LEN(SUBSTITUTE(Anna!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Bob!B2:H25)-LEN(SUBSTITUTE(Bob!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Dionne!B2:H25)-LEN(SUBSTITUTE(Dionne!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Emily!B2:H25)-LEN(SUBSTITUTE(Emily!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Helen!B2:H25)-LEN(SUBSTITUTE(Helen!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(James!B2:H25)-LEN(SUBSTITUTE(James!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Martin!B2:H25)-LEN(SUBSTITUTE(Martin!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Ric!B2:H25)-LEN(SUBSTITUTE(Ric!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Simon!B2:H25)-LEN(SUBSTITUTE(Simon!B2:B25,"Rumbles","")))/LEN("Rumbles")+SUM(LEN(Will!B2:H25)-LEN(SUBSTITUTE(Will!B2:B25,"Rumbles","")))/LEN("Rumbles")
This is being entered as an array formula.
The problem is that this is working for "Rumble" but when I try to copy the formula into other cells and try to get it to search for other words it no longer does.
Is this the right forumla to be replicating? Is there an easier way?
Thank you for your help in advance.
Will
