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:

> 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.

Thanks, but no dots, and Command-W closes the file window, not the Help window.

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

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...

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.

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.

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!

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.

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.

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.

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.

Yes it is.

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.

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.

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.

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.

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.

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

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.

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:

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.

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.

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

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.

No problem. I have to rely on others' expertise as well, as you can see. I'm no smarter than Edison.

I'll keep the RAM and upgrade ideas in my back pocket. Thanks for chiming in. Your input was more helpful than you give yourself credit for.

That's true and I apologize for not scrolling back to see if you did mention your OS. You will notice that each of my posts has a signature line that shows my OS and other relevant information that may assist people who are willing to help. Some time ago, a moderator told me I should do that. It improves the chances that people will give relevant advice and not chase ghosts. You might want to consider it. You use My Stuff>Edit Profile>Signature.

Those 8 files sitting in the Preferences folder do not have "prefs" in their file names. If they did, the ones for Entourage, Excel, and Word would have the same names as those found inside the Microsoft folder. I moved the 8 files out of the Preferences folder. Then I moved the Excel.prefs.plist file out of the Microsoft folder to the Desktop. When I then opened an Excel file, it was replaced. However, the Excel Help window still did not have a close button. I put the plist file back.

I also had a file called Microsoft Office Settings (11) sitting on my Desktop. I forget where it came from. I just remember finding it, along with a VBA Preferences file in a strange place. Both files have been on my Desktop for awhile. The latter file (actually its twin) also appears in the Microsoft folder (above). Both have weird dates, that is, months after I got the computer, but a year apart. I put the former into the Microsoft folder before trying the above test.

The Excel Help window will close by doing the following:
Cmd-W (closes the spreadsheet window)
Cmd-W (does nothing perceptible)
Cmd-W (closes Excel Help window)
Excel is still running

Of those 8 files not in the Microsoft folder, two were replaced in the Preferences folder after opening an Excel file: autoupdate.plist and OfficeNotifications.plist

One of the 8 sequestered files is called autoupdate2.plist which means a duplicate was made somehow. I would think that would be an intentional act (which I don't recall doing myself) rather than a program doing that on its own.

> One of the 8 sequestered files is called autoupdate2.plist which means a duplicate was made somehow.

Hmmm... I'm running Excel 2008, and I've had the same plist in /Users/Your short name/Library/Preferences since the first time I launched the app.

Also, I've got a folder "com.microsoft.autoupdate2" in /private/var/folders.

I wonder if we're dealing with an unfortunate nomenclature gaffe?

I'll post this now so I don't forget again.
Buttons were hiding off the top of the Help windows. Windows were jammed to the top of the screen, right up to the Menu Bar. Changing the display resolution revealed the whole window, with buttons. I just pulled the windows down and changed the resolution back. Problem solved!

I have a similar issue with excel. I have one line of toolbar and one line of the edit line for formulas.

I believe in that case they intended for the toolbar line that this is all on, to occupy only one line. But my formula bar is below it and stretches cross screen.

This causes windows that I click the green maximize button on to take up an upper left position UNDER the formula bar. I have to resize the window slightly to decrease its height and then grab it from over on the right (past where my formula bar ends) and drag it down so I have access to the window's buttons in their upper left corner.

Precisely to prevent that, I have customized my toolbar and shortened the formula bar so that both fit on one line across my wide screen. I removed tools that I habitually use the keyboard shortcuts for, as well as the ones I've never used, mostly because I have no idea what they do, and apparently, I haven't needed to know. And, I had room to add some that I'll use, and find it more convenient than searching the menus for them. "Now, where was that blankety-blank command anyway?"

does this fit all in one line for you?

{=IF(B20="","",-SUM(IF(JDCCU!$A$3:$A$9926="N",0,IF(IF(JDCCU!$C$3:$C$9926>=$M$3,
IF(JDCCU!$C$3:$C$9926<=$M$4,JDCCU!$I$3:$I$9926=B20,0),0),JDCCU!$E$3:$E$9926,0))))}

The formula bar has its own row to itself in my toolbar in excel. And even then, that formula wraps to another line.

I don't mind text wrapping in the formula bar, and I do get that once in awhile, but I don't use IF or other "fancy" formulations. Since the formula you posted will not fit on one line even when you stretch the formula bar all the way across your screen, why not shorten it? I thought your complaint was needing two rows of toolbars. I think there should be an option to "dock" the toolbars at the sides of the windows. Given today's wide screens, that would be more efficient.

Edit
Re-reading your post... Why not put the formula bar on top, and right-justify the toolbar below it? If that will stick, maybe your buttons will not be hidden.