my profile | my password | search | faq & rules | forum home

» Film-Tech Forum ARCHIVE   » Community   » Film-Yak   » Excel assistance please

Author Topic: Excel assistance please Brad Miller Administrator Posts: 17775 From: Plano, TX (36.2 miles NW of Rockwall) Registered: May 99 posted 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.  |  IP: Logged Manny Knowles "What are these things and WHY are they BLUE???" Posts: 4247 From: Bloomington, IN, USA Registered: Feb 2002 posted 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.  |  IP: Logged Dustin Mitchell Phenomenal Film Handler Posts: 1865 From: Mondovi, WI, USA Registered: Mar 2000 posted 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.  |  IP: Logged Manny Knowles "What are these things and WHY are they BLUE???" Posts: 4247 From: Bloomington, IN, USA Registered: Feb 2002 posted 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.  |  IP: Logged David Stambaugh Film God Posts: 4021 From: Eugene, Oregon Registered: Jan 2002 posted 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.  |  IP: Logged Randy Stankey Film God Posts: 6539 From: Erie, Pennsylvania Registered: Jun 99 posted 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...  |  IP: Logged Manny Knowles "What are these things and WHY are they BLUE???" Posts: 4247 From: Bloomington, IN, USA Registered: Feb 2002 posted 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.  |  IP: Logged David Stambaugh Film God Posts: 4021 From: Eugene, Oregon Registered: Jan 2002 posted 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.  |  IP: Logged Brad Miller Administrator Posts: 17775 From: Plano, TX (36.2 miles NW of Rockwall) Registered: May 99 posted 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.  |  IP: Logged

All times are Central (GMT -6:00)

Printer-friendly view of this topic

Hop To: Select a Forum: Category: Operations -------------------- Digital Cinema Forum Film Handlers' Forum Large Format Forum Ground Level Feature Info, Trailer Attachments & REAL Credit Offsets Category: Community -------------------- Film-Yak Film Handlers' Movie Reviews The Afterlife Bob Maar's Joke-A-Thon Category: Classified -------------------- Equipment and Supplies Wanted/For Sale Help Wanted/Seeking Employment Category: System -------------------- Software Beta Testing Forum

The Film-Tech Forums are designed for various members related to the cinema industry to express their opinions, viewpoints and testimonials on various products, services and events based upon speculation, personal knowledge and factual information through use, therefore all views represented here allow no liability upon the publishers of this web site and the owners of said views assume no liability for any ill will resulting from these postings. The posts made here are for educational as well as entertainment purposes and as such anyone viewing this portion of the website must accept these views as statements of the author of that opinion and agrees to release the authors from any and all liability.