Spreadsheet with many sheets, linking for a summary sheet - issue

[tryinghard]

I am putting together a spreadsheet which has 27 sheets of the same template feeding into a summary sheet. However, the summary sheet will not automatically update when one of the input sheets is updated. I have the autocalc on, and have pressed F9. The only way to get it to update is to manually go into the cell (F2), and hit enter. Then it calculates.

Once I have done it once for that cell, it works no problem, until I close and reopen the file. However, I do not want to keep going into every cell on the summary every time I open the spreadsheet, to ensure it recalculates.

Has anyone else had this problem? I am losing trust in this spreadsheet, but need it to calc all the underlying sheets.

[Trebor76]

Hi there,

I once had a similar problem but it was due to a macro running faster than excel could calculate. See if these tips help (especially the keystrokes to invoke a full calculation).

HTH

Robert

[robert111]

Don't know why you have a problem, but the formula below works fine.

in A1 of sheet 1 put


=SUMPRODUCT((Sheet2!$A$1)*1)+SUMPRODUCT((Sheet3!$A$1)*1)

and build it up for as many sheets as you have. Take off the dollar signs and drag it down and across as required.

Bob