Formula from multiple worksheets

[beirrac]

I'm trying to add totals from multiple worksheets to a total page worksheet. It is just a basic autosum of 4 numbers, each representing a quarter of the year. I build the formula as =SUM(Sheet1:Sheet4!A2) and get the correct total. Let's say the total is 50. Then, I try to drag the formula (and I tried copy and paste formula only) to cell A3 on the total page worksheet. The formula will show =SUM(Sheet1:Sheet4!A3) BUT the total shown is 50, which is the total from A2. Same happens if I drag down to capture the numbers from A3, A4, etc. The formula looks correct but the total is always A2. I've done this before but on this worksheet it doesn't seem to work. Any ideas?

 

[T. Valko]

I'm trying to add totals from multiple worksheets to a total page worksheet. It is just a basic autosum of 4 numbers, each representing a quarter of the year. I build the formula as =SUM(Sheet1:Sheet4!A2) and get the correct total. Let's say the total is 50. Then, I try to drag the formula (and I tried copy and paste formula only) to cell A3 on the total page worksheet. The formula will show =SUM(Sheet1:Sheet4!A3) BUT the total shown is 50, which is the total from A2. Same happens if I drag down to capture the numbers from A3, A4, etc. The formula looks correct but the total is always A2. I've done this before but on this worksheet it doesn't seem to work. Any ideas?

Make sure calculation is set to automatic.

Excel versions 2003 and earlier:

Tools>Options>Calculation tab>Automatic>OK

Excel versions 2007 and later:

Formulas tab>Calculation>Calculation Options>Automatic

 

[Dryver14]

what result do you get if you type

=SUM(Sheet1:Sheet4!A3) ?

your doing nothing wrong there

 

[beirrac]

It was selecting automatic under calculation options, it had been set on manual. I could do them individually but just could not do a copy/drag and paste to have the correct total. Thank you so much for your help.

 

[T. Valko]

It was selecting automatic under calculation options, it had been set on manual. I could do them individually but just could not do a copy/drag and paste to have the correct total. Thank you so much for your help.

You're welcome. Thanks for the feedback!