Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
Lookup date on multiple worksheets, total amts for adjacent colmn | Excel Discussion (Misc queries) | |||
MIN date from multiple different worksheets in a workbook | Excel Worksheet Functions | |||
opening multiple files into one workbook, but multiple worksheets.... | Excel Discussion (Misc queries) | |||
Transferring date from multiple worksheets to a "totals" worksheet. | Excel Discussion (Misc queries) |