myfathersson
New Member
- Joined
- May 4, 2010
- Messages
- 22
Hi.
I'm trying to come up with a formula that will allow me to sum values across multiple sheets where the sheet name is referenced by a wildcard or some such thing.
Situation:
I have a spreadsheet that is comprised of a summary sheet and a number of sheets each containing the timesheet for a given staff member. In the summary sheet there is a list of staff rate categories. The sheet totals the number of days worked for all staff in a given category, multiplies the total by the category rate giving the total cost for the category. Currently the timesheet sheets are named by the staff name (e.g., JBloggins) with the sheets pertaining to a specific category grouped together. The summary sheet formula looks like this:
where the timesheet sheet for JBloggins is the first in the category set and ZSmith is the last. The formula totals all the values in cell K22 from sheet JBoggins to ZSmith inclusive. The problem is when resources come and go we have to insert/remove sheets from the appropriate category sheet set and update the formula, which is a pain.
Requirement:
I'd like to be able to do something like put the category name in the sheet name (e.g, Developer - JBloggins) and build a formula that sums the days for the category something like the following (understanding that the formula below would not work but it gives you the idea):
This way it wouldn't matter where a given timesheet occurs in the workbook. As long as the category name used in the sheet name is correct, the formula would total all sheets for the given category. I could add and remove timesheet sheets without concern that the formula has to be updated or the sheets are in the correct order.
I know I could do it using VBA but I'd rather not if I can avoid it.
Any ideas?
Peter
I'm trying to come up with a formula that will allow me to sum values across multiple sheets where the sheet name is referenced by a wildcard or some such thing.
Situation:
I have a spreadsheet that is comprised of a summary sheet and a number of sheets each containing the timesheet for a given staff member. In the summary sheet there is a list of staff rate categories. The sheet totals the number of days worked for all staff in a given category, multiplies the total by the category rate giving the total cost for the category. Currently the timesheet sheets are named by the staff name (e.g., JBloggins) with the sheets pertaining to a specific category grouped together. The summary sheet formula looks like this:
Code:
=SUM(JBloggins:ZSmith!K22)where the timesheet sheet for JBloggins is the first in the category set and ZSmith is the last. The formula totals all the values in cell K22 from sheet JBoggins to ZSmith inclusive. The problem is when resources come and go we have to insert/remove sheets from the appropriate category sheet set and update the formula, which is a pain.
Requirement:
I'd like to be able to do something like put the category name in the sheet name (e.g, Developer - JBloggins) and build a formula that sums the days for the category something like the following (understanding that the formula below would not work but it gives you the idea):
Code:
=SUM(Developer*!K22)This way it wouldn't matter where a given timesheet occurs in the workbook. As long as the category name used in the sheet name is correct, the formula would total all sheets for the given category. I could add and remove timesheet sheets without concern that the formula has to be updated or the sheets are in the correct order.
I know I could do it using VBA but I'd rather not if I can avoid it.
Any ideas?
Peter
