In Excel 2007 I have a formatted table, 10 columns wide, in which 5 of the
columns have formulas.

There is a nice feature in Excel 2007 that allows me to type a value in a
cell which is directly beneath the table and the table then expands to
€œconsume€� that value and automatically fills the remaining columns in the
table with, where appropriate, the formulas.

I want to protect the table so that the formulas are not inadvertently
overwritten AND take advantage of this feature.

However, if I lock and hide the cells, then Protect the worksheet, this
feature is disabled.

Can anyone help?

Hi Hugh

The only way to do this is to resize your table first.
Click within tableDesign tabResizemake the table as large as you are
likely to need.
Select the cells where you want to enter dataFormatProtectionremove
Locked.
Right click on sheet tabProtect sheet.

The disadvantage of course is that if you are using the table as a
source for other things e.e Pivot Table, then the range will contain
lots of blank rows.

The only other way is via code.
The following event code was written for a Table called Table2
Data is entered in columns A and B, and columns C and D contain formulae.
You would need to amend to suit your situation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tr As Long
tr = Target.Row
If Target.Count 1 Then Exit Sub
If tr = 1 Then Exit Sub
If Target.Column < 1 Then Exit Sub
ActiveSheet.Unprotect
ActiveSheet.ListObjects("Table2").Resize Range("$A$1:$D" & tr)
Range(Cells(tr + 1, "A"), Cells(tr + 1, "B")).Locked = False
ActiveSheet.Protect
End Sub

To install
Copy code as above
Right click on sheet tabView Code
Paste code into white pane that appears
Alt+F11 to return to Excel
--
Regards
Roger Govier

Hugh wrote:
In Excel 2007 I have a formatted table, 10 columns wide, in which 5 of the
columns have formulas.

There is a nice feature in Excel 2007 that allows me to type a value in a
cell which is directly beneath the table and the table then expands to
€œconsume€� that value and automatically fills the remaining columns in the
table with, where appropriate, the formulas.

I want to protect the table so that the formulas are not inadvertently
overwritten AND take advantage of this feature.

However, if I lock and hide the cells, then Protect the worksheet, this
feature is disabled.

Can anyone help?

Thanks for the help.

It's disappointing that these two functions won't work in harmony.

I've elected to leave the formulas unprotected and, if they get overwritten
they can be repaired.

"Roger Govier" wrote:

Hi Hugh

The only way to do this is to resize your table first.
Click within tableDesign tabResizemake the table as large as you are
likely to need.
Select the cells where you want to enter dataFormatProtectionremove
Locked.
Right click on sheet tabProtect sheet.

The disadvantage of course is that if you are using the table as a
source for other things e.e Pivot Table, then the range will contain
lots of blank rows.

The only other way is via code.
The following event code was written for a Table called Table2
Data is entered in columns A and B, and columns C and D contain formulae.
You would need to amend to suit your situation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tr As Long
tr = Target.Row
If Target.Count 1 Then Exit Sub
If tr = 1 Then Exit Sub
If Target.Column < 1 Then Exit Sub
ActiveSheet.Unprotect
ActiveSheet.ListObjects("Table2").Resize Range("$A$1:$D" & tr)
Range(Cells(tr + 1, "A"), Cells(tr + 1, "B")).Locked = False
ActiveSheet.Protect
End Sub

To install
Copy code as above
Right click on sheet tabView Code
Paste code into white pane that appears
Alt+F11 to return to Excel
--
Regards
Roger Govier

Hugh wrote:
In Excel 2007 I have a formatted table, 10 columns wide, in which 5 of the
columns have formulas.

There is a nice feature in Excel 2007 that allows me to type a value in a
cell which is directly beneath the table and the table then expands to
€œconsume€� that value and automatically fills the remaining columns in the
table with, where appropriate, the formulas.

I want to protect the table so that the formulas are not inadvertently
overwritten AND take advantage of this feature.

However, if I lock and hide the cells, then Protect the worksheet, this
feature is disabled.

Can anyone help?
.