The Daily WTF: Curious Perversions in Information Technology
Welcome to TDWTF Forums Sign in | Join | Help
in Search

Excel clears the undo stack?

Last post 03-24-2013 9:58 AM by wrack. 80 replies.
Page 1 of 2 (81 items) 1 2 Next >
Sort Posts: Previous Next
  • 06-04-2007 12:16 PM

    • rbowes
    • Top 150 Contributor
    • Joined on 02-09-2007
    • Winnipeg, MB
    • Posts 412

    Excel clears the undo stack?

    Excel is probably the most annoying program I've ever used, but I'm forced to use it for work. So I deal with its ultra-annoying windowing and weird copying/pasting and all the other little problems.

    However, the most annoying I've found so far is that the "undo" stack seems to erase itself every time you save. So basically, if I do some work, save, then realize I screwed something up, I can't undo. It's gone. I have to hope I have a backup of the file somewhere!

    Does anybody know what the logic behind this is? It seems to me that the most important time to undo is after I've saved the file that I've screwed up... 

     


    And on a sidenote, am I the only one driven to the brink of insanity by the copy/paste implementation? I frequently copy a block of cells, write a caption where they're going to go, and paste them. But as soon as you type, the "copy" vanishes, and I have to go back and re-copy my data! I'm accustomed to the standard clipboard method where, when you copy something, it hangs out on the clipboard until you copy something else. And also, why can't you do "cut" then "paste special..."? I have to copy, paste special, delete. Drives me crazy!

     

  • 06-04-2007 12:36 PM In reply to

    • Pap
    • Top 200 Contributor
    • Joined on 09-12-2006
    • Earf
    • Posts 281

    Re: Excel clears the undo stack?

    It's a pretty irritating program.  OpenOffice.org has free binaries for Windows and can open and save Excel files. You could give them a shot.
  • 06-04-2007 12:40 PM In reply to

    • rbowes
    • Top 150 Contributor
    • Joined on 02-09-2007
    • Winnipeg, MB
    • Posts 412

    Re: Excel clears the undo stack?

    I use that all the time, and I love OpenOffice.org. However, it doesn't support everything we do at work.
  • 06-04-2007 2:12 PM In reply to

    • Iago
    • Top 500 Contributor
    • Joined on 01-12-2006
    • Posts 159

    Re: Excel clears the undo stack?

    OpenOffice's macro support sucks, though. (Or rather, the support is probably great, but the documentation is so useless that it's impossible to use.) Say what you like about VBA-driven Excel worksheets, but you must admit that half our economy relies on them, so they can't be entirely bad! (I speak as someone who tried to switch completely to Linux at home, then gave up and started using Excel in Wine, because while Excel is frustrating, no free alternative can touch it.)
  • 06-04-2007 2:26 PM In reply to

    Re: Excel clears the undo stack?

    I have to admit, the undo stack clearing is quite stupid, although I personally have never had a problem with it and I use Excel every single day in my job.  The copy functions might be counter to what many other programs do, but it does work here, and I can think of many many programs I have used in the past (Photoshop/Pagemaker, etc) that where very counterintuitive to my way of thinking, but they are quite succesful programs none the less.

      I think Excel is one of the most powerful programs on the market right now for the average user.  It has a fairly low learning curve, and you can even get behind the scenes and program in VBA to accomplish some very powerful applications.  This has helped it gain and hold the market share I beleive.

     nuff said.

  • 06-04-2007 2:50 PM In reply to

    Re: Excel clears the undo stack?

    rbowes:
    Excel is probably the most annoying program I've ever used, but I'm forced to use it for work. So I deal with its ultra-annoying windowing and weird copying/pasting and all the other little problems.

    However, the most annoying I've found so far is that the "undo" stack seems to erase itself every time you save. So basically, if I do some work, save, then realize I screwed something up, I can't undo. It's gone. I have to hope I have a backup of the file somewhere!

    Does anybody know what the logic behind this is? It seems to me that the most important time to undo is after I've saved the file that I've screwed up...

     I don't know how Excel has managed to remain so unrelated to every other major app out there, especially its brothers and sisters in the office family. In addition to what you noted, I find that usually when it says "undo", it doesn't say *what* it would undo, and the redo option is almost always grayed out. I don't know what turns it on or off.


    And on a sidenote, am I the only one driven to the brink of insanity by the copy/paste implementation? I frequently copy a block of cells, write a caption where they're going to go, and paste them. But as soon as you type, the "copy" vanishes, and I have to go back and re-copy my data! I'm accustomed to the standard clipboard method where, when you copy something, it hangs out on the clipboard until you copy something else. And also, why can't you do "cut" then "paste special..."? I have to copy, paste special, delete. Drives me crazy!

    Nope. You're not the only one. You don't even have to type anything. I frequently have to do requirements analysis, and the easiest way to get starteed is to merge several documents into one. Generally speaking, clicking on various things in one work workbook, scrolling etc (i'm not sure which action it is in particular) causes the pasteboard to disappear.

    Also I noticed today that the programming language in excel has no infix or postfix operators -- literally none. I wanted to make a cells bold when its neighbors are different from itself. I think i first tried something like "=if(A1 != B1, 1, 0)"...then I realized "!=" doesn't exist, so I tried "=if(not A1=B1, 1, 0)". Turns out the correct answer is "=(not(A1=B1),1,0)".Later I wanted to compare two sets of cells: "=if(and(not(A1=B1),not(C1=D1)), 1, 0)".

     Not that this bothers me personally. I hate expressiveness in all languages...why have to different ways to say the same exact thing?

     

    Edit: I almost forgot the most ridiculous part. Did you ever copy 20-30 cells and then close Excel. It gives you a warning saying there is a large amount of data on the pasteboard, and would you like to purge it? When did a few hundred bytes become a large amount of data? I have 2GB in my laptop dumbass -- for godsakes just keep *everything* in memory.
     

  • 06-04-2007 3:20 PM In reply to

    Re: Excel clears the undo stack?

    savar:

    Did you ever copy 20-30 cells and then close Excel. It gives you a warning saying there is a large amount of data on the pasteboard, and would you like to purge it? When did a few hundred bytes become a large amount of data? I have 2GB in my laptop dumbass -- for godsakes just keep *everything* in memory.

    Excel's erring on the side of caution, which is (usually) better than the other way around.  What if you really need to release that memory and it didn't say anything?

    “Do you know what it looks like when a child has to hopscotch without leg braces, just crawling from square to square?” - David G. Hall
  • 06-04-2007 4:47 PM In reply to

    Re: Excel clears the undo stack?

    In an odd way, M$ should get a bonus point for this "feature" -- it teaches people to do versioning without versioning software in place.  Save it with a filename like spreadsheet.2007.06.04-1627.xls -- that'll solve the problem!  Although for a more complete implementation, you probably should bring the resulotion down to the second level, since multiple versions could be created within one minute.

    Yeah!!  Application Development using Excel! 

    Why does petaflop sound so erotic?
  • 06-04-2007 6:24 PM In reply to

    Re: Excel clears the undo stack?

    Agreed, All of those are driving me bonkers, especially the copy/paste thing.

    And there I was thinking Office 07 makes everything better. That's what Microsoft said, after all...

  • 06-04-2007 7:34 PM In reply to

    • stratos
    • Top 75 Contributor
    • Joined on 09-06-2006
    • Zeeland, Netherlands
    • Posts 765

    Re: Excel clears the undo stack?

    I only use excel to process client transmitted data. (as in they send us a xls)
    most common tasks being some string manipulation foo to generate, formal names. (Mrs J. Johnson)
    and generating, super secure passwords. =right(random(),4)

    My secret technique to preventing all kinds of data loss is to almost always copy stuff to a diffrent sheet/tab and
    mostly use the special paste, only-values-please,-no-you-don't-need-to-think-for-me,-please-no-god-stop-it.

     
    My next to second most annoying excel pet peeve would be the fact that it can't decide if it wants to make a new window for every excel sheet or put them all just in one excel window instance.
     

    My web-consulting company - My web development blog - "Show me a sane man and I will cure him for you." - C. G. Jung - PHP The Anthem
  • 06-04-2007 9:28 PM In reply to

    Re: Excel clears the undo stack?

    savar:

    Also I noticed today that the programming language in excel has no infix or postfix operators -- literally none. I wanted to make a cells bold when its neighbors are different from itself. I think i first tried something like "=if(A1 != B1, 1, 0)"...then I realized "!=" doesn't exist, so I tried "=if(not A1=B1, 1, 0)". Turns out the correct answer is "=(not(A1=B1),1,0)".Later I wanted to compare two sets of cells: "=if(and(not(A1=B1),not(C1=D1)), 1, 0)".

    I could be mistaken, but I think Excel uses the VB-esque <> for "not equal".

    I too use Excel (2000 on most workstations, but one has 2k3) at work, but I've yet to have a strong urge to throw a brick at it. Then again, we don't do anything complex with it like formulas etc, just basic type in data in a table format.

    As for the clipboard, I've had the opposite issue of the occasional thing staying on the clipboard longer than I need it to. But then when I have to copy stuff, it's usually I'm copying it to another app so I go F2 the cell and copy the text out (I've never had to copy multiple cells apart from when I put something in the wrong sheet by mistake - there is one headache I have there in that Excel blanks out the formatting of the original cells when you cut and paste (or even drag) out - sensible, but I would've liked it to blank the formatting by taking the formatting of neighbooring cells/rows). Usually in that case I get annoyed by the office clipboard ring that pops up when you copy something while something else is already on the clipboard or something like that. I only copy the text directly because that's all I want when pasting (since the target is usually a plain textbox) and when you copy a cell and paste into plaintext, the conversion does some weird things if the cell has more than one line. (Namely, it doubles any " character and then puts " around it.)

  • 06-04-2007 9:49 PM In reply to

    Re: Excel clears the undo stack?

    I have documented a load of them on my Excel woes page. Undo in Excel is an absolute abomination. Tip: it's worse than you realise.

  • 06-05-2007 12:06 AM In reply to

    Re: Excel clears the undo stack?

    stratos:
    My next to second most annoying excel pet peeve would be the fact that it can't decide if it wants to make a new window for every excel sheet or put them all just in one excel window instance.
     

     Huh?  Last I checked this was one of Excel's program options.  (In 2003, anyway, it's Tools->Options, View tab, "Windows in taskbar" checkbox.)

     

  • 06-05-2007 4:23 AM In reply to

    Re: Excel clears the undo stack?

    Brother Laz:

    Agreed, All of those are driving me bonkers, especially the copy/paste thing.

    And there I was thinking Office 07 makes everything better. That's what Microsoft said, after all...

     

    Well... I just checked in Excel 2007, did some changes to a file, saved it... and the undo stack is still there.

    I'm not really a microsoft-lover, but this problem seems fixed with the release of office 2007...

    (though hundreds of other things seem to have been broken in the process) 

  • 06-05-2007 10:08 AM In reply to

    • rbowes
    • Top 150 Contributor
    • Joined on 02-09-2007
    • Winnipeg, MB
    • Posts 412

    Re: Excel clears the undo stack?

    Pineconius:

    stratos:
    My next to second most annoying excel pet peeve would be the fact that it can't decide if it wants to make a new window for every excel sheet or put them all just in one excel window instance.
     

     Huh?  Last I checked this was one of Excel's program options.  (In 2003, anyway, it's Tools->Options, View tab, "Windows in taskbar" checkbox.)

    I tried that for awhile, and it was really buggy (Office 2k3). I could no longer double-click files to open them unless there was already an Excel window open, and when the other window was open it would ignore it anyways. But maybe I just had bad luck, who knows?

    <edit> I figured I'd try it again. So I went into Tools->Options->View, and there it is, enabled. Apparently it doesn't work. Or, at least, it doesn't do what I want it to -- that is, have all instances running separately so I can do crazy things like put them beside each other and hit the "X" to close one and not have all of them close.

  • 06-05-2007 12:45 PM In reply to

    Re: Excel clears the undo stack?

    rbowes:
    <edit> I figured I'd try it again. So I went into Tools->Options->View, and there it is, enabled. Apparently it doesn't work. Or, at least, it doesn't do what I want it to -- that is, have all instances running separately so I can do crazy things like put them beside each other and hit the "X" to close one and not have all of them close.

    That feature is supposed to stop Excel from putting individual workbooks into the taskbar. normally you get one taskbar button for the process itself plus an additional button for each workbook. I turned this feature off since it helps me remember that two separate workbooks are running in the same window.

    The only way I can see to open workbooks in separate windows is to start a new excel process, go back to explorer, double click the file (opens 2nd excel process), switch to "New Document" and close it (make sure you use the bottom "x" and not the top "x", and voila! Easy as pie, right?? ....Right guys??

  • 06-05-2007 1:02 PM In reply to

    Re: Excel clears the undo stack?

    No-one who posts on this site knows me (they lurk) but I'm known for encountering a truly inordinate number of design flaws and bugs. I run old computers (main computer is a PII 333) but even up-to-date work machines fare little better. It feels like fate picks on me -- why do I find all the bugs? I do at least make a gallery and a mockery of them all, but even so, it would be nice if programs just worked every now and then.

    There is, therefore, something really rather reassuring that I am not the only person who finds Excel to be such an abortion. I thought Adobe were fractious for managing to give Macintosh Photoshop and Illustrator very different interfaces: Macintosh Illustrator, v8 at least, looks and behaves like a Windows program trapped on a Mac. But Office manages to have programs under the same roof behave in completely unrelated ways. Thank goodness I don't have to deal with Excel much. My Mac version now crashes any time I try to copy data and paste it into another program. No idea why ... corrupt MS libraries? Conflicting libraries with OE and IE? No idea. Ugh.

  • 06-08-2007 3:20 AM In reply to

    • Tiogshi
    • Not Ranked
    • Joined on 06-19-2005
    • BC, Canada
    • Posts 4

    Re: Excel clears the undo stack?

    Here's a real WTF...

    Every branch and tier of the Canadian Government uses Excel. In fact, the B.C. Provincial government will not use Access, or any other kind of database software, because, and I quote from a man on the inside; "Databases are unreliable security risks". So, they use hand-managed Excel sheets with vital and private information, using shared drives and plaintext e-mails to track everything instead. If I sit in the right place in the provincial capitol city with a WEP sniffer, I can hand you the greater bulk of the welfare program in less than a week.

    Also nevermind there's a few dozen degree-holding economists (their degree is even in Economics!) working for the province who can't find their ass with both hands, let alone set up a flexgraph to demonstrate or explore the demographics of some information they're working with.

  • 06-08-2007 5:18 AM In reply to

    Re: Excel clears the undo stack?

    Tiogshi:

    In fact, the B.C. Provincial government will not use Access, or any other kind of database software, because, and I quote from a man on the inside; "Databases are unreliable security risks".

    They kinda have a point. If you let those people use Access, or anything else as complicated and filled with strange misfeatures, the result would be an unreliable security risk (not to mention a spectacular disaster).

    The problem is that their current approach is not massively better. Somebody needs to take away their computers and give them a stack of file cards.
     

  • 06-11-2007 12:52 PM In reply to

    Re: Excel clears the undo stack?

    Daniel Beardsmore:

    No-one who posts on this site knows me (they lurk) but I'm known for encountering a truly inordinate number of design flaws and bugs. I run old computers (main computer is a PII 333) but even up-to-date work machines fare little better. It feels like fate picks on me -- why do I find all the bugs? I do at least make a gallery and a mockery of them all, but even so, it would be nice if programs just worked every now and then.

    There is, therefore, something really rather reassuring that I am not the only person who finds Excel to be such an abortion. I thought Adobe were fractious for managing to give Macintosh Photoshop and Illustrator very different interfaces: Macintosh Illustrator, v8 at least, looks and behaves like a Windows program trapped on a Mac. But Office manages to have programs under the same roof behave in completely unrelated ways. Thank goodness I don't have to deal with Excel much. My Mac version now crashes any time I try to copy data and paste it into another program. No idea why ... corrupt MS libraries? Conflicting libraries with OE and IE? No idea. Ugh.

    Perhaps you should take a night job as a QA analyst? :)

  • 06-11-2007 7:00 PM In reply to

    Re: Excel clears the undo stack?

    RaspenJho:

    Perhaps you should take a night job as a QA analyst? :)

    I don't have the patience or dedication for creating and following proper test plans and withstanding the monotony. I used to do a lot of freeware/shareware beta testing and found a lot of bugs that way. But if anyone wants to pay me to just play with a program and fill a few volumes with its bugs ...

  • 06-13-2007 1:37 PM In reply to

    • NateO
    • Not Ranked
    • Joined on 04-20-2006
    • Posts 3

    Re: Excel clears the undo stack?

    Daniel Beardsmore:

    I have documented a load of them on my Excel woes page. Undo in Excel is an absolute abomination. Tip: it's worse than you realise.

    The answer to Woe #1 can be found, here:

    http://puremis.net/excel/MastersText/text_Nate.shtml#q7

    There's your reverse search. And VBA makes that even easier...

    The answer to woe #2 is to use IsNumber(), e.g.,


     =ISNUMBER("foo"+2)


    Autosave should never be used, and navigating a Spreadsheet should generally be performed via keyboard strokes versus mouse clicks. ;)

    Cheers,
    Nate

  • 06-13-2007 5:16 PM In reply to

    Re: Excel clears the undo stack?

    NateO:
    Autosave should never be used, and navigating a Spreadsheet should generally be performed via keyboard strokes versus mouse clicks. ;)

    Sounds like a fellow "Old School Lotus 1-2-3" user to me :)

  • 06-15-2007 9:40 AM In reply to

    Re: Excel clears the undo stack?

    NateO:

    The answer to Woe #1 can be found, here ...

    Good grief, what on earth is that? I can sense his confidence from the list of non-working or plain confusing examples in there. The "I", "II", "III" etc should be stripped out (they are not actually part of your name), and it doesn't cope with multi-part surnames, assuming Mike B. Clark Griswald has Clark Griswald as his surname. It's hard to tell from that formula how much of it is needed simply to do the surname/forename split and how much is for fancy tricks that don't work. Given that it still doesn't work for all the examples, it might be easier to admit that there's no way of doing it and reduce it to meaningful code :P

    It may well have been easier to use VBA, but I don't work there now. But in any case, it's a deeply complex and unmaintainable solution (I wanted surname and forename in separate columns) that addresses one very specific use case. It does not meet my criteria of offering countFields, nthField, reverseFind etc.

    NateO:

    The answer to woe #2 is to use IsNumber(), e.g.,


     =ISNUMBER("foo"+2)

    Woe number two is that I can't programmatically handle FIND or FINDB failing. I don't know how ISNUMBER helps? I can write ISNUMBER(FIND("test", "is not in here")) and it will not error out? ISNUMBER(`#VALUE!`) is FALSE? Your spreadsheet package would have to be pretty bad to get to a state where you can call a function with a terminal error condition as a valid argument. But then, it is pretty bad regardless of whether this is true or not.

    Sadly, you are correct. It does work. You can take `#VALUE!` as a valid argument.

  • 06-15-2007 1:06 PM In reply to

    Re: Excel clears the undo stack?

    Looks like a valid string to me...

         '#VALUE!'
         '#STRING!'
         '#ERROR!'
         '#WTF!'

  • 06-15-2007 2:31 PM In reply to

    • NateO
    • Not Ranked
    • Joined on 04-20-2006
    • Posts 3

    Re: Excel clears the undo stack?

    Daniel Beardsmore:

    Good grief, what on earth is that? I can sense his confidence from the list of non-working or plain confusing examples in there. The "I", "II", "III" etc should be stripped out (they are not actually part of your name), and it doesn't cope with multi-part surnames, assuming Mike B. Clark Griswald has Clark Griswald as his surname. It's hard to tell from that formula how much of it is needed simply to do the surname/forename split and how much is for fancy tricks that don't work. Given that it still doesn't work for all the examples, it might be easier to admit that there's no way of doing it and reduce it to meaningful code :P

    It may well have been easier to use VBA, but I don't work there now. But in any case, it's a deeply complex and unmaintainable solution (I wanted surname and forename in separate columns) that addresses one very specific use case. It does not meet my criteria of offering countFields, nthField, reverseFind etc.


    He is me, dude. You don't have to talk about me in a 3rd party sense, I'm right here, eh. ;)

    I didn't write that to solve your problem, I wrote it to solve someone else’s, four years ago (I might add):

    http://www.mrexcel.com/board2/viewtopic.php?p=197455#197455

    In any event, it's a Worksheetfunction construct, and yes, it's a little complex, as it's a complex problem! What would your SQL solution look like?

    What it does do is look for the last space (or 2nd last, in cases, i.e., more complexity), which was the only point in my mentioning it. The approach could be tailored for your problem. Of course that might require research and experimentation, when you have time from not building your website. =/

    And of course the real wtf is that you're receiving data in the format in which you describe. Who combobulates all of this stuff into a single cell? You're going to throw Excel under the bus for this? It's Spreadsheet technology, not Black Magic! This is a complex problem which is driven by a DBA, presumably, or someone writing data who doesn't know any better.

    Fancy tricks that don't work? Hey, let's be fair, here, it works as advertised, it wasn't written to solve all of life’s problems, to be sure. I love the assertion of how unmaintainable this is. Unmaintainable by who? You?

    In the aforementioned example, Griswald's the last name and B. and Clark are the middle names. I see you're not following the logic of the construct, the suffixes are stripped out! ;)

    This is handled so much easier with VBA, split the thing into an array, with a space as your delimiter, and do whatever you please with it.

    Daniel Beardsmore:

    Woe number two is that I can't programmatically handle FIND or FINDB failing. I don't know how ISNUMBER helps? I can write ISNUMBER(FIND("test", "is not in here")) and it will not error out? ISNUMBER(`#VALUE!`) is FALSE? Your spreadsheet package would have to be pretty bad to get to a state where you can call a function with a terminal error condition as a valid argument. But then, it is pretty bad regardless of whether this is true or not.

    Sadly, you are correct. It does work. You can take `#VALUE!` as a valid argument.


    That's just it, though, you can handle it, in a non-convoluted manner. It's called error-trapping. Is this really such a crazy concept? The function returns an error when there is no match (surely this isn't Earth shattering?), and there's a simple way to deal with it. And that's not a String, it's an error-return. It really looks like this:

    =ISNUMBER(#VALUE!)

    "Sadly"? I thought you wanted a way to deal with this? Is it no fun that there's such a simple solution to your problem? Perhaps you are, oh, let's see... Trolling? :p

    There's usually workarounds for simple problems in Excel, not always, but many times. Obviously the software's going to have its limitations, but it's pretty robust for what you pay for, it's dirt cheap! You just have to understand the limitations of Excel and proceed from there.

    Cheers,
    Nathan P. Oliver

  • 06-15-2007 2:52 PM In reply to

    • NateO
    • Not Ranked
    • Joined on 04-20-2006
    • Posts 3

    Re: Excel clears the undo stack?

    NateO:


    That's just it, though, you can handle it, in a non-convoluted manner. It's called error-trapping. Is this really such a crazy concept? The function returns an error when there is no match (surely this isn't Earth shattering?), and there's a simple way to deal with it. And that's not a String, it's an error-return. It really looks like this:

    =ISNUMBER(#VALUE!)

    Just to be sure, Strings and Errors are two different Data Types under the XLOPER:

    http://msdn.microsoft.com/archive/en-us/office97/html/SF7EF.asp

    The fact that the function can return two different data types may or may not be optimal, which is neither here nor there, it is what it is. But what this does do is provide you several different ways to test whether the function returned a successful return. Yes, there are other ways to test this aside from IsNumber(), as well. ;)

    Cheers,
    Nate Oliver

  • 06-15-2007 5:12 PM In reply to

    Re: Excel clears the undo stack?

    NateO:

    He is me, dude. You don't have to talk about me in a 3rd party sense, I'm right here, eh. ;)

    I know that now, but I hadn't studied the page you linked to at the time. Only later did I check to see what the page was about and noticed it referenced a "NateO".

    NateO:

    I didn't write that to solve your problem, I wrote it to solve someone else’s ...

    So why are you surprised that it doesn't do what I was saying Excel needs? Computers are toolkits. The aim isn't to provide a pre-made solution to every single problem on earth, but to provide the means to solve all the unknown ones. Your excruciatingly long-winded formula may be the answer in Excel's current abilities to solve one particular problem, but it's not generic and would take a heck of a lot of unwrapping to even begin to guess what it does. I don't even recognise a lot of what you're doing -- functions I am not familiar with. I did finally start Excel 98 on another machine to see if you can pass error messages into functions, but that was it.

    NateO:

    In any event, it's a Worksheetfunction construct, and yes, it's a little complex, as it's a complex problem! What would your SQL solution look like?

    SQL? Why would I be doing it in Excel? It's probably true that VBA would have been better, if I'd wanted to spend the time forcing myself to understand Visual Basic syntax. But I don't get why you think I'd have been writing programmatic code in SQL.

    NateO:

    What it does do is look for the last space (or 2nd last, in cases, i.e., more complexity), which was the only point in my mentioning it. The approach could be tailored for your problem.

    I am sure it can, but I'd have to unravel it all first and figure out what it does. It won't, however, solve any other problems until I figure out the logic behind what you're doing. But think about it -- you're trying to suggest that Excel is OK because solutions of this form exist? Yes, you've found a solution that contradicts my belief (and I will amend my page when I get around to it), but it's a painfully sub-optimal solution.

    Either Excel is lame, or (and this is where my own feelings lie on this matter) we need a separate product geared towards using a spreadsheet as a text processor. Excel is, at the end of the day, a number processor. A lot of tasks involving text fit well within the basic concept of a grid but require a package focused on text processing instead of statistics.

    NateO:

    And of course the real wtf is that you're receiving data in the format in which you describe. Who combobulates all of this stuff into a single cell? You're going to throw Excel under the bus for this? It's Spreadsheet technology, not Black Magic! This is a complex problem which is driven by a DBA, presumably, or someone writing data who doesn't know any better.

    The information was targeted at our database, but came from schools in all sorts of random formats -- Excel spreadsheets, stacks of paper (pen, pencil, laser printed, photocopied...), stacks of card, Word documents, Word documents on floppy discs ... Sadly, there is no hope of ever teaching IT to school staff. They haven't got a clue and you'll never get them to give you anything in the format you want it. Be thankful some of them even know what e-mail is and how to use it. Some of them would have middle names to be removed, too, if they were dumped straight from school records. And to make it more entertaining, they were full of misspellings. If you used the real spellings, the school would complain. The school's spellings, the parents would complain.

    NateO:

    In the aforementioned example, Griswald's the last name and B. and Clark are the middle names. I see you're not following the logic of the construct, the suffixes are stripped out! ;)

    Which suffixes are stripped out? I see "Joe Paul Dirt IV" becoming "Dirt IV, Joe Paul" which, to me, looks like the "IV" suffix is being retained. Nothing appears to be stripped from any rows.

    NateO:

    That's just it, though, you can handle it, in a non-convoluted manner. It's called error-trapping. Is this really such a crazy concept?

    There are various approaches to error handling, used in all the different languages and packages. To me, it's not remotely obvious that a cell containing "#VALUE!" is actually a function return from a vartype function, and is a special yet genuine value (of a different type) that I can subsequently pass into another cell. To me, it says "This cell has no value due to an error." A cell with no value can't then be passed to a function.

    The standard trick for function calls that can fail is to have an error return code. If string elements start at 1 (common in BASIC), then make 0 the return code. Nowhere in the Excel documentation does it remark (that I recall!) that if FIND fails, it returns a special value that you can catch. When you're trying to learn Excel as you go, you don't have time to figure out weird error handling techniques. I do feel that this a slightly tricky situation to handle errors in, but that's where good documentation really helps.

    NateO:

    "Sadly"? I thought you wanted a way to deal with this? Is it no fun that there's such a simple solution to your problem?

    I'm wondering why you're even on a site like this. Do you have to ask? Do you really have to ask? There are always workarounds to everything, but the point is that they shouldn't have to exist. If you've paid one iota of attention to this thread, you'll realise that the whole problem with Excel is that it was broken a long time ago (if not from day 1, but I don't go that far back) and year upon year upon year, it's not got any better. These weird workarounds may have been reasonably in Excel 3 or even 97, but Excel is stuck in a rut.

    Software developers are supposed to improve software to do what humans want, not expect humans to bend over backwards trying to figure out weird systems that don't make sense. It was acceptable in the 2 MHz days, but there are plenty of free cycles in your Intel Core Duo to spend making a program operate in a sensible, friendly way. Don't forget that Excel is not a technical application. There are indications from what someone here said that the latest Excel has improved. About time, too ...

  • 06-15-2007 5:46 PM In reply to

    Re: Excel clears the undo stack?

    Just an FYI...  You can use the ISERR(x) function to convert your #VALUE! into a boolean value indicating whether an error has occurred.

    The fact that ISERR even exists means that you can pass an error condition into a function ;)

     

  • 06-15-2007 6:44 PM In reply to

    Re: Excel clears the undo stack?

    You know, it's definitely sounding like a case of bad documentation. As much as people hate PHP, all the return values of functions are documented well enough that you can make good use of them (regardless of your personal position on "=== false"!) and the examples are useful too. But then, I hardly thing the state of Microsoft's documentation is anything new. But it's useful to know that if I have to jihad on Excel again, I can shop around for info -- you can do more with Excel than MS would have you believe :) Thanks.

    Now, if only there were documented workarounds for the design of the undo stack!

  • 06-20-2007 4:14 AM In reply to

    Re: Excel clears the undo stack?

    Borland C++ Builder v. 6 also has this very useful feature: clears undo buffer on 'Save', and saves on 'Compile'. It was very frustrating when, long ago in student times, I find some buggy piece of code, fix it, run program to find out that i broke it even more, stop program, hit Ctrl+Z and... no can undo! wtf?
    Luckily user could turn both above options off. Builder also had some more features I was finding quite wtfy,
    like immediate breaking into debugger  on any exception ('Why tf this program breaks when I have no breakpoints set?'), allowing to set breakpoints on lines which it cannot break ('I set three breakpoints and it hit none, wtf? aaah I set them on empty lines, stupid me!') and some many more ;)


    As you probably noticed, English is not my native language. It's up to your deportment what you do with this ;]
  • 02-26-2013 1:19 PM In reply to

    Re: Excel clears the undo stack?

    You can necro-bump this post as the undo buffer is not cleared on Excel 2007 and the latest version of Excel.

    Older versions of Excel can also clear the undo buffer when AutoFilter is enabled/disabled or an spell check is started.

  • 02-26-2013 4:56 PM In reply to

    Re: Excel clears the undo stack?

    Thank god you cleared that up. I've not slept for 7 years.
  • 02-26-2013 8:16 PM In reply to

    Re: Excel clears the undo stack?

    In Excel 2007, it is possible to undo after a save.

  • 02-26-2013 11:46 PM In reply to

    Re: Excel clears the undo stack?

    savar:
    I wanted to make a cells bold when its neighbors are different from itself. I think i first tried something like "=if(A1 != B1, 1, 0)"...then I realized "!=" doesn't exist, so I tried "=if(not A1=B1, 1, 0)". Turns out the correct answer is "=(not(A1=B1),1,0)".
    What was wrong with "=IF(A1=B1,0,1)"?
  • 02-27-2013 7:53 AM In reply to

    • Zagyg
    • Top 500 Contributor
    • Joined on 03-07-2008
    • Luton, England
    • Posts 117

    Re: Excel clears the undo stack?

    Tae Wong:

    You can necro-bump this post as the undo buffer is not cleared on Excel 2007 and the latest version of Excel.

    Older versions of Excel can also clear the undo buffer when AutoFilter is enabled/disabled or an spell check is started.

    Well, I'm glad they fixed that. Now try this (in the latest version of Excel): 1. Create a macro to do something utterly trivial (like make current selection bold). 2. Type some shit into some cells 3. Run the macro. 4. Undo.
    Cassidy: Descending into Oracle-bashing is like cattle-prodding disabled kids in a swimming pool.
  • 02-27-2013 9:09 AM In reply to

    • Zemm
    • Top 50 Contributor
    • Joined on 11-26-2007
    • Gold Coast, Australia
    • Posts 1,350

    Re: Excel clears the undo stack?

    nosliwmas:
    Thank god you cleared that up. I've not slept for 7 years.
    Glad to see you're here for the long haul. Are you located anywhere near Inverness perchance?
    Filed Under: Can you think of something that talks, other than a person?
  • 02-27-2013 3:22 PM In reply to

    Re: Excel clears the undo stack?

    Microsoft Excel 2007 allows you to undo after a save. What date is this “post”?

  • 02-28-2013 6:03 AM In reply to

    Re: Excel clears the undo stack?

    Tae Wong:
    What date is this “post”?
     

    It says what the date is right by the date bar.


    In complex analysis, a meromorphic function on an open subset D of the complex plane is a function that is holomorphic on all D except a set of isolated points

  • 03-02-2013 8:39 PM In reply to

    Re: Excel clears the undo stack?

    Clear undo buffer is a feature that is currently enabled in Excel XP and other older versions of Excel, including 2000 and more. MSFT has updated the sort order to ignore single typewriter apostrophe and hyphens during sort; and to ignore accented strings.

    If you want to cancel a save in Excel, click the save button on the toolbar, press Esc and the Office Assistant says Document not saved. If you need someone to abandon the Secret Maryo Chronicles game project!

  • 03-06-2013 6:39 AM In reply to

    • ender
    • Top 50 Contributor
    • Joined on 04-27-2006
    • Sunny side of the Alps
    • Posts 1,404

    Re: Excel clears the undo stack?

    Looks like the funky copy/paste/undo interaction hasn't changed between Excel 2010 and 2013 - both exhibit the exact same weird behaviour.
    Because 10 billion years' time is so fragile, so ephemeral... it arouses such a bittersweet, almost heartbreaking fondness.
  • 03-06-2013 10:41 AM In reply to

    • PJH
    • Top 10 Contributor
    • Joined on 02-14-2007
    • Newcastle, UK
    • Posts 3,836

    Re: Excel clears the undo stack?

    ender:
    Looks like the funky copy/paste/undo interaction hasn't changed between Excel 2010 and 2013 - both exhibit the exact same weird behaviour.
    I've watched that loop round a couple of times and I'm missing whatever point it's trying to make; what is it?
    "Because you watched 'The Very Hungry Caterpillar,' we recommend 'The Human Centipede.'"
    --
    UED - Countryside: To kill Piers Morgan
  • 03-06-2013 11:06 AM In reply to

    Re: Excel clears the undo stack?

    The letters for bold, italic and underline change according to every language. For example, Slovenian changes B/I/U to K/L/P. It is an GIF animation, though.

  • 03-06-2013 11:13 AM In reply to

    • ender
    • Top 50 Contributor
    • Joined on 04-27-2006
    • Sunny side of the Alps
    • Posts 1,404

    Re: Excel clears the undo stack?

    PJH:
    I've watched that loop round a couple of times and I'm missing whatever point it's trying to make; what is it?
    1. copied data is lost when you type something (well-known Excel annoyance)
    2. copied data resurrects when you undo both the text you typed and the first paste
    Because 10 billion years' time is so fragile, so ephemeral... it arouses such a bittersweet, almost heartbreaking fondness.
  • 03-06-2013 1:00 PM In reply to

    Re: Excel clears the undo stack?

    The way Excel deals with multiple documents is complete fucking bullshit and no torture would be too extreme for the people who invented it.
  • 03-06-2013 1:02 PM In reply to

    Re: Excel clears the undo stack?

    ender:
    PJH:
    I've watched that loop round a couple of times and I'm missing whatever point it's trying to make; what is it?
    1. copied data is lost when you type something (well-known Excel annoyance)
    2. copied data resurrects when you undo both the text you typed and the first paste
    Yeeeah... I don't really think the clipboard should be affected by any of that. Excel was designed by fucknozzles.
  • 03-06-2013 1:07 PM In reply to

    Re: Excel clears the undo stack?

    Tae Wong:

    The letters for bold, italic and underline change according to every language. For example, Slovenian changes B/I/U to K/L/P. It is an GIF animation, though.

    Do you really think Slovenes want that? It sounds like stupidity to me.
  • 03-06-2013 1:27 PM In reply to

    Re: Excel clears the undo stack?

    bridget99:
    ender:
    PJH:
    I've watched that loop round a couple of times and I'm missing whatever point it's trying to make; what is it?

    1. copied data is lost when you type something (well-known Excel annoyance)
    2. copied data resurrects when you undo both the text you typed and the first paste
    Yeeeah... I don't really think the clipboard should be affected by any of that.
    The problem is that Excel doesn't use the normal Windows clipboard.  For Excel (and Word too) Microsoft decided to create their own special custom clipboard with extra "features" (that I've never once needed to use), they  fucked it up, and they don't really seem to care to fix it.

     

    I am incoherent in several languages.
  • 03-06-2013 1:44 PM In reply to

    • ender
    • Top 50 Contributor
    • Joined on 04-27-2006
    • Sunny side of the Alps
    • Posts 1,404

    Re: Excel clears the undo stack?

    El_Heffe:
    For Excel (and Word too) Microsoft decided to create their own special custom clipboard with extra "features" (that I've never once needed to use), they  fucked it up, and they don't really seem to care to fix it.
    Word doesn't have these problems. I don't remember when the extra clipboard features were introduced, but IIRC the weird copy/paste behaviour predates them.
    Because 10 billion years' time is so fragile, so ephemeral... it arouses such a bittersweet, almost heartbreaking fondness.
  • 03-06-2013 1:56 PM In reply to

    Re: Excel clears the undo stack?

    El_Heffe:

    bridget99:
    ender:
    PJH:
    I've watched that loop round a couple of times and I'm missing whatever point it's trying to make; what is it?

    1. copied data is lost when you type something (well-known Excel annoyance)
    2. copied data resurrects when you undo both the text you typed and the first paste
    Yeeeah... I don't really think the clipboard should be affected by any of that.
    The problem is that Excel doesn't use the normal Windows clipboard.  For Excel (and Word too) Microsoft decided to create their own special custom clipboard with extra "features" (that I've never once needed to use), they  fucked it up, and they don't really seem to care to fix it.

     

    I have vague, troubling recollections of some kind of fancy clipboard coming out around the same time as Windows XP.
Page 1 of 2 (81 items) 1 2 Next >
Powered by Community Server (Non-Commercial Edition), by Telligent Systems