Film-Tech Forum ARCHIVE: Excel assistance please

This is topic Excel assistance please in forum Film-Yak at Film-Tech Forum ARCHIVE.

To visit this topic, use this URL:
https://ft-forum.com/ft/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic;f=8;t=006286

Posted by Brad Miller (Member # 2) on 10-28-2010, 07:05 PM:

I need to make a spreadsheet with various borders. People will enter text inside these borders, but I need to be able to lock the borders down so if someone decides to cut/paste (instead of copy/paste) that the borders will remain and only the text move.

I would also like to be able to selectively prevent text modification in some of the cells.

Can anyone walk me through this please? I am using Office 2010. Thanks.

Posted by Manny Knowles (Member # 1171) on 10-28-2010, 10:17 PM:

Brad,

I've never done this before myself but I've heard about Excel having layers. One of them is a drawing layer.

This page mentions how to access the layer. I'll play with it tomorrow at work and see if I can come up with a more detailed "how to."

Now back to my new toy.

Posted by Dustin Mitchell (Member # 372) on 10-29-2010, 12:22 AM:

I haven't used 2010 but in older versions of excel you could 'protect' individual sheets or even whole workbooks. To protect a sheet or workbook in older versions you'd go to 'Tools' and then 'Protection'. Protecting a sheet or workbook through this menu locks down every cell by default. To leave some cells unlocked you have to select them before you protect the sheet/workbook, right click, go to 'format cells' and select the 'Protection' tab. In this tab you can lock the cell (checked by default) or simply hide the formula in that cell. When you protect the sheet/workbook you can check various boxes to let users perform various formatting functions.

Like I said those instructions are for older versions of excel but it probably hasn't changed too much.

Posted by Manny Knowles (Member # 1171) on 10-29-2010, 02:42 PM:

Unfortunately the graphics layer is always on top, so it will totally obscure any cells under it. I was HOPING you'd be able to apply the formatting UNDER the cell data and use no actual cell formatting at all -- that way it would not matter if they cut/paste instead of copied/pasted data.

And there does not seem to be a way to lock down only the formatting without also totally locking access to the cell.

Looks like Excel needs another tweak.

Excel *does* let you choose how much info to paste. So the only work-around I could come up with is:

(Right-click in the cell)
PASTE SPECIAL > VALUES

Teach 'em to do it that way for the time-being.

Posted by David Stambaugh (Member # 1102) on 10-29-2010, 04:07 PM:

Would being able to disable cut-and-paste from certain cells solve the moving-borders problem? It appears you can do that, and even still permit copy while disabling only cut.

Posted by Randy Stankey (Member # 64) on 10-29-2010, 04:53 PM:

I don't know Excel very well because I refuse to use any Microsoft product but, in Open Office, you can protect an entire sheet from being modified without entering a password first. You can copy from those cells but you can not alter them

You can put your protected data on one sheet and copy it over to a second, accessible via the tabs on the bottom, and you will be able to carry out calculations normally. Those cells can be modified but the cells from which the data was copied can not. If, ever, they do get erased it should be easy to copy the data back.

In Open Office, it is also possible to create views which show several areas of the entire spreadsheet document at once. In so doing, you can have protected data, changeable data and anything else you need to see, all visible on one page.

Now... To translate that to Microsoft... [Roll Eyes]

Posted by Manny Knowles (Member # 1171) on 10-29-2010, 04:53 PM:

David -- How?

Brad -- You can also try this work-around:

STEP 1. Dedicate a worksheet in the same workbook file to hold a "template" of your main worksheet that has the formatting only (without any data).

STEP 2. If your employees should ever screw up your formatting (on the main sheet), you can copy the entire "template" and PASTE FORMATTING ONLY onto the entire main worksheet (the one that contains the data).

OPTIONAL: Protect the worksheet that keeps the template so nobody can mess it up.

To keep track of worksheets, you can rename them from the default "Sheet 1" etc by double-clicking on a tab and entering a new name (e.g. "Template" and/or "Main Page") OR by right-clicking on a tab and selecting "Rename"

Note that among the various "Paste Special" options are the ability to also paste formulas (in case they enter data into a cell where a formula was)

Recommended: Explore all the various Paste Special features.

Posted by David Stambaugh (Member # 1102) on 10-29-2010, 05:05 PM:

Various methods for disabling cut and/or copy in Excel are discussed here. Note that thread has posts spanning from 2004 to 2010. Looks like you have to do some VB scripting so it depends on how motivated you are.

Posted by Brad Miller (Member # 2) on 10-29-2010, 05:06 PM:

So far we have already tried everything listed except Manny's idea of pasting formatting only. I'll keep a template, but probably not on that Excel sheet Teaching these people to paste special values is simply not possible.