Sum Function error with re-located sheets in workbook.

[lrollo]

In a spreadsheet there are potential differences between using two forms of aggregation formula:

='BLD 1'!D3+'BLD 2'!D3+'BLD 3'!D3 ... +'BLD 11'!D3 created by selecting the nominated cell in successive tabbed sheets

or

=SUM('BLD 1 :BLD 11!D3') created types =Sum( in the target cell and selecting the nominated cell in the first sheet in the series, and then, holding down the Shift key, selecting the nominated cell in the last sheet in the series, adding the cell reference to the formula and closing the parentheses.

The first form will always total the values in ALL the nominated cells. But creating the formula in workbooks with many sheets can be a pain.

In the second form, the total may not be the same IF there are any changes to the order in which the sheets series was created.

The second form of the formula depends on the internal sheet numbers of the series assigned as the sheets are created.

If Sheet 11 is moved to precede Sheet 9, the second form will NOT include Sheet 9 or Sheet 10 as the formula recognised the internal sheet No. 11 as the last item in the series.

THIS HAS A REAL POTENTIAL FOR DISASTER, particularly where there is no obvious way of cross checking the integrity of the summation.

As this circumstance must have occurred many times, I suspect there is a work around or some other procedure to eliminate this potential hazard is spreadsheets. IT is a TRAP for the innocent who assume the formula will do as apparently instructed.

You can view the internal names by opening the VBA editor and looking in the properties box. This will immediately disclose any change in order. Re-locating the tabbed sheets into the original order will solve the problem. But HOW DO YOU KNOW a problem exists?

Suggestion or solutions would be welcomed.