An open community 
of Macintosh users,
for Macintosh users.

FineTunedMac Dashboard widget now available! Download Here

Page 1 of 2 1 2 >
Topic Options
#5498 - 10/30/09 02:45 PM Excel safeguard against overwriting formulas?
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
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:

Top
#5504 - 10/30/09 11:26 PM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
artie505 Online


Registered: 08/04/09
> 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

Top
#5507 - 10/31/09 05:29 AM Re: Excel safeguard against overwriting formulas? [Re: artie505]
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
Thanks, but no dots, and Command-W closes the file window, not the Help window. confused

Top
#5509 - 10/31/09 06:25 AM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
ryck Online


Registered: 08/04/09
Loc: Okanagan Valley
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

iMac (Retina 5K, 27", 2017), 3.4 GHz Intel Core i5, 8GB RAM, 2400 MHz DDR4
OS Mojave 10.14.6
Canon MX710 Printer
Epson Perfection V500 Photo Scanner
Carbon Copy Clone on 1TB LaCie USB-C
Carbon Copy Clone on 500GB OWC Mercury OTG Pro

Top
#5524 - 11/01/09 12:00 PM Re: Excel safeguard against overwriting formulas? [Re: ryck]
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
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...

Top
#5556 - 11/03/09 06:07 AM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
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.

Top
#5564 - 11/03/09 12:19 PM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
Virtual1 Offline


Registered: 08/04/09
Loc: Iowa
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

Top
#5569 - 11/04/09 06:16 AM Re: Excel safeguard against overwriting formulas? [Re: Virtual1]
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
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!


Top
#5593 - 11/06/09 06:03 AM Re: Excel safeguard against overwriting formulas? [Re: JoBoy]
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
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.


Edited by dianne (11/06/09 03:44 PM)
Edit Reason: post and replies merged with Gregg's original thread; subject line revised to match the original topic.

Top
#5598 - 11/06/09 09:49 AM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
JoBoy Offline


Registered: 08/05/09
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.


Edited by dianne (11/06/09 03:46 PM)
Edit Reason: revised subject line.
_________________________
Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top
#5599 - 11/06/09 10:04 AM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
JoBoy Offline


Registered: 08/05/09
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.


Edited by dianne (11/06/09 03:47 PM)
Edit Reason: revised subject line.
_________________________
Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top
#5600 - 11/06/09 10:06 AM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
dianne Offline

Moderator

Registered: 08/04/09
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.


Edited by dianne (11/06/09 03:49 PM)
Edit Reason: revised subject line.
_________________________
dianne • moderator

Back up everything you can't afford to lose – documents, mail, movies, music, photos, and other data and settings.

Top
#5605 - 11/06/09 10:32 AM Re: Excel safeguard against overwriting formulas? [Re: dianne]
JoBoy Offline


Registered: 08/05/09
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.


Edited by JoBoy (11/06/09 06:23 PM)
_________________________
Mac Pro dual Quad-Core Intel Xeons Early 2008; 16GB RAM; MacOS X 10.11.6, iOS 9.3.5

Top
#5606 - 11/06/09 12:01 PM Re: Excel safeguard against overwriting formulas? [Re: dianne]
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
Yes it is.


Edited by Gregg (11/06/09 04:56 PM)
Edit Reason: removed irrelevant content

Top
#5615 - 11/06/09 04:59 PM Re: Excel safeguard against overwriting formulas? [Re: JoBoy]
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
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.

Top
#5616 - 11/06/09 05:09 PM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
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.

Top
#5621 - 11/06/09 06:36 PM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
JoBoy Offline


Registered: 08/05/09
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

Top
#5623 - 11/06/09 06:45 PM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
JoBoy Offline


Registered: 08/05/09
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

Top
#5632 - 11/06/09 08:37 PM Re: Excel safeguard against overwriting formulas? [Re: Gregg]
oldMacMan Offline


Registered: 09/02/09
Loc: Auckland, New Zealand
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

Top
#5634 - 11/06/09 09:00 PM Re: Excel safeguard against overwriting formulas? [Re: oldMacMan]
oldMacMan Offline


Registered: 09/02/09
Loc: Auckland, New Zealand
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

Top
#5647 - 11/07/09 08:14 AM Re: Excel safeguard against overwriting formulas? [Re: oldMacMan]
JoBoy Offline


Registered: 08/05/09
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

Top
#5651 - 11/07/09 09:03 AM Re: Excel safeguard against overwriting formulas? [Re: oldMacMan]
joemikeb Online
Moderator

Registered: 08/04/09
Loc: Fort Worth, Texas
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.
_________________________
joemikeb • moderator

Top
#5654 - 11/07/09 11:07 AM Re: Excel safeguard against overwriting formulas? [Re: joemikeb]
JoBoy Offline


Registered: 08/05/09
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

Top
#5658 - 11/07/09 01:46 PM Re: Excel safeguard against overwriting formulas? [Re: JoBoy]
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
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.

Top
#5659 - 11/07/09 01:58 PM Re: Excel safeguard against overwriting formulas? [Re: oldMacMan]
Gregg Offline


Registered: 08/04/09
Loc: Milwaukee, WI (USA)
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.

Top
Page 1 of 2 1 2 >

Moderator:  alternaut, dianne, dkmarsh