Lock location of sheet within a spreadsheet

[PJ]

Is there a way to lock the location of a sheet within a spreadsheet? I have
a spreadsheet that has a sheet for each employee. Then I have a Total sheet
that totals across all the sheets. For example, the Total sheet adds cell C3
on Sheet 1 plus C3 on Sheet 2 plus C3 on Sheet 3, etc. I want to lock Sheet
1 so it can't be moved because the formula will not work if the sheets are
rearranged. See formula below.

=SUM('Sheet1:Sheet10'!C3)

 

[Dave Peterson]

You could add two "dummy" sheets--one to the far left and one to the far right.

Call them Start and Finish.

Then your formula would become:

=sum('start:finish'!c3)

I would lock the cells on those sheets and protect them. You don't want someone
putting something in one of the cells that breaks the formulas.

After you do this, you can play what-if games, too. Just drag a worksheet
outside that "sandwich" and that employee's numbers will be ignored.

Remember to have the total sheet outside the sandwich, too.

 

[Jim Thomlinson]

There is no way to lock the position of a sheet that I know of. Generally
when I want to use a formula such as that I will create 2 blank worksheets.
One called Start and one called end. I place them at either end of my source
sheets. I then use formulas like

=SUM('Start:End'!C3)

Not fool proof but it helps keep end users from moving the end sheets...

 

[Dave Peterson]

You could protect the workbook's structure.
In xl2003's menus:
tools|protection|protect workbook|and check structure

But this means that sheets can't be deleted, added, moved, renamed without
unprotecting the workbook first.

 

[PJ]

Sounds like a good workaround. Thanks for the tip.