Unoffical empeg BBS

Quick Links: Empeg FAQ | RioCar.Org | Hijack | BigDisk Builder | jEmplode | emphatic
Repairs: Repairs

Page 1 of 2 1 2 >
Topic Options
#328807 - 16/01/2010 16:02 Broken Excel Spreadsheet
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5549
Loc: Ajijic, Mexico
I have an Excel spreadsheet that went bonkers on me (technical term) and I'm wondering what went wrong.

It's about 1400 rows by 7 columns, and contains only data, that is non-numerical information. I have salvaged the data by copying it to the Windows clipboard and pasting it into a new, blank spreadsheet, then using the AutoFit tool to set the column widths. The salvaged spreadsheet is as good as new, all the fill colors are right, the formulas work, everything is good.

I don't know what I did to cause the original spreadsheet to go wrong. All at once the column widths and formatting got weird, I couldn't align columns (If the data were left-justified I couldn't change it to centered, for example) and the cells with formulas in them (example: =countA(D2:D1286) to count the number of cells with data) showed the formulas rather than the result of the calculation. It's as though the spreadsheet got put into some alternate viewing mode that I couldn't get out of.

Does anybody know what happened?

tanstaafl.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#328812 - 16/01/2010 22:49 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
Attack
addict

Registered: 01/03/2002
Posts: 599
Loc: Florida
I think you hit CTRL + `

EDIT: That is the CTRL + tilde (the key to the left on the 1 on most keyboards.


Edited by Attack (17/01/2010 01:08)
_________________________
Chad

Top
#328815 - 17/01/2010 00:43 Re: Broken Excel Spreadsheet [Re: Attack]
larry818
old hand

Registered: 01/10/2002
Posts: 1039
Loc: Fullerton, Calif.
That didn't do it for me.

Check "Tools > Options > View" and look under "window options", be sure "formulas" is not checked.

Edit... Ah, that weird left single quote mark, yes, it does work...


Edited by larry818 (17/01/2010 00:48)

Top
#328816 - 17/01/2010 01:00 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
gbeer
carpal tunnel

Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
Not being able to change things sounds like protection was turned on, but that's somewhat hard to do without noticing.
_________________________
Glenn

Top
#328818 - 17/01/2010 02:28 Re: Broken Excel Spreadsheet [Re: Attack]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5549
Loc: Ajijic, Mexico
Originally Posted By: Attack
I think you hit CTRL + `


I have no idea how I might have done it, but doing so EXACTLY replicates what happened to my spreadsheet. Well, maybe I was doing a Shift-Tab and hit Ctrl ` instead, but that seems a stretch. Literally. I do use that Shift-Tab a lot, though, so it's not impossible.

What is the purpose of that CTRL + ` command anyway, other than to wreck my spreadsheet?

Anyway, you nailed it. Thanks!

tanstaafl.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#328819 - 17/01/2010 02:31 Re: Broken Excel Spreadsheet [Re: larry818]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5549
Loc: Ajijic, Mexico
Originally Posted By: larry818
Check "Tools > Options > View" and look under "window options", be sure "formulas" is not checked.


Indeed, hitting that accursed Ctrl ` combo besides wrecking the formatting does set that "formulas" checkbox.

Unchecking that box and resetting the column widths restores the spreadsheet to its former glory.

tanstaafl.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#328829 - 18/01/2010 00:31 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
gbeer
carpal tunnel

Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
Dose not hitting that combo a second time put it all back?
_________________________
Glenn

Top
#328831 - 18/01/2010 11:31 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
Tim
veteran

Registered: 25/04/2000
Posts: 1529
Loc: Arizona
Originally Posted By: tanstaafl.
What is the purpose of that CTRL + ` command anyway, other than to wreck my spreadsheet?

It makes it easy to check a bunch of formulas at once if you have an error somewhere. It is a lot easier than going through cell by cell checking them.

Top
#328837 - 18/01/2010 13:32 Re: Broken Excel Spreadsheet [Re: Attack]
wfaulk
carpal tunnel

Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
Originally Posted By: Attack
I think you hit CTRL + `

EDIT: That is the CTRL + tilde

A "tilde" is the curlicue that's the shifted version of that key on US keyboards: "~". It's used in Spanish (and other Iberian languages) to modify 'n's, as in "Español" and "baño".

The '`' is correctly referred to as the grave accent, but often referred to as a "backquote" or "backtick".

[[Pedant mode off]]
_________________________
Bitt Faulk

Top
#328847 - 18/01/2010 15:08 Re: Broken Excel Spreadsheet [Re: wfaulk]
Robotic
pooh-bah

Registered: 06/04/2005
Posts: 2026
Loc: Seattle transplant
I was thinking of the mathematic uses for the tilde.
Who would have thought such a small character would get such a large wikipedia article?
Fun stuff!
_________________________
10101311 (20GB- backup empeg)
10101466 (2x60GB, Eutronix/GreenLights Blue) (Stolen!)

Top
#328857 - 18/01/2010 18:37 Re: Broken Excel Spreadsheet [Re: gbeer]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5549
Loc: Ajijic, Mexico
Originally Posted By: Gbeer
Dose not hitting that combo a second time put it all back?


Hey, yes it does. I never thought of trying that. Of course at the time of the panic I had no idea what had caused it, so thinking of what might undo it wasn't obvious.

tanstaafl.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#328858 - 18/01/2010 19:05 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5549
Loc: Ajijic, Mexico
And while we're on the topic of Excel...

I'm running a pretty old version of Excel, "About" says it is Excel 2002. Before that I was running an even older version of unknown vintage.

In the older version when I opened multiple spreadsheets by clicking on the filenames (opening by file association) each spreadsheet opened in it's own Excel program. The newer (2002) version opens all the spreadsheets in a single program, all of the spreadsheet "windows" being contained in a single larger Excel window, sort of a window within a window setup.

I hate, despise, detest, etc. this.

[Diatribe]I seem to spend half my time moving those spreadsheets around inside the Excel window, trying to find the scroll bars or trying to uncover columns that are hidden by another spreadsheet. Whenever I open a spreadsheet, the main window is always whatever size the previous Excel session was, rather than the size appropriate for the particular spreadsheet, and it is ALWAYS the wrong size so I drag the window out or in to size it properly and then realize that I was supposed to drag the spreadsheet window the the right size and then drag the Excel window... and I can't tell you how many times I have forgotten that I had more than one spreadsheet open within the Excel window and closed the window, losing the changes in the "hidden" spreadsheet because I decided not to save the changes in the one on top and didn't read the warning message carefully.[/Diatribe]

Fortunately I have found the workaround, and that is to start Excel by running the program directly (not through file association) and then opening the spreadsheet from within Excel. That way I can have as many different Excels running as I need. Of course they will all still be that idiotic window within window nonsense.

Do later versions of Excel still do this window-in-window stuff? If not, I might be persuaded to upgrade. Or, is there some setting somewhere within my 2002 version of Excel to turn this "feature" off?

tanstaafl.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#328860 - 18/01/2010 19:11 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
peter
carpal tunnel

Registered: 13/07/2000
Posts: 4180
Loc: Cambridge, England
Originally Posted By: tanstaafl.
Or, is there some setting somewhere within my 2002 version of Excel to turn this "feature" off?

I don't have Excel here, but, if it helps you on your search through the preferences windows, the Microsoft name for the "feature" that you (and I) hate so much is "MDI". In theory it's an older design than the obviously right way of doing it (which Microsoft call "multi-SDI"), so if an older Excel had a setting for it, so should a newer one.

Peter

Top
#328861 - 18/01/2010 19:13 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
wfaulk
carpal tunnel

Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
It would have helped you to find this answer if you had known that that interface was called "MDI".

This should work:
Quote:
Click Options from the Tools menu, choose the View tab, and click the Windows In Taskbar checkbox. Now, when you return to Excel, each open workbook will appear as a separate button on your Taskbar.
_________________________
Bitt Faulk

Top
#328862 - 18/01/2010 19:21 Re: Broken Excel Spreadsheet [Re: peter]
peter
carpal tunnel

Registered: 13/07/2000
Posts: 4180
Loc: Cambridge, England
Originally Posted By: peter
if an older Excel had a setting for it, so should a newer one.

O silly me for assuming Microsoft would do something vaguely sane.

How you get so big making software of this kind?

Peter

Top
#328863 - 18/01/2010 19:24 Re: Broken Excel Spreadsheet [Re: wfaulk]
peter
carpal tunnel

Registered: 13/07/2000
Posts: 4180
Loc: Cambridge, England
Originally Posted By: wfaulk
This should work

Very splendidly, that article is only readable if you click through from a Google results page.

Peter

Top
#328866 - 18/01/2010 19:44 Re: Broken Excel Spreadsheet [Re: peter]
wfaulk
carpal tunnel

Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
Of course.
_________________________
Bitt Faulk

Top
#328867 - 18/01/2010 19:48 Re: Broken Excel Spreadsheet [Re: peter]
wfaulk
carpal tunnel

Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
Originally Posted By: peter
How you get so big making software of this kind?

By exploiting a monopoly?
_________________________
Bitt Faulk

Top
#328870 - 18/01/2010 21:27 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
gbeer
carpal tunnel

Registered: 17/12/2000
Posts: 2665
Loc: Manteca, California
Originally Posted By: tanstaafl.

Fortunately I have found the workaround, and that is to start Excel by running the program directly (not through file association) and then opening the spreadsheet from within Excel. That way I can have as many different Excels running as I need. Of course they will all still be that idiotic window within window nonsense?

tanstaafl.


I've used that on occasion. If you maximize the inner window, it effectively goes away.
_________________________
Glenn

Top
#328880 - 19/01/2010 00:46 Re: Broken Excel Spreadsheet [Re: wfaulk]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5549
Loc: Ajijic, Mexico
Originally Posted By: wfaulk
This should work:


No, all that does is show a separate icon in the task bar for each spreadsheet you have open. All of the spreadsheets are still contained within the single Excel window. FWIW, I already had that box checked by default.

Can anyone think of a single advantage to this window-in-window format? Oh, wait, I actually did discover one thing that works better. When cutting and pasting between separate instances of Excel, formulas are pasted as values only, whereas in the window-in-window version, the formulas are carried over. Other than that, I can't think of anything.

tanstaafl.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#328881 - 19/01/2010 00:50 Re: Broken Excel Spreadsheet [Re: gbeer]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5549
Loc: Ajijic, Mexico
Originally Posted By: gbeer
If you maximize the inner window, it effectively goes away.


Good tip!!

tanstaafl.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#328885 - 19/01/2010 02:24 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
larry818
old hand

Registered: 01/10/2002
Posts: 1039
Loc: Fullerton, Calif.
It seems that everything that's upsetting is settable in tools>options somewhere.

Personally, I prefer to have one copy of a program running. I typically have about a dozen excel spreadsheets open at any time, and I don't want a dozen copies of excel running and burning up memory.

I'm still running office '97 as all versions after that run a separate copy of word for every open document. I heard that they did this because outlook uses word as the editor and they couldn't figure out a way to slip an email in and out of the open documents, so decided everything shall be it's own copy of word. Brilliant.

However, I use Mozilla. And on my primary machine, I run Windows 2000, which I feel is the peak of windows development. I use XP on everything else, for a variety of reasons... but 2K is the best. Less annoying behavior, and most things just work, something MS has gotten away from.

I'm hoping 7 is ok, but I'm worried about it being "not worse than xp..."

I'm still missing os/2... smile

Top
#328894 - 19/01/2010 08:01 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
peter
carpal tunnel

Registered: 13/07/2000
Posts: 4180
Loc: Cambridge, England
Originally Posted By: tanstaafl.
Can anyone think of a single advantage to this window-in-window format?

Yes: it's easier to port software from the DOS era, or pre-multi-tasking Windows era, that assumes it owns the whole screen. Oh, wait, you mean advantages for the user of the software? No, not really.

Well, in fact I suppose it's also easier to "port" users' experience of software from earlier times. If you use Windows like an Iphone -- if all your windows are always maximised, and you use Alt-Tab to go and find Excel when you want to do some excelling, then Alt-Tab to go and find Word when you now want to do some wording, then MDI is the thing you need.
Quote:
When cutting and pasting between separate instances of Excel, formulas are pasted as values only, whereas in the window-in-window version, the formulas are carried over.

That's nothing to do with MDI as such, of course, it's just down to the fact that they're separate instances of Excel. A proper multi-SDI application like Word (probably) is, can run several top-level windows from the same instance.

Faking that by running new instances for each document (here's how to do that for double-clicking Excel files) will use a bit more memory than one-instance-for-all -- but far less than the N times as much one might expect, because all the read-only and/or relocatable-code memory pages, such as the program code itself, will be shared between them.

Peter

Top
#328902 - 19/01/2010 13:18 Re: Broken Excel Spreadsheet [Re: peter]
wfaulk
carpal tunnel

Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
And a properly designed program wouldn't be using multiple instances anyway; it would just have one program with multiple top-level windows.
_________________________
Bitt Faulk

Top
#328914 - 19/01/2010 14:58 Re: Broken Excel Spreadsheet [Re: wfaulk]
Roger
carpal tunnel

Registered: 18/01/2000
Posts: 5683
Loc: London, UK
Originally Posted By: wfaulk
And a properly designed program wouldn't be using multiple instances anyway; it would just have one program with multiple top-level windows.


I hate that. I prefer multiple instances of the process. Because, face it, the program isn't going to be perfect, so I'd rather have each of my documents sandboxed. It also means you can look at one document while you've got a modal dialog open in another instance.

That said: Excel's implementation of multiple SDI sucks. You might get multiple task bar buttons, but it doesn't behave properly with multiple monitors.
_________________________
-- roger

Top
#328917 - 19/01/2010 15:25 Re: Broken Excel Spreadsheet [Re: Roger]
peter
carpal tunnel

Registered: 13/07/2000
Posts: 4180
Loc: Cambridge, England
Originally Posted By: Roger
It also means you can look at one document while you've got a modal dialog open in another instance.
Is that how it works? Multiple instances must be the way, then. I had sort of thought (but then I haven't done any Win32 programming for a while) that a modal dialog only disabled its owner window (and the owner's children), not other top-level windows?

Modal dialogs are IMO another reminder that your application developers prioritise their nostalgia for DOS days, over your user experience.

Peter

Top
#328918 - 19/01/2010 15:54 Re: Broken Excel Spreadsheet [Re: peter]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5549
Loc: Ajijic, Mexico
Originally Posted By: peter
Faking that by running new instances for each document (here's how to do that for double-clicking Excel files...


Those instructions are for Windows XP, and don't seem to be applicable to Vista. I only get as far as "Choose Folder --> Options --> File Type." I don't seem to have "Folder --> Options" available to me. I went into Control Panel and looked at "Default Programs" but the options available to me there weren't useful either.

I sincerely hope that this can be done in Vista!

tanstaafl.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#328919 - 19/01/2010 16:05 Re: Broken Excel Spreadsheet [Re: Roger]
wfaulk
carpal tunnel

Registered: 25/12/2000
Posts: 16706
Loc: Raleigh, NC US
Originally Posted By: Roger
I prefer multiple instances of the process. Because, face it, the program isn't going to be perfect, so I'd rather have each of my documents sandboxed.

I suppose that there's some benefit in that, and is, in fact, the reason (many years ago) I moved from Netscape as both web browser and email client to two separate programs.

Originally Posted By: Roger
It also means you can look at one document while you've got a modal dialog open in another instance.

Well, that's an implementation bug, IMO.
_________________________
Bitt Faulk

Top
#328932 - 19/01/2010 17:15 Re: Broken Excel Spreadsheet [Re: peter]
Roger
carpal tunnel

Registered: 18/01/2000
Posts: 5683
Loc: London, UK
Originally Posted By: peter
Originally Posted By: Roger
It also means you can look at one document while you've got a modal dialog open in another instance.
Is that how it works?


Err. You're actually correct there, provided the app follows the rules. MFC wizard-generated multi-SDI applications don't, interestingly.

One potential problem is that Win32 message loops have thread-affinity, and Win32 windows have thread-affinity (it's this way by default as a Win16 backwards compatibility thing), meaning that it's possible for the modal message loop to mess up the other windows in the thread anyway.

Similarly, if a process doesn't make good use of background threads or asynchronous operations, it'll block the UI thread, whether there's a modal window open or not (assuming a single UI thread, which is the model for most Win32 applications).

Thus, one-process per top-level window is my preferred way to implement this. You get sandboxing; you get more address space (assuming 32-bit), and you don't have to waste too much memory, because Windows shares pages when it can. You shouldn't even need to worry about using the clipboard between processes, because Windows data objects have that covered. Although, ironically, it seems that Excel's mildly broken here, too.
_________________________
-- roger

Top
#337504 - 23/09/2010 11:14 Re: Broken Excel Spreadsheet [Re: Roger]
peter
carpal tunnel

Registered: 13/07/2000
Posts: 4180
Loc: Cambridge, England
Originally Posted By: Roger
Excel's implementation of multiple SDI sucks.

(Today having to actually look at two Excel spreadsheets at the same time:) AAARGH how did this thing even SHIP in that UTTERLY BROKEN state?

Peter

Top
#337507 - 23/09/2010 12:16 Re: Broken Excel Spreadsheet [Re: peter]
Roger
carpal tunnel

Registered: 18/01/2000
Posts: 5683
Loc: London, UK
Originally Posted By: peter
Originally Posted By: Roger
Excel's implementation of multiple SDI sucks.

(Today having to actually look at two Excel spreadsheets at the same time:) AAARGH how did this thing even SHIP in that UTTERLY BROKEN state?

Peter


The secret: don't double-click the XLS file to open it. Open a fresh instance of Excel, and then open it in that.
_________________________
-- roger

Top
#337511 - 23/09/2010 14:04 Re: Broken Excel Spreadsheet [Re: Roger]
tanstaafl.
carpal tunnel

Registered: 08/07/1999
Posts: 5549
Loc: Ajijic, Mexico
Originally Posted By: Roger
The secret: don't double-click the XLS file to open it. Open a fresh instance of Excel, and then open it in that.
Yes, I've been doing that for some time now. However, there is a caveat to be wary of.

If you cut and paste between spreadsheets that are opened in different instances of Excel, only the data values are pasted, formulas and comments are not copied over. If both spreadsheets are opened in the same instance of excel, then all information is pasted.

tanstaafl.
_________________________
"There Ain't No Such Thing As A Free Lunch"

Top
#337519 - 23/09/2010 15:59 Re: Broken Excel Spreadsheet [Re: tanstaafl.]
peter
carpal tunnel

Registered: 13/07/2000
Posts: 4180
Loc: Cambridge, England
Ah! In fact 99% of the time I'm not double-clicking on them anyway, I'm clicking on them in Firefox (from an internal, actually quite groovy, document control system). I bet that means I can tell Firefox what to do with them. (As opposed to what I'd like to tell Firefox to do with them smirk .) I'll look into that tomorrow...

Peter

Top
Page 1 of 2 1 2 >