formula for mulitple worksheets

Phylis Sophical · Jan 14th, 2002, 06:37 PM

So happy I have found this board. Here's the problem.
I have 20+ sheets in a workbook. The first one is a summary sheet. I am trying to
put a formula in the summary sheet to add up the totals of a particular cell in all
the other sheets. (cell C47 of each sheet)
I have tried =, then go to each sheet, click the cell and a plus sign, then Enter.

I have tried =, then selecting all the sheets together, clicking in C47 and clicking
Enter.

I have tried linking one cell on the summary sheet to C47 of sheet1, then going to
the next cell and repeating for sheet2 and so on. Then simply adding those cells
up =SUM(F44:AJ44). I did this in a row, not a column, does that make a
difference?

When I first enter the formulas, the result carries into the summary sheet. But if I
go to a new sheet and put in a total amount, or change the amount on any of the
other sheets, it won't show in the summary sheet.
I have the sheets protected except for the cells I enter the total amounts in. Is this
making a difference? Some of the sheet names are two words, but they do have
colons on either side in the formula.

Thanks in advance for any help.

Phylis Sophical · Apr 14th, 2002, 08:58 PM

Thanks Mr.E, I found my message on the second to the last page. I'm just going to throw this message in to try to bump it back to the top of the lot.

SamS · Apr 14th, 2002, 09:15 PM

Phylis - Your first option should work, is your options set to Manual Calculation. If your status bar is on at the bottom left of the sheet the word CALCULATE will appear. If this is the case go to Tools, Options, Calculation and change to Automatic.

Phylis Sophical · Apr 14th, 2002, 09:21 PM

Oh for Pete's sake. That was exactly it. That's what I get for fooling around on the Calculation tab. Thanks Sam.

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

Jan 14th, 2002, 06:37 PM	#1
Phylis Sophical New Member Join Date: Jan 2002 Location: Canada Posts: 42	So happy I have found this board. Here's the problem. I have 20+ sheets in a workbook. The first one is a summary sheet. I am trying to put a formula in the summary sheet to add up the totals of a particular cell in all the other sheets. (cell C47 of each sheet) I have tried =, then go to each sheet, click the cell and a plus sign, then Enter. I have tried =, then selecting all the sheets together, clicking in C47 and clicking Enter. I have tried linking one cell on the summary sheet to C47 of sheet1, then going to the next cell and repeating for sheet2 and so on. Then simply adding those cells up =SUM(F44:AJ44). I did this in a row, not a column, does that make a difference? When I first enter the formulas, the result carries into the summary sheet. But if I go to a new sheet and put in a total amount, or change the amount on any of the other sheets, it won't show in the summary sheet. I have the sheets protected except for the cells I enter the total amounts in. Is this making a difference? Some of the sheet names are two words, but they do have colons on either side in the formula. Thanks in advance for any help.

Apr 14th, 2002, 08:58 PM	#2
Phylis Sophical New Member Join Date: Jan 2002 Location: Canada Posts: 42	Thanks Mr.E, I found my message on the second to the last page. I'm just going to throw this message in to try to bump it back to the top of the lot.

Apr 14th, 2002, 09:15 PM	#3
SamS Board Regular Join Date: Feb 2002 Location: Brisbane, Down Under Posts: 533	Phylis - Your first option should work, is your options set to Manual Calculation. If your status bar is on at the bottom left of the sheet the word CALCULATE will appear. If this is the case go to Tools, Options, Calculation and change to Automatic.

Apr 14th, 2002, 09:21 PM	#4
Phylis Sophical New Member Join Date: Jan 2002 Location: Canada Posts: 42	Oh for Pete's sake. That was exactly it. That's what I get for fooling around on the Calculation tab. Thanks Sam.