adding sheets which add to master sheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1623271%22%20slang%3D%22en-US%22%3Eadding%20sheets%20which%20add%20to%20master%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1623271%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20told%20that%20I%20could%20add%20sheets%20in%20a%20way%20that%20would%20automatically%20be%20included%20in%20a%20formula%20on%20the%201st%20sheet%20to%20recap%20all%20of%20the%20other%20sheets.%26nbsp%3B%20We%20track%20jobs%20daily%20on%20sheets%20which%20all%20match%20exactly.%26nbsp%3B%20We%20then%20add%20up%20the%20same%20total%20cell%20in%20each%20sheet%20to%20a%20master%20invoice%20-%20sheet%201.%26nbsp%3B%20Before%20I%20do%20this%20manually%20I%20thought%20I'd%20put%20it%20out%20there%20in%20case%20there%20is%20a%20way%20to%20add%20a%20sheet%20and%20not%20have%20to%20change%20every%20formula%20on%20the%20master%20to%20include%20the%20new%20sheets.%26nbsp%3B%20I've%20never%20heard%20of%20this%20and%20have%20used%20excel%20a%20lot%20over%20the%20years%20with%20some%20extensive%20worksheets.%26nbsp%3B%20Although%20I've%20not%20used%20a%20worksheet%20like%20this%20client%20has%20set%20up%20so%20it's%20certainly%20possible%20that%20what%20I'm%20asking%20about%20is%20possible!%3C%2FP%3E%3CP%3EThanks%20for%20any%20feedback!%3C%2FP%3E%3CP%3EEd%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1623271%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1623581%22%20slang%3D%22en-US%22%3ERe%3A%20adding%20sheets%20which%20add%20to%20master%20sheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1623581%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F777068%22%20target%3D%22_blank%22%3E%40TheNov%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInsert%20a%20blank%20new%20sheet%20to%20the%20left%20of%20the%20first%20sheet%20that%20you%20want%20to%20include%20in%20the%20total%2C%20and%20name%20it%20First.%3C%2FP%3E%0A%3CP%3EInsert%20a%20blank%20new%20sheet%20to%20the%20rightt%20of%20the%20last%20sheet%20that%20you%20want%20to%20include%20in%20the%20total%2C%20and%20name%20it%20Last.%3C%2FP%3E%0A%3CP%3EThe%20First%20and%20Last%20sheets%20will%20remain%20blank.%3C%2FP%3E%0A%3CP%3EUse%20formulas%20like%20this%20on%20the%20master%20sheet%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSUM(First%3ALast!B2)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20add%20a%20new%20sheet%20to%20be%20included%20in%20the%20total%2C%20insert%20it%20between%20the%20First%20and%20Last%20sheets.%20The%20SUM%20formulas%20will%20automatically%20include%20the%20new%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I was told that I could add sheets in a way that would automatically be included in a formula on the 1st sheet to recap all of the other sheets.  We track jobs daily on sheets which all match exactly.  We then add up the same total cell in each sheet to a master invoice - sheet 1.  Before I do this manually I thought I'd put it out there in case there is a way to add a sheet and not have to change every formula on the master to include the new sheets.  I've never heard of this and have used excel a lot over the years with some extensive worksheets.  Although I've not used a worksheet like this client has set up so it's certainly possible that what I'm asking about is possible!

Thanks for any feedback!

Ed

1 Reply

@TheNov 

Insert a blank new sheet to the left of the first sheet that you want to include in the total, and name it First.

Insert a blank new sheet to the rightt of the last sheet that you want to include in the total, and name it Last.

The First and Last sheets will remain blank.

Use formulas like this on the master sheet:

 

=SUM(First:Last!B2)

 

If you want to add a new sheet to be included in the total, insert it between the First and Last sheets. The SUM formulas will automatically include the new sheet.