Table with formulas

[Peterino]

Hi

In the table I only want Col B available for input so, I first unlocked the sheet then locked Cols C- F and then protected the workbook. No password.

The problem is that when the sheet is protected the formulas in Cols C - F don't work. If unprotected it works.

Is this fixable?

Thank You. Peter

[jeffreybrown]

Hi Peter,

All worked fine for me. I made sure all was locked except for column B, made a change to cell B5 and and formulas adjusted.

[Peterino]

Unprotected Unprotected.PNG

Protected Protected.PNG

[jeffreybrown]

Check the calculation of the worksheet. Make sure it's not set to manual.

Formulas >> Calculation >> Calculation Options >> Automatic

[jeffreybrown]

Sorry about that. I see what you mean now. Not sure the solution, but a quick google gave me this option.

https://excel.uservoice.com/forums/3...heets-add-rows

OR

http://www.excel-first.com/autoexpan...tected-sheets/

[Peterino]

Wow! It looks like maybe it isn't possible to have working formulas in a table if the sheet is protected. This can't be.

[jeffreybrown]

Maybe an option then is to convert the table to a range and build it out to a distant row, say 1000.

Now on your formulas, base them off of column B.

Example for column C. =IF(N(B4),B4*1.29,"")

Now if there isn't a number in B4, the formula returns nothing. If there is a number, you get the calculation.

All of your formulas are pretty benign, so going all the way down to row 1000 will not cause any performance lag.