Autosum button

[Robert H]

When I protect a sheet the Autosum button is disabled. I would like others
to Average entries in an unlocked column and Autosum seems to be the easiest
way for them. Is there any way to turn the Autosum back on while the sheet
is protected or an equally short way of averaging several sets of numbers in
one column? TIA

 

[Gord Dibben]

Not sure what you mean by "several sets of numbers"

Do you mean a several non-contiguous cells or ranges?

Autoaverage or autosum won't work very well on non-contiguous ranges as far as I
can see, and work differently, I might add.

Numbers in A1:A10

Select A1:A2 and A5:A6 and A9:A10 and hit autoaverage.

You will get formulas in A11:A13 each with an average of each of the above
selections of cells.

Do same with autosum and you get the sum of just the last set of cells

This macro which can be assigned to a button will autoaverage a contiguous range
of numbers and stick the average formula in the cell below the selected range
same as autoaverage.

Sub Average_Range()
ActiveSheet.Unprotect Password:="justme"
Set rng = Selection
Set rng1 = rng.Offset(rng.Rows.Count, 0).Resize(1, 1)
rng1.Formula = "=Average(" & rng.Address & ")"
ActiveSheet.Protect Password:="justme "
End Sub


Gord Dibben MS Excel MVP