Sum Function error with re-located sheets in workbook.

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.

Welcome to the forum.

The easy way is to insert two blank bookend sheets (say, Left and Right) and use =sum(Left:Right!D3)

Uninitiated novices shouldn't be making changes to workbooks when errors can have DISASTROUS consequences.

Your admonition about uninitiated novices is both unseemly and unhelpful.

lrollo, you brought them into the conversation.

EDIT: It ocurrs to me that you took the remark as directed at you; it wasn't. I assumed your reference was to other people who use workbooks you create. Please accept my apology for speaking carelessly.

Novices are entitled to expect that correctly entered formula work as intended ...

And they will be fully rewarded by Excel in that regard.

... and are not going to be prostituted by undisclosed quirks of the program. I regard this quirk as a program failure.

One man's quirk is another's intent, and the behavior is 'undisclosed' only to those who choose not to learn how the program works.

Worksheet and workbook protection offers a means of reducing the likelihood of people who don't know what they're doing causing problems.