Autofilling formulas from multiple worksheets

Closed
Tom - May 20, 2010 at 04:39 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - May 21, 2010 at 12:26 PM
Hi guys. I am decent with excel, but I am trying to help a fellow employee out at the moment who has very little knowledge of excel. She is doing a sales report summary for the month. Each day of the month has its own worksheet within the Excel workbook, and there is a summary page, which I am helping her with. I am trying to use formulas pull the sales totals for each day and display them on the summary page. There are 10 sales category totals each day, so it would take me a really long time to manually link these all to the summary page. The daily category totals are in the same cell of every worksheet What I'm wondering is:

Is there a way for me to enter the formula for the first day of the month linking it to the category total in the worksheet for the first day of the month, and then somehow autofill it down for the rest of the month so that the formula automatically adjusts? For example: ='1st'!L9, ='2nd'!L9, -'3rd'!L9, ='4th'!L9, etc. so that I (or really my co-worker) doesn't have to manually link each total to the front page.

3 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 20, 2010 at 05:30 PM
Without seeing the layout, I can only suggest that you make use of indirect and string manipulation to accomplish that

Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee.
0
https://authentification.site/files/22553601/sample_sales_workbook.xlsx

There is what I am working with. I have only included days 3-7 of the month and filled in a few of the "phone" and "web" categories on the front page using basic linking, which is very tedious. I am wondering, since, for example, the web total is in cell L8 on every daily sales worksheet, if I can somehow only link, say the first day of the month to that cell, and then use some sort of autofill to automatically link days 2-31 to the same total in their respective worksheets within this workbook.

FYI I deleted most of the other data and changed the product names to "item 1" and "item 2" for confidentiality reasons. I know this worksheet looks very incomplete

Thanks
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 21, 2010 at 12:26 PM
Try this

This is for cells E2 on sheet Month Total

=IF(ISERROR(INDIRECT(ADDRESS(9,12,1,,A2))), "", INDIRECT(ADDRESS(9,12,1,,A2)))
or
=IF(ISERROR(INDIRECT(A2 & "!L9")), "",INDIRECT(A2 & "!L9"))

This is for b2
=IF(ISERROR(INDIRECT(ADDRESS(8,12,1,,A2))), "", INDIRECT(ADDRESS(8,12,1,,A2)))
or
=IF(ISERROR(INDIRECT(A2 & "!L8")), "",INDIRECT(A2 & "!L8"))

Drag it down till 31
0