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

[Martinweber77411]

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.

[#2]

@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.