You would have two worksheets:
-- the Active sheet, in which your data is entered and contains your dependant lists
-- the Shadow sheet, which has a formatted values-only (no formulas) shadow of all your data in the Active sheet
If you compared the two sheets, you would see exactly the same thing. But the Shadow sheet does not contain any of the formulas, dependant list drop-downs, etc that the Active sheet does.
Select your range on the Shadow sheet and create the table.
In the code module for the Active sheet, insert this code:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim wksAct As Worksheet 'Active sheet for data entry
Dim wksShd As Worksheet 'Shadow sheet, containing a copy of the data
Dim rwAct As Long 'row being used on Active sheet
Dim rwShd As Long 'row to write in on Shadow sheet
Dim x As Long 'generic counter
Dim varXLcalc 'variable to hold XL calculation state
'Set calculation to Manual to save lots of time
Set varXLcalc = Application.Calculation
Application.Calculation = xlCalculationManual
'Set sheet objects
Set wksAct = ActiveSheet
Set wksShd = ActiveWorkbook.Worksheets("Sheet2")
'Get row numbers
rwAct = Target.Row
rwShd = wksShd.Range("A100000").End(xlUp).Row + 1
'Write data
For x = 1 To 7 'I just went to 7 columns
wksShd.Cells(rwShd, x).Value = wksAct.Cells(rwAct, x).Value
Next x
'Reset XL calculation state
Application.Calculation = varXLcalc
End Sub
Now, as you write into the Active sheet, your data values are written into your Shadow sheet, and your table in the Shadow sheet will automatically expand. Because of that, you can use that table and the data in it for charts, calculations, etc. It's the same data, but you get to have it in a table.
If you hide the Shadow sheet, you don't (usually) have to worry about protecting it. But you can still protect your Active sheet against fat-fingered data entry overwriting your formulas and such.
That may not work for you, but it's one possible solution.
Ed