Locking Cells and Tables to protect data

Question

Level 1

5 points

Locking Cells and Tables to protect data

This issue has been raised before, and judging by the number of posts on this issue, I'm wondering why Apple did not initially include, or at least now include this handy feature?

Most spreadsheets (including the Apple's Excel for Mac) have the ability to lock cells to prevent inadvertant editing of formulae and data. How else would one ensure 100% accuracy ?

I have a spreadsheet that I developed some time ago on Excel for Mac and contains data and formulae that I password protected by locking the relevant cells. I am now redevelopng it to work on the iPad, but rather dissapointed that I can't password protect "Arange/Lock" my tables. A bit more dissapointing is that the iPad version cant lock cells at all. There is a little fix by placing a rectangle of the cells and locking, but this can be unlocked ny a user.

Numbers is such a great product, and wondering why Apple is so slow in fixing this important issue ?

iMac, Mac OS X (10.6.8)

Posted on Jun 17, 2012 4:24 PM

Reply

Answer 1

Barry

Level 9

60,029 points

Jun 17, 2012 11:12 PM in response to jazzizgreat

Hi j,

Perhaps Apple does not see this as such an "important issue" that needs fixing.

Many spreadsheet applications, including MS Excel, do offer the ability to lock individual cells. But those applications produce documents consisting of a single large table, on which every cell in the document must be placed.

Numbers uses a different model: Tables are smaller, generally have a single purpose, and may be located on a number of different Sheets ('canvasses').

Implicit in that model is the ability to separate data entry from calculations by placing the two tasks onto separate sheets. Locking the table(s) containing the calculations may be used to prevent inadvertent editing of the formulas. For that matter, locking the table(s) containing the data could also be used to prevent (inadvertent) editing of the data.

And, no, password protection for that locking is not available. That was also true in iWork's predecessor, AppleWorks. Like the iWork applications, AppleWorks offered password protection at the document level, but not for anything short of that. AppleWorks's spreadsheet module, which used the large, single table model, did offer the ability to lock single cells or groups of cells (but not to assign a password to that lock/unlock operation).

And, as an aside, "Apple's Excel for Mac" is emphatically not among the spreadsheets that "have the ability to lock cells." There's a very simple reason for that: Applications that don't exist can't have that (or any) ability, and "Apple's Excel for Mac" does not exist. Excel for Mac is a Microsoft application that runs on the Mac platform.

Regards,

Barry

PS: If this is as important an issue as you make it to be, you should Provide Numbers Feedback to Apple, requesting a Feature enhancement be added to a future edition of Numbers providing this ability. Use the item of that name in the Application menu (in Numbers, the "Numbers" menu), or click the link in this message.

B

Reply

Answer 2

jazzizgreat Author

Level 1

5 points

Jun 18, 2012 3:15 AM in response to Barry

Hi Barrry

You are indeed correct, Excel for Mac is not an Apple product, and I don't know why but for some reason while posting I thought of it as an Apple product. For what it's worth, one can lock cells in Excel for Mac using a password.

I like the way Apple has modeled the Numbers tabes, very nifty, but it's still possible for user to unlock a table and inadvertantly corrupt formulae and data.

Thank you anyway for your advise and will certainly forward my request to Apple.

All the best,

André

Reply

Answer 3

Jun 19, 2012 4:39 AM in response to jazzizgreat

André.

I have adopted the attitude that Numbers isn't a collaborative app. Locking things serves to protect you from yourself, but there are many obstacles to creating a document that can be edited or "used" by another person who may not understand the construction of the document. There is still much to like about Numbers, for the document creator, but not so much for a target user. You can layer protections to create a more robust document. For instance, a Shape with Fill set to zero Opacity that overlays a table will prevent clicking into that table, but the content is still visible.

The bottom line for me is that another user, if you can find one with a Mac and Numbers available to use your document with, who is intent on altering the document, will have little trouble doing it.

Jerry

Reply

Answer 4

jazzizgreat Author

Level 1

5 points

Jun 19, 2012 6:58 PM in response to Jerrold Green1

Thank you all for the input thus far, much appreciated !

My spreadsheet application will ultimatley be used on the iPad, but I'm still having problems preserving the data in tables, even with the use of forms. Keep in mind that the users may not be familiar with spreadsheet programming.

I have one sheet (table) that displays the calculated results and I can protect formulas in this table by placing a transparent rectangle over the whole table (no direct input required to this sheet). I would prefer the rectangle to be locked in place over the table (with a password to prevent unlocking), but it can't be done on the iPad at the moment. Not too much of a problem though.

I also use forms on the iPad to input selections from the user. Predefined record and category names in the input tables are ultimately displayed in the forms which allow the user to input the correct information into the input tables. I use this information to lookup data in other tables (using VLOOKUP etc) and then calculate the final result which is displayed in a result table as mentioned above.

Problem 1:

If the user presses the delete key in the form a predetermined record is deleted and will proclude any furter input for this record. Is there any way to undelete a record ?

Problem 2:

Since the input table is used to lookup data via VLOOKUP, the lookup sequence is also corrupted.

How does one inhibit record deletion in forms?

Any other help would be appreciated.

Thanks

Reply

Answer 5

Jun 20, 2012 4:14 AM in response to jazzizgreat

André,

"Forms" is a Numbers for iOS feature exclusively. I'd suggest that you check in that discussion area for advice on working with forms and other issues peculiar to the iOS implementation.

Command-Z, or the menu item Edit > Undo, will reverse a deletion. But, you have to undo in a timely manner and you have to suffer the loss of other entries that might be undone in the process.

Jerry

Reply