An open community 
of Macintosh users,
for Macintosh users.

FineTunedMac Dashboard widget now available! Download Here

Previous Thread
Next Thread
Print Thread
Page 1 of 2 1 2
Excel safeguard against overwriting formulas?
#5498 10/30/09 09:45 PM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
Excel Help, an oxymoron... or just moronic?
Using Office 2004, Excel 11.5.5 and OS 10.5.8

I'll start with a problem not mentioned in my subject/topic title. When I open Excel Help from the Help menu, the only way I can close it is to Quit Excel. What am I missing? I do not see a close box, a close icon, nothing that I can use to get out of Help. Help!

Now the main event...
I want to be able to prevent overwriting a cell containing a formula, so that typing something in said cell is impossible. In AppleWorks, that was simple: just Lock the cell. In Excel, there's an extra step: Protect the sheet or workbook. I tried locking a cell, then protecting the workbook, then the reverse order. Neither one worked. I was still able to type over it. I tried checking just Structure when protecting, and also Structure and Windows. I didn't try only Windows. I don't know what either one means, really.

Protecting the Sheet gives me three options. It seems that what I'm after would be Content. I've no idea what Objects or Scenarios is referring to. But, whenever I check any of them, all the green triangles signifying a cell containing a formula disappear. This appears to be the opposite of what I want to do!

And no, Excel Help did not help, and neither did my Missing Manual. As usual, they tell you what it's supposed to do, but not how to do it. Why can't people write decent instruction manuals? :rant:

Re: Excel safeguard against overwriting formulas?
Gregg #5504 10/31/09 06:26 AM
Joined: Aug 2009
Likes: 15
Online

Joined: Aug 2009
Likes: 15
> I'll start with a problem not mentioned in my subject/topic title. When I open Excel Help from the Help menu, the only way I can close it is to Quit Excel. What am I missing? I do not see a close box, a close icon, nothing that I can use to get out of Help. Help!

Excel 2009... Command-W works, as does clicking on the amber dot in the top left corner of the help window.

Sorry, but I'm so casual an Excel user that I've never even used a formula.


The new Great Equalizer is the SEND button.

In Memory of Harv: Those who can make you believe absurdities can make you commit atrocities. ~Voltaire
Re: Excel safeguard against overwriting formulas?
artie505 #5507 10/31/09 12:29 PM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
Thanks, but no dots, and Command-W closes the file window, not the Help window. confused

Re: Excel safeguard against overwriting formulas?
Gregg #5509 10/31/09 01:25 PM
Joined: Aug 2009
Likes: 14
Offline

Joined: Aug 2009
Likes: 14
I have Excel 2004 11.5.5. too and, like Artie, am not a power user.

I did a quick test....started a new sheet and opened Excel Help. I got the same window I used to get when I had 10.5.8. which includes two of the coloured dots top left (Red, Green). Red causes the box to go away. All of the icons in the Help menu bar worked normally.

Is an Excel re-install in order?

ryck


ryck

"What Were Once Vices Are Now Habits" The Doobie Brothers

iMac (Retina 5K, 27", 2020), 3.8 GHz 8 Core Intel Core i7, 8GB RAM, 2667 MHz DDR4
OS Ventura 13.6.3
Canon Pixma TR 8520 Printer
Epson Perfection V500 Photo Scanner c/w VueScan software
TM on 1TB LaCie USB-C
Re: Excel safeguard against overwriting formulas?
ryck #5524 11/01/09 08:00 PM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
I hope not. I might try a plist file first, or another account. Of course, if it's the latter, that can be a pain as well.

First, I'm holding out for an Excel power user who'd like to help out, not just get help. That, of course, would be regarding the core question here...

Re: Excel safeguard against overwriting formulas?
Gregg #5556 11/03/09 02:07 PM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
I got a solution to the main issue off of an Excel forum populated mostly by Windows users: Protect Sheet, Contents first and then lock the cells. They probably can't help me with the other issue. I'm on my own. The usual frontline troubleshooting techniques will be my first step.

I'm disappointed that the once vibrant Office for Mac sub-community from the MacFixIt days has not followed the herd to FTM. I find that I'm posting here much less than I used to, a result of not finding as many threads that are of interest to me.

Re: Excel safeguard against overwriting formulas?
Gregg #5564 11/03/09 08:19 PM
Joined: Aug 2009
Offline

Joined: Aug 2009
there are several other approaches to locking a cell. first, you could put the data in a cell somewhere else. or you could make that row/column 0 units in size so it doesn't even show up. You could also use data validation and make it the only value in the allowed list.

If you're only worried about accidental editing and not malicious editing, you could put ALL your user inputs on one sheet in one place annotized and formatted as best for them, and do all the calculations etc on another page that draws its data from the input page. And then maybe have a third sheet that is all the output?

Using data validation lists alone could be the solution to your problem, preventing people from making invalid entries? Data validation can allow one selection (say to "fruits" to change the validation list in the next column to only include fruits as selectable options in the popup etc.



I work for the Department of Redundancy Department
Re: Excel safeguard against overwriting formulas?
Virtual1 #5569 11/04/09 02:16 PM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
Thanks for your input, even though I find it very confusing.

Data for the cells with formulas are in an adjacent column in most cases on these spreadsheets. So, skipping down, one sheet for inputs and another for calculations also does not work. I need to see them side by side.

Make which row/column 0 units? Why would I want a row/column that I can't even see?

I don't understand validation lists. I am the only one inputting data, if that makes any difference.

I'm not being dismissive. Even though I probably won't use any of these ideas (I already have the answer to my chosen method) I might just learn something.

Thanks again!


Re: Excel safeguard against overwriting formulas?
JoBoy #5593 11/06/09 02:03 PM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
Got any advice for me about my Excel thread? (just the closing Help issue)


Edit by moderator: This post to JoBoy and its replies have been detached from Incorporating text from another cell using Excel.

Last edited by dianne; 11/06/09 11:44 PM. Reason: post and replies merged with Gregg's original thread; subject line revised to match the original topic.
Re: Excel safeguard against overwriting formulas?
Gregg #5598 11/06/09 05:49 PM
Joined: Aug 2009
Offline

Joined: Aug 2009
Originally Posted By: Gregg
Got any advice for me about my Excel thread? (just the closing Help issue)


I had many strange issues like the one you mentioned when I was running Excel 2004 under Snow Leopard 10.6.1. I was given two pieces of advice: Upgrade my RAM to 4Gb (I only had 2Gb) for a total of $72.00 from OtherWorld Computing (Cruicial is another popular, low-cost source); and upgrade to Office 2008.

Since I did both, all of those problems have disappeared. Strange problems with Excel 2004 have been attributed by some to the fact that 2004 is dependent on Rosetta that enables PPC software to run on Intel Macs while 2008 is Universal Binary and does not need Rosetta. I do not understand the intricacies of this point, but I do know that since doing these two upgrades, my troubles are over and I previously was having troubles so frequently that it seriously compromised my productivity.

Last edited by dianne; 11/06/09 11:46 PM. Reason: revised subject line.

Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: Excel safeguard against overwriting formulas?
Gregg #5599 11/06/09 06:04 PM
Joined: Aug 2009
Offline

Joined: Aug 2009
I forgot to mention this: If you use the old style Macros in Excel, be warned that you can still use existing ones, but you can't create new ones. I hear via the rumor mill that some kind of old Macro function will be restored in the next release of Excel/Mac, but I don't know what it is. Although I used Macros frequently in the older versions of Excel, the many problems I was having with 2004 and Snow Leopard forced me to upgrade to Excel 2008. Since then, I've found ways to minimize the inconvenience of not having Macros, but it takes a lot of work to rearrange complex spreadsheets to do without the Macros. I'm not a programmer, so I am unable to use the existing forms of Basic programming to produce the automated spreadsheets I enjoyed for so many years.

Last edited by dianne; 11/06/09 11:47 PM. Reason: revised subject line.

Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: Excel safeguard against overwriting formulas?
Gregg #5600 11/06/09 06:06 PM
Joined: Aug 2009
Moderator
Offline
Moderator

Joined: Aug 2009
Gregg,

Is this the Excel thread you are referring to? Excel safeguard against overwriting formulas?

I wanted to make sure before I detach and move your question and its replies from this topic.

Last edited by dianne; 11/06/09 11:49 PM. Reason: revised subject line.

Back up everything you can't afford to lose: documents, mail, movies, music, photos, and other data and settings.
Re: Excel safeguard against overwriting formulas?
dianne #5605 11/06/09 06:32 PM
Joined: Aug 2009
Offline

Joined: Aug 2009
Originally Posted By: dianne
Gregg,

Is this the Excel thread you are referring to? Excel safeguard against overwriting formulas?

I wanted to make sure before I detach and move your question and its replies from this topic.


Gregg can answer for himself, but I had to go to the thread you just mentioned to understand the question he asked in this thread. Thanks for being an alert Moderator who keeps this place organized. I really appreciate it.

Last edited by JoBoy; 11/07/09 02:23 AM.

Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: Excel safeguard against overwriting formulas?
dianne #5606 11/06/09 08:01 PM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
Yes it is.

Last edited by Gregg; 11/07/09 12:56 AM. Reason: removed irrelevant content
Re: Excel safeguard against overwriting formulas?
JoBoy #5615 11/07/09 12:59 AM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
Originally Posted By: JoBoy
Strange problems with Excel 2004 have been attributed by some to the fact that 2004 is dependent on Rosetta that enables PPC software to run on Intel Macs while 2008 is Universal Binary and does not need Rosetta.


But that is incorrect. I do not have Rosetta installed.

I plan to upgrade to Office 2010 when it comes out. No plans to add RAM at this time.

I don't use macros, but thanks for that info anyway.

Re: Excel safeguard against overwriting formulas?
Gregg #5616 11/07/09 01:09 AM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
Originally Posted By: Gregg
I got a solution to the main issue off of an Excel forum populated mostly by Windows users: Protect Sheet, Contents first and then lock the cells. They probably can't help me with the other issue. I'm on my own. The usual frontline troubleshooting techniques will be my first step.


1st issue: I've got that in the wrong order. Oops! Plus, there are three steps:
A) Edit > Go To > Special... check appropriate box, in this case, it's formulas (selects all cells with formulas)
B) Format > Cells > Protection... lock cells
C) Tools > Protection > Protect Sheet... (contents)

2nd issue (Help won't close): I moved the Excel plist file out of my Home > Library > Preferences folder, then fired up Excel. That did not fix the problem, and no new plist file was created! I even went into Excel preferences, changed a setting and saved it. It still did not make a new plist file! I put the original plist file back, and crawled back under my rock.

Re: Excel safeguard against overwriting formulas?
Gregg #5621 11/07/09 02:36 AM
Joined: Aug 2009
Offline

Joined: Aug 2009
Originally Posted By: Gregg

But that is incorrect. I do not have Rosetta installed.


All I know is that Excel 2004 is very unpredictable running on system 10.6.1. Others have reported problems with multiple MS Office 2004 applications. You might want to try 10.6.2 that should be available for download fairly soon and see if it cures any of your problems. If that doesn't do it, I still recommend Excel 2008, but if you're determined to stick with 2004, try installing Rosetta. Maybe it will help. Maybe it won't. I think your main problem is that the Mac OS X Snow Leopard is leaving 2004 behind. When I upgraded to 2008, all of my former problems went away and I've never had such a smooth, quick operating system experience.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: Excel safeguard against overwriting formulas?
Gregg #5623 11/07/09 02:45 AM
Joined: Aug 2009
Offline

Joined: Aug 2009
Originally Posted By: Gregg
2nd issue (Help won't close): I moved the Excel plist file out of my Home > Library > Preferences folder, then fired up Excel. That did not fix the problem, and no new plist file was created!


Don't move the plist file. Delete it! You won't get a new plist file created until the old one is deleted. Unless you're a Mac system guru, you shouldn't change locations of system files.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: Excel safeguard against overwriting formulas?
Gregg #5632 11/07/09 04:37 AM
Joined: Sep 2009
Offline

Joined: Sep 2009
Excel cell protection works on two levels.

Individual cells are Locked by default - go to the Format menu, choose Format Cells and click on the Protection tab. Here, you can change the "Locked" status of the currently selected cell(s).

However, this Locked status is ignored unless the Sheet as a whole is protected - go to the Tools menu, select Protection and Protect Sheet. Once you do this then the Cell protection comes into play.

The process is to first use Cell Protection to unlock all cells that you want users to be able to modify, and then protect the sheet.

This applied to Excel 2008, but from memory it worked exactly the same in 2004.



Mac since 1984, Apple since 1978
MacBook Pro, iPhone 5, MacBook (in living room for iTunes and searching for recipes), iPad
Re: Excel safeguard against overwriting formulas?
oldMacMan #5634 11/07/09 05:00 AM
Joined: Sep 2009
Offline

Joined: Sep 2009
I have just checked, and the process is exactly the same in Excel 2004.

Also checked the Help issue - in 2004, the help windows have a working red close button in the usual location at the top left of the window. I'm using Snow Leopard 2.6.1

My usual way of solving issues of odd behaviour of this sort is to:

• Quit all Microsoft programs
• Move the relevant preference file to the desktop
• Re-launch the troublesome program (Excel, in this case). A new default preference file will be created.
• If this fixes the problem, then trash the delinquent preference file from the desktop.
• If not then return it to its original location so any other preferences may be retained.

The location of the preference file for Excel 2004 is:
~/Library/Preferences/Microsoft/com.microsoft.Excel.prefs.plist

For Excel 2008 it is:
~/Library/Preferences/com.microsoft.Excel.plist

~ represents the users home directory


Mac since 1984, Apple since 1978
MacBook Pro, iPhone 5, MacBook (in living room for iTunes and searching for recipes), iPad
Re: Excel safeguard against overwriting formulas?
oldMacMan #5647 11/07/09 04:14 PM
Joined: Aug 2009
Offline

Joined: Aug 2009
Originally Posted By: oldMacMan

My usual way of solving issues of odd behaviour of this sort is to:

• Quit all Microsoft programs
• Move the relevant preference file to the desktop
• Re-launch the troublesome program (Excel, in this case). A new default preference file will be created.
• If this fixes the problem, then trash the delinquent preference file from the desktop.
• If not then return it to its original location so any other preferences may be retained.

The location of the preference file for Excel 2004 is:
~/Library/Preferences/Microsoft/com.microsoft.Excel.prefs.plist

For Excel 2008 it is:
~/Library/Preferences/com.microsoft.Excel.plist

~ represents the users home directory


I tried your procedure and it works. Sorry about the confusion with my post. I was repeating advice I was given from what I considered a reliable source.

I have already offered the solution I found for repeated, bizarre behavior of Excel 2004 similar to that described in this thread. That's about all I can do.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: Excel safeguard against overwriting formulas?
oldMacMan #5651 11/07/09 05:03 PM
Joined: Aug 2009
Likes: 16
Moderator
Offline
Moderator

Joined: Aug 2009
Likes: 16
Originally Posted By: OldMacMan
My usual way of solving issues of odd behaviour of this sort is to:

• Quit all Microsoft programs
<snip>
• Re-launch the troublesome program (Excel, in this case).

When I was training Microsoft Support Engineers back in the mid-90's, that was essentially the standard first response "fix" we taught regardless of whether it was on a Windows or Mac platform. I could go on at length into the reasons why this is still the case with their products but it is still the standard first response "fix" for Windows and Windows apps.

Apple is not perfect, but at least they had the good sense to admit when the code base for their OS and apps was not worth trying to "fix" and they started over with a clean sheet of paper. I suspect there are those at Microsoft who wish it were economically and politically feasible for them to do the same. In that sense they are held captive by their own market dominance.


If we knew what it was we were doing, it wouldn't be called research, would it?

— Albert Einstein
Re: Excel safeguard against overwriting formulas?
joemikeb #5654 11/07/09 07:07 PM
Joined: Aug 2009
Offline

Joined: Aug 2009
Joemikeb:

You were the first responder to my distress call in the long thread, "When is an Excel file getting too big?" You also recommended OWC as a reliable vendor of RAM. It was your advice, coupled with reports that Excel 2008 was more comfortable with Snow Leopard than 2004, that led to my making those two changes.

Upon installing the extra RAM and upgrading to MS Office 2008, my troubles ceased. My machine continues to run flawlessly. It is the finest experience I have ever had with a computer. It is a tribute to the quality of Snow Leopard and the good advice given on this forum. Thank you for your help.


Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5
Re: Excel safeguard against overwriting formulas?
JoBoy #5658 11/07/09 09:46 PM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
Originally Posted By: JoBoy
Don't move the plist file. Delete it! You won't get a new plist file created until the old one is deleted. Unless you're a Mac system guru, you shouldn't change locations of system files.


Hmm. Every time I see someone recommending checking out a plist file, they say move it to the Desktop, just in case you want to put it back later. I suppose I could copy it to my jump drive, then delete it.... Don't worry, I'm not about to permanently change the location of anything else. No harm was done, it just didn't regenerate as advertised. Can anyone else confirm the behavior that it's supposed to exhibit?

Regarding your previous post, I am not using Snow Leopard. I identified my OS, first thing. And to repeat, I plan to get Office 2010 when available.

Re: Excel safeguard against overwriting formulas?
oldMacMan #5659 11/07/09 09:58 PM
Joined: Aug 2009
Likes: 1
Gregg Offline OP
OP Offline

Joined: Aug 2009
Likes: 1
Originally Posted By: oldMacMan
A. I have just checked, and the process is exactly the same in Excel 2004.

B. Also checked the Help issue - in 2004, the help windows have a working red close button in the usual location at the top left of the window. I'm using Snow Leopard 2.6.1

C. My usual way of solving issues of odd behaviour of this sort is to:
.....

D. The location of the preference file for Excel 2004 is:
~/Library/Preferences/Microsoft/com.microsoft.Excel.prefs.plist


A. Yes, it is. (regarding protection) Thanks for going the extra mile. smile

B. Mine does not have buttons. Strangely, in Word, same thing, but... Unlike Excel, when I hit Cmd-W it closes the Help window. (ASA, in Excel, it closes the spreadsheet)

C. Same thing I tried, but...

D. I have 8 com.microsoft.____.plist files in the Preferences folder. I did not even notice the Microsoft folder farther down in that window. There are 17 items in it, including duplicates of the Excel and Word plist files. (That's all I use from Office.) I wonder if I can Trash those 8 files without hurting anything. I suppose I could copy them to the jump drive first, just in case. I'll try again on the ones in the Microsoft folder, but not for awhile.

Thanks for your insights.

Page 1 of 2 1 2

Moderated by  alternaut, dianne, dkmarsh 

Link Copied to Clipboard
Powered by UBB.threads™ PHP Forum Software 7.7.4
(Release build 20200307)
Responsive Width:

PHP: 7.4.33 Page Time: 0.033s Queries: 65 (0.024s) Memory: 0.7122 MB (Peak: 0.8966 MB) Data Comp: Zlib Server Time: 2024-03-28 12:06:08 UTC
Valid HTML 5 and Valid CSS