multiple worksheets consolidated report

stargazer · Apr 2nd, 2002, 01:41 PM

I would appreciate learning how to construct a formula or function that would allow me to create, on a summary worksheet, a SUM total of cell entries on 40 plus worksheets in the same workbook, which entries are all in identical positions on the sheets. So I'd like to retrieve and SUM, for example, all 40 cell B24s, into a cell on the summary sheet. The need arises since the 40 worksheets were originals produced without the now needed category as part of the summary sheet. I'd like to avoid having to do the tedious job of creating a new report column of all the needed data, by going back and forth, summary to individual sheets to create cell reference formulas. Thanks in advance for advice.

Mark W. · Apr 2nd, 2002, 01:53 PM

See the Excel Help topic for "Refer to the same cell or range on multiple sheets by using a 3-D reference".

stargazer · Apr 3rd, 2002, 06:25 AM

Mark, Thanks for the information. That does the job. I appreciate the quick help! Bob

nisht · Apr 3rd, 2002, 06:40 AM

If you can change little of your logic for what you are doing I think you can change lot of your time and energy.

I had one question from friend like you and i have suggest him to use autofilter and then link the last cell to give you the results.

Here you can even avoid using number of workseets... i think you can just use one main worksheet as data and then get customise reports.

goto my site http://www.pexcel.com/guestdownload.htm

here you can download file 'Daily Total'

this is for some fire station.

if you have any difficulty write back to me

nishith desai
nisht@pexcel.com
http://www.pexcel.com

stargazer · Apr 3rd, 2002, 07:18 AM

Thanks for the input. My worksheets, each pertaining to an individual employee, have quite a lot of data, with 12 or so different kinds of information on them, so don't seem to be small enough to have several on one sheet. But I'm happy to try other structures, since I am sure there are more efficient ways to do what I am trying to do. I am no expert at Excel, though definitely want to learn more. Regards, Bob

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 2nd, 2002, 01:41 PM	#1
stargazer New Member Join Date: Mar 2002 Location: Weathersfield,VT Posts: 18	I would appreciate learning how to construct a formula or function that would allow me to create, on a summary worksheet, a SUM total of cell entries on 40 plus worksheets in the same workbook, which entries are all in identical positions on the sheets. So I'd like to retrieve and SUM, for example, all 40 cell B24s, into a cell on the summary sheet. The need arises since the 40 worksheets were originals produced without the now needed category as part of the summary sheet. I'd like to avoid having to do the tedious job of creating a new report column of all the needed data, by going back and forth, summary to individual sheets to create cell reference formulas. Thanks in advance for advice.

Apr 2nd, 2002, 01:53 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	See the Excel Help topic for "Refer to the same cell or range on multiple sheets by using a 3-D reference".

Apr 3rd, 2002, 06:25 AM	#3
stargazer New Member Join Date: Mar 2002 Location: Weathersfield,VT Posts: 18	Mark, Thanks for the information. That does the job. I appreciate the quick help! Bob

Apr 3rd, 2002, 06:40 AM	#4
nisht Board Regular Join Date: Feb 2002 Location: Ahmedabad Gujarat Posts: 303	If you can change little of your logic for what you are doing I think you can change lot of your time and energy. I had one question from friend like you and i have suggest him to use autofilter and then link the last cell to give you the results. Here you can even avoid using number of workseets... i think you can just use one main worksheet as data and then get customise reports. goto my site http://www.pexcel.com/guestdownload.htm here you can download file 'Daily Total' this is for some fire station. if you have any difficulty write back to me nishith desai nisht@pexcel.com http://www.pexcel.com

Apr 3rd, 2002, 07:18 AM	#5
stargazer New Member Join Date: Mar 2002 Location: Weathersfield,VT Posts: 18	Thanks for the input. My worksheets, each pertaining to an individual employee, have quite a lot of data, with 12 or so different kinds of information on them, so don't seem to be small enough to have several on one sheet. But I'm happy to try other structures, since I am sure there are more efficient ways to do what I am trying to do. I am no expert at Excel, though definitely want to learn more. Regards, Bob