date from multiple worksheets
 

Each day, I imput a new month-date-report in to a new worksheet, named as the
day of the month correponding to the data thus far. For instance, if
tomorrow is 10-17-07, then the tab for the worksheet is named "7".

I would like to create a summary page that will track the new monthly total,
which is in the same location on the same page. I have no problem writing
the search function to find the data I want, but I would like to avoid having
to edit each formula, ie "=sumif('1'!E:E . . ." "=sumif('2'!E:E . . ." all
the way to "sumif('31'!E:E . . ."

Is there anyway to set an array over multiple worksheets or to draft the
formula in such a way that the sheet name will automatically grow when doing
a fill?

date from multiple worksheets
 

"=sumif('1'!E:E . . ."

In the start cell, replace the above
with this equivalent construct using INDIRECT & ROWS:
=sumif(indirect("'"&rows($1:1)&"'!E:E"),...
Copy down 31 rows. The ROWS part will increment to return the required
sheetnames: 1,2,3... as you copy down.

If you are copying it across from the start cell,
replace ROWS($1:1) in the construct with:

COLUMNS($A:A)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jdvanhemert" wrote:
Each day, I imput a new month-date-report in to a new worksheet, named as the
day of the month correponding to the data thus far. For instance, if
tomorrow is 10-17-07, then the tab for the worksheet is named "7".

I would like to create a summary page that will track the new monthly total,
which is in the same location on the same page. I have no problem writing
the search function to find the data I want, but I would like to avoid having
to edit each formula, ie "=sumif('1'!E:E . . ." "=sumif('2'!E:E . . ." all
the way to "sumif('31'!E:E . . ."

Is there anyway to set an array over multiple worksheets or to draft the
formula in such a way that the sheet name will automatically grow when doing
a fill?

date from multiple worksheets
 

Thank you Max - that was brilliant and a huge future time saver!

"Max" wrote:

"=sumif('1'!E:E . . ."

In the start cell, replace the above
with this equivalent construct using INDIRECT & ROWS:
=sumif(indirect("'"&rows($1:1)&"'!E:E"),...
Copy down 31 rows. The ROWS part will increment to return the required
sheetnames: 1,2,3... as you copy down.

If you are copying it across from the start cell,
replace ROWS($1:1) in the construct with:

COLUMNS($A:A)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jdvanhemert" wrote:
Each day, I imput a new month-date-report in to a new worksheet, named as the
day of the month correponding to the data thus far. For instance, if
tomorrow is 10-17-07, then the tab for the worksheet is named "7".

I would like to create a summary page that will track the new monthly total,
which is in the same location on the same page. I have no problem writing
the search function to find the data I want, but I would like to avoid having
to edit each formula, ie "=sumif('1'!E:E . . ." "=sumif('2'!E:E . . ." all
the way to "sumif('31'!E:E . . ."

Is there anyway to set an array over multiple worksheets or to draft the
formula in such a way that the sheet name will automatically grow when doing
a fill?

date from multiple worksheets
 

welcome, glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"jdvanhemert" wrote in message
...
Thank you Max - that was brilliant and a huge future time saver!