How to find source cells that make up a numeric sum?

%3CLINGO-SUB%20id%3D%22lingo-sub-2360148%22%20slang%3D%22en-US%22%3EHow%20to%20find%20source%20cells%20that%20make%20up%20a%20numeric%20sum%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360148%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20have%20a%20workbook%20that%20has%20~20%20data%20sheets%20over%20which%20numeric%20values%20are%20scattered.%20In%20addition%2C%20there's%20an%20overview%20sheet%20that%20sums%20up%20values%20for%20rows%20across%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DSUM('First%20Datasheet%3ALast%20Datasheet'!D2)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20nicely%20sums%20up%20the%20values%20in%20each%20D2%20cell%20across%20all%20the%20data%20sheets%2C%20e.g%2010.%20It's%20a%20PITA%20however%20to%20find%20out%20from%20which%20of%20the%20sheets%20these%20values%20come%20from%20(e.g.%205%20from%20First%20Datasheets%20D2%2C%202%20from%20Second%20Datasheets%20D2%20and%203%20from%20Last%20Datasheets%20D2)%20.%20In%20order%20to%20do%20this%2C%20one%20has%20to%20go%20through%20all%20data%20sheets.%3C%2FP%3E%3CP%3EI%20wonder%20if%20there's%20an%20easier%20solution%20to%20reveal%20from%20which%20cells%20the%20sum%20is%20being%20calculated%20(0s%20shouldn't%20count).%20Thanks.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2360148%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-2360557%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20find%20source%20cells%20that%20make%20up%20a%20numeric%20sum%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2360557%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1055606%22%20target%3D%22_blank%22%3E%40Martinweber77411%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou'd%20have%20to%20change%20the%20formula%20to%20something%20like%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSheet1!D2%2BSheet2!D2%2B...%2BSheet20!D2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20use%20Evaluate%20Formula%20on%20the%20Formulas%20tab%20of%20the%20ribbon%20to%20inspect%20each%20value.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOr%20create%20a%20series%20of%20link%20formulas%20on%20the%20overview%20sheet%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DSheet1!D2%3C%2FP%3E%0A%3CP%3E%3DSheet2!D2%3C%2FP%3E%0A%3CP%3E...%3C%2FP%3E%0A%3CP%3E%3DSheet20!D2%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPlus%20a%20formula%20that%20sums%20these%20cells.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

We have a workbook that has ~20 data sheets over which numeric values are scattered. In addition, there's an overview sheet that sums up values for rows across:

 

=SUM('First Datasheet:Last Datasheet'!D2)

 

This nicely sums up the values in each D2 cell across all the data sheets, e.g 10. It's a PITA however to find out from which of the sheets these values come from (e.g. 5 from First Datasheets D2, 2 from Second Datasheets D2 and 3 from Last Datasheets D2) . In order to do this, one has to go through all data sheets.

I wonder if there's an easier solution to reveal from which cells the sum is being calculated (0s shouldn't count). Thanks.

1 Reply

@Martinweber77411 

You'd have to change the formula to something like

 

=Sheet1!D2+Sheet2!D2+...+Sheet20!D2

 

You can then use Evaluate Formula on the Formulas tab of the ribbon to inspect each value.

 

Or create a series of link formulas on the overview sheet:

 

=Sheet1!D2

=Sheet2!D2

...

=Sheet20!D2

 

Plus a formula that sums these cells.