Want to get the totals off multiple sheets.
Posted by Curtis on July 20, 2000 8:45 AM
I have a problem. I have a workbook consisting of 2 sheets: Sheet1 and Totals. The user will key in figures on Sheet1 which are totaled at the bottom of the page. I have a button the user can click if he runs out of room on Sheet1 and have a blank copy of Sheet1 created thus making it Sheet2. So now we have 3 sheets: Sheet1, Sheet2 and Totals. Sheet2 is identical to Sheet1 except it's empty. Then the user can add figures into it(Sheet2)and they wll be sub-totaled at the bottom of it. The problem is how do I make my Totals page keep pulling the sub-totals off these sheets and totaling them on it? If I had a set number of sheets it wouldnt be hard but this workbook can sometimes only one sheet but up to 25. I want my totals sheet to pull the sub-totals off each sheet no matter how many I add. Please help. Thanks.
Curtis
Posted by Ryan on July 20, 0100 10:46 AM
Curtis,
What you can do is when the button is pushed to add the new sheet(s), code for an update of the formula's on the Total sheet. Hope this get's you in the right direction.
Ryan
Posted by Ada on July 20, 0100 8:01 PM
Curtis
If your formulas consist only of summing across the sheets, a simpler alternative is to have a blank "StartSheet" and a blank "EndSheet" with your data sheets in between them.
The formulas on your "TotalSheet" would refer to StartSheet:EndSheet.
Make sure that new data sheets are inserted between "StartSheet" and "EndSheet" and the formulas on the "TotalSheet" will automatically include them.
Ada
