746 votes125 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
Thanks for the votes! Based on your feedback/votes we’ve just announced the new XLOOKUP function. You can read more about it here: https://techcommunity.microsoft.com/t5/Excel-Blog/Announcing-XLOOKUP/ba-p/811376
Ah, that is wonderful. Very nice.
I encourage everyone to vote on Bill Jelen's idea. This is a once in a lifetime upgrade, or three times a century anyway, so let's help them get it right.
That's awesome to read. The absence of any press on the subject was very worrisome. The absence of blog type applications of them to real world problems, answering real questions if only from those who had them already was exceedingly worrisome, especially as so much time passed. Doing this is bread and butter, literally, for quite a few folks, at the minimum as advertising for how good their training offers are likely to be. And nothing!
Remember when CONCAT() came along, oh boy, and then really just didn’t do what was needed? And we let it mainly die while waiting and hoping for a miracle which finally came with TEXTJOIN()? It felt a bit like that: that MS was way off plan because the spill functions were turning out to be pretty flawed, or at least nothing special, and they were limiting their spread in the wild so they could improve them. Seemed like that, maybe, and that they would die like CONCAT() and in some years’ time a new version would rise, really work, and then be really rolled out.
Maybe I should write dark fiction of some kind, but to me that’s how it seemed it could be going.
And… I feared for XLOOKUP() too. As you say, it clearly dips into the spill functionality at the very least, and MS kind of implied that the spill engine would actually come to underly everything so it seemed likely it was based in it, not just tapping into it, and then, well, and then refer back a couple paragraphs and I feared it could be sucked down if the spill functionality had issues.
Contrarily, I feared, and still do, that it might actually be “special passengered” through the early roll-out stage with no improvements really allowed before release to the wild. Bill Jelen already has one suggested add to it (an error handling argument so no need to wrap the search in something to handle errors) which had 27 votes already this morning. And I, among others, would still like to see it do things like take multiple lookup values (like a first name column, a last name column, even 3,4,5 columns for the lookup value), return more than one column’s contents, see it definitely be able to be applied through ad hoc tables created with CHOOSE(), and very important to me, return a “table” (spill range being the likely physical presentation) of results from ALL the “hits” in the lookup range—so, say, all 147 instances of a customer ID presenting in a spill range that I could operate further with as a subset: replacing filtering capabilities with formulaic filtering.
But your news is GOOD NEWS regardless! I can hardly wait!
And just when I was getting used to the idea of changing over to Index-Match… whew! Dodged that bullet!
Well that's almost awesome. Almost everything one could want.
No complaint. Frankly stunned to see something is actually happening.
But... I was told about the new "Spill" functions a year ago, almost, and that they would reach regular monthly updated people like me early in 2019.
So... I'll hold off the palpitations until the day I type =XLOOKUP( and the function help appears instead of nothing. Been checking on the spill functions that way for a long time. And honestly, I can't remember EVER seeing that those have made it out of the hands of the "some in the Insiders program" they were initially rolled out to. All the sites that gave gushing notice... that's all that ever happened.
I have a question though. I'll keep using this for the next year or three I guess, but I wonder why I could never find anything about the INDIRECT-MATCH combo. JUST them, not combined with VLOOKUP or OFFSET. Could go left (biggest point really). Only picked one column and a cell, so no extra calculations when something in a range/Named Range/Table's other cells changed, except, you know, INDIRECT is volatile. Simple, straightforward. Just never read anything about it.
Say the lookup column is h3:h7, the target column is G, and the lookup value is in 10. Then:
MATCH looks in F10, searches H3:H7, "0" is aking for an exact match, so sorting doesn't matter. That returns a row, then INDIRECT pairs that with "g" to give a cell to look for a value in and return it.
You know the column you want, so the "g" isn't a mystery and somehow unfair. You'd enter something like G3:G7 in an INDEX-MATCH formula so really not unfair. (Adjust accordingly for one of the 27 horizontal lookups ever done in history.)
I suppose it's because INDIRECT is volatile. But I've never been slowed by it.
Oh well. Back to hoping for a really short wait on XLOOKUP! Yay!
(The above is just me getting a first reminiscence in, you know, the "walked uphill to school and uphill back home" kind. "For the kids" who just look at us blankly in five years when we talk about life before XLOOKUP.)
Oh, and to Mr. Wyn Hopkins!
That Autocorrect idea is shear brilliance. It can clearly be useful in quite a few instances. I will use it myself and teach it to others, especially those who find Microsoft function definitions and help somewhat... abstruse... and therefore hard to figure out if one does not already know what each element is already which rather defeats the purpose in most cases. Expert "speech" ≠ Expert "teach" in quite a bit of their support site help.
Just brilliant. Simple, complete, brilliant.
Related, but no longer explaining those requirements, conceptually a formula is all about Excel providing building blocks with which one can nimbly piece together functions to solve one’s UNIQUE need of the moment. One needs general functions that do not attempt to solve world hunger on their own, but rather are small, exact, building blocks. HOWEVER… some things turn out to be more general and are needed often by everyone with just small, easily defined refinements to match the unique needs they of their moments. Lookups are one of these. So then a function of broader scope than, say, SUM(), is needed. Yes, just as there are workaround for summing, there are workarounds for lookups. But workarounds are hard to learn for many, and very hard to adapt to their needs because they do not understand them. They usually are not extensible for the same reason. If MS wants to sell to a wider audience, a simple function is needed. People who have a solid handle on the workaround can sit all as smug as their characters dictate and keep using their workarounds. Everyone else would use the new simple function. I can “go left” with VLOOKUP() using a negative number for the column (and in string functions as well). It takes some work, a wee more than INDEX/MATCH, but it’s not unbearable. I don’t use it often. Workarounds are a pain. I just want to enter a simple function modelled on the current VLOOKUP, mostly, and be done with it. So does literally everyone else I personally know.
Lastly, and this is why Apple does so well with so many products, almost no one in the world wants to intimately know how their car works and to then tinker with it before, during, and after every use of the car. They just want to drive it. MS has to provide this “toaster” simplicity if they want their products used 10-100 times as much and as widely as they are now. A universal lookup function that takes easy care of all possible lookups would be quick to learn for 90% of its usages, and if one had to learn to use some of its features better to take advantage of its more advanced capabilities, one could do it that time, perhaps forgetting afterwards. It would, in a clear and obvious way, handle the basic, simple VLOOKUP() work we see so much of. And if you wanted to do more, you could just by widening your thinking about its parameters: “OHHHH… not just go over 3 columns, but to another sheet too… ohhhh… I never thought of that before…” No new function to learn and explain to anyone validating your spreadsheet, just a tweak to your usage of an old parameter friend.
Folks, we simply need one function, fully enabled, to seek and find for us. Not limited by defining a limited, structured, lookup need, and then created but rather created to seek and find, then return a desired result.
So, a function that
1) Takes an input value
2) Hunts in a given range for it finding all occurrences of it (either reporting a list of them, or evaluating further from the list)
3) Allows one to choose a value OFFSET from it (or each) and return that value (or values). An allowance for returning an ADDRESS instead would be useful.
4) Allows one to select one of many results (instance number) including a provision for “last” and counting back from “last.”
5) Provides for a defined “error” result in the manner that IFERROR() does, or Excel’s own error result if that is not defined.
To further explain the above:
1) Pretty obvious, but included should be taking any result from a formula for this but “cleansing” the physical form of the formula’s return so that it does not itself cause an error as invalid input. (Ever use F9 to evaluate portions of a failed formula and found that the failure point (when the formula evaluates on its own does not cause failure when you F9 that portion and then hit Enter? That’s because the F9 process cleansed it somehow while Excel did not as it stepped through. The other way around happens too.)
2) ANY range given it. Period. Single cell. A “Table” or a block of data we plebs think of as a table. A range built of subranges using the various joining methods available. Something CHOOSE() creates. And folks… 3-D ranges. “4-D” even, if one thinks of different files providing the 4th dimension.
3) So, conceptually, picking the column to find a result in is currently a very simple, single value offset. And probably the offset almost always desired, honestly, but the function should not inherently limit itself to this. A single value, like 3 or -4, should default to a column 3 to the RIGHT or 4 to the LEFT, but a full 3-D offset should be available (or… 4-D).
4) It should internally locate ALL occurrences of the lookup value. It should make this list available for output. It should use this list as a table for “sub-lookup” to return values (or their cell addresses if desired) quickly. One should be able to specify the “instance number” desired, or one chosen from a range of instances, the literal “last” instance, and of course, counting back from the end as well as forward from the beginning.
5) One of the huge performance hits in Excel is the double evaluation inherent in a huge number of IF() usages. You know, IF “some long formula” is not an error then “some long formula.” IFERROR() improved that, though not useful for all instances since it just handles error results, but the idea here is to enter the lookup with any “some long formulas” involved as its sub-parts, and if it returns a null (as in “no instance of the lookup value was found” rather than one was found and the lookup location it returned was an empty cell), then return some defined result if one chose to define one rather than some Excel error. This way one need not wrap the lookup in an IF() test for this, evaluating it once for the IF() test, then returning it as a result if that returned TRUE and so having to evaluate it a second time.
Call it ULOOKUP() (for Universla lookup.)
911 votes173 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →
Thanks again for all the passion on this issue – we hear you and we’ll get someone on the team to dig in to the issue. I’ve seen a few related sub-issues while scanning over the comment section for this one, so we may reach out to a few of you for clarifications. Thanks again for all the votes, and keep them coming for the issues you care about!
Now you can be confused since I began and ended with opposites. Hint: I voted for this quite a while ago.
There's this thing called sarcasm. I may not have been deft with it, but even poor sarcasm is usually recognized as sarcasm. Perhaps a clearer version would "Hey, MS just solved the single Undo stack issue. They withdrew Excel from the market..."
In any case, in general, I like to try to understand how a thing came to be and to give "props" when due. I think many do. But it doesn't in the least let me live happily with this sad situation.
To be fair to my point, I was pointing out that the single-sheet version of affairs was similar to our world of single but multi-sheet spreadsheets. And that for the last 26 years there's literally no reason on Earth why the shared Undo stack would have been kept.
It sets the sitch in stark terms. A lot of people don't even live 26 years. And this drags on. With no excuse or reason ever presented. Since I haven't sunk to the misery of not thinking on it anymore and just building hatred and resentment, I occasionally speculate. Not asking for forgiveness either. My life will go on with or without this. A part of it will suck, but that's all. And I'll still want individual Undo stacks the whole while. (Sorry, I couldn't fit "whale" into that.)
Rob... my man... MS DID solve this already, made sure each file has its own Undo stack!
It's called (the horrible and monstrous forced shift to the world of) SDI. If you don't fight Excel, it will open a new instance for each spreadsheet and those instances are almost as unaware of each other as Spotify and Quicken are of each other.
Including individual Undo stacks. Oh boy!
Of course, fight progress and open your spreadsheets in ways that open in a single instance and those spreadsheets that are completely separated from each other via SDI (which operates regardless of single instance or multiple Excel instances) are suddenly still joined together via, amongst a few other things, their single, shared, Undo stack.
But you really can have separate Undo stacks if you just open instances willy-nilly.
And oh yes, it surely should be an easily set user preference. Right on the money there!
But it is not by any means a "pro" feature. Even if you just need two spreadsheets open at once for manual work you intend to do in each as required by your job's minute-to-minute needs, you will sooner or later suffer with a single Undo stack. Or suffer from the disconnection between instances of Excel. And there are at least 10 other situations I expect arise very often amongst us all that are nothing pro-like, just average Joe-like.
Admittedly, almost nothing is the end of the world here, usually one can give up his work by closing without saving if needs be. But it SUCKS to lose work for such a simple thing to fix. Sometimes it's a LOT of work, but even still, it's not like a tornado ripping up Xenia. It just sucks though each time you end up in that bind and it should never even be able to occur.
By the way, I think you have the lead on an important "how it came to be": Until about 1993, there was a single sheet per spreadsheet. If one needed multi-sheet functionality, one had to make several, or many, spreadsheets that would work together. Still separate files though. A single instance of Excel-wide Undo stack makes pretty decent sense then, it would actually be enabling for many, though not required. Maybe how we ended up having it so long before it's last need for being went away.
As soon as multi-sheet ("tabbed") spreadsheets could be created, most things settled into single spreadsheets and so a private Undo stack for each multi-sheet spreadsheet would have kept that equivalent functionality without hindering the future.
Yet... here we are, relegated to the "hinderlands..." (sorry, couldn't resist)
Right hand, left hand, neither knowing the other exists.
I'm not sure if I should wish they shared best practices around the company (or had done so, long ago), or if I should be happy to see a monopolist miss a pretty good trick (the sharing of best practices).
Seriously though, as I described in more detail lower down here, close an open workbook and they have no problem whatever identifying the Undo elements registered to that workbook and deleting them from the stack. It's not like you re-open it immediately and they're still available. So they can already identify what goes with what. How hard... oh... yeah, not hard. Must actually be a motivation issue.
(Not really clear why macros have to wipe out the Undo stack either. Never seen a plausible explanation for that.)
Posting the 1st half now, so it is in order. Here. Sadly, that will make the halves come backwards in any emails...
Have a SINGLE INSTANCE of Excel open and IN IT create 3 files, A1.xlsx, etc. Make some clear entries, patterned for each one, like 1,1,1 and 2,2,2 and 3,3,3.
Try UNDO. That last 3 goes away, no matter which file you are in AND, unlike described following those links to different places after them, you go to that exact cell in that exact spreadsheet and see what went away. No "hidden" Undo's occur. Yay. BUT you were in A1.xlsx when you hit UNDO and wanted the last thing done there to be undone.
Of course, that did NOT happen and CAN'T happen which is why this Suggestion exists.
Those links are said to make the point that this is programmed in in such a way that it CANNOT be undone, reprogrammed, overridden... it is an aspect of MDI and flat cannot be done any other way. Those poor dears at MS have no physical way to override that behavior so this is an outcome. Choices? Well... no UNDO at all, of course, or SDI and then it never comes up to discuss so problem solved. Whew... no more dirty bathwater, problem solved... hey honey, where's the baby? He was here right before I threw out the dirty bathwater and now I can't find him... solved the dirty bathwater (UNDO) problem though.
In the links, a person mentions that closing one of the contributing files wipes out its contributions to the Undo stack, but leaves the rest. Yeah, that happens, both with the 2016 I have now and the 2010 I have still at home. Another person says no... yeah it wipes out that file's contributions, but only by wiping out the entire stack, all gone, not just some. His point would seem to be the "nothing at all can be done with it in an MDI world." But he is wrong, and so is his point, and also that of anyone else saying so.
You have the above work. Close any of the files, the one with the last changes (so hitting UNDO would have Undone something in IT), saving or not saving first. Try UNDO. It doesn't open that file up and Undo that last thing. Even if you saved it with the changes so that it had something to undo. That file and its contributions to the Undo stack are GONE. Open it, make changes, save and close it, try Undo (it acts on the other files since that one is gone). Well... open it back up. Now:
1) When you closed it, the Undo stack KEPT its contributions until the Excel instance would be closed and now that you have opened it back up it will Undo things exactly as if you never closed it because it can't do anything else.
2) When you closed it, its contributions from the stack were removed. Gone. Never to return. Or at least marked to be permanently ignored. So opening it back up can't reactivate its contributions. They don't exist anymore.
Posting the 2nd half first so it appears after the 1st half, I hope. (It seems I write too many characters sometimes.)
If it is 1), nasty things will happen. Saved material will be subject to being undone. That might be the least of it. Making that impossible might have been the whole point of your saving, closing, and reopening. Nasty, nasty, nasty, on a whole new level from what we have now. But it doesn't happen, not at all. Those things are actually gone, so it is 2), above.
Alrighty... if the actions are removable then they are ignorable. In other words, if in the MDI world, Excel can now ignore the fact of those actions being in the Undo stack and make no use of them, only acting on the items the never closed files contributed (easy to ignore something that doesn't exist any longer, right?), then it can certainly ignore them for other reasons.
YES... for sure there might be reasons you would not want to and the program's guiding lights might have gone with the "hard choice" and made the Undo stack the monster it is today. Sure. Maybe. I know none of us would agree that that is so: about "reasons" and hard choices that is.
However, no matter what they or anyone else says, that is all a lie. It is a lie because it has been said (mainly to avoid the work, I guess) for 25 years now, since computer generally had enough power and a worthwhile Windows version (3+) to do menaingful work on more than one file at a time.So while an off-the-cuff, first time I thought of it remark might be "wrong", going on with it, stubbornly, for 25 years makes it a lie about a month or so into the 25 years.
If the stack can keep track of the spreadsheet the contribution came from (and it can or change cell A3 in A3.xlsx, switch to A1.xlsx and hit UNDO and you would see A3 Undo all right... A3 in spreadsheet A1.xlsx, not in A3.xlsx... it would act relatively because it did NOT know which spreadsheet put that act into the Undo stack... which is clearly and absolutely NOT the case!), then a trivial amount of code can look at a setting you have chosen and decide whether to ignore all contributions from the other spreadsheets and on UNDO in the one you have up (or not, if you set the setting the other way).
Yes, that could seem to lead to issues, but not really since it would be Excel-wide, not spreadsheet by spreadsheet that one made that choice. The other aspect for trouble would be "tree-ing", akin to forward and back in web browsers, and having to deal with the situation in which you have ten things in the history, go back 3 to the 7th, then click something new and it becomes the new #8 while the old #8, #9, and #10 are permanently lost. Same idea.
So NO, FLAT NO, this is NOT NOT NOT a physical impossibility. It just ISN'T. If I can see that, for G*d's sake so can 3,000,000 programmers. And their managers and strategic guiding hands. This CAN be done, and with the same trivial code college kids use to write browsers for a grade one week in some semester's class. It literally is not rocket science. It's TRIVIAL. Period.
NOT doing it is a choice, not a physical law of the universe. A choice. A bad one.
Guys, I'm done arguing the point with the following last bit:
No. And further, I have two instances showing up in Task Manager and Ending one's process leaves the other open and happy.
That is from opening them by opening files from Explorer one at a time. One istance opens, then the second. And third. I did the screen capture after Ending it so it doesn't show there.
Normal file opening techniques yet separate instances like when you forced them for years if you had to make Undo not a problem. NOT NOT NOT opening normally and creating separate windows but really being one instance.
And no related Undo stacks.
Well, I guess I don't see how to add a file to the comment, so no screenshot.
Yes, exactly... IF you have a single instance of Excel open. For instance, I can open Excel, then open a second file in that instance with File|Open. Those two files will behave precisely as you relate.
However, if I open an instance of Excel, and a file in it, then open a new instance of Excel (using Ctrl-Click for example, in the wonderful version I used to have that supported MDI), and open a file in it, the two files I have open are UTTERLY separate when it comes to the Undo function.
This has been the case for a very long time, and for those of us who had to edit a couple spreadsheets at the same time (editing one, say, and the boss calls and wants you to edit a second one right then), we would open a second instance, on purpose, with Ctrl-Click. And the two would be safe from each other.
The problem happens when Excel releases versions that do NOT support MDI, they are SDI only, out of the box. Then the Undo stacks are separate, by default. Hey! This problem is solved! No more whiners like me!
People claim it was meant to solve some esoteric problem that I've never seen or heard of involving two or more monitors. Maybe. But I have plain vanilla equipment and never saw any problem yanking maximized Excel windows from monitor to monitor or even extending the workspace over both, so I don't buy it as a big enough problem to need solved over MY back.
And htough I meant the below comment about them doing it to solve this problem as a bitter, bitter, joke, the longer I think about it, the more it seems like they meant it to "solve" this problem.
Rememmber, if the second instance you open does not have a problem with your Personal spreadsheet already being open, then it is simply a new window for the first instance, not a separate instance like we see.(Or you don't have a Personal workbook. Lol. there's that possibility.) Open something, then open (Ctrl-Click) a second instance, see the warning about the Personal spreadsheet being in use already, then open a new something there. Then check the Undo function like I mentioned.(Maybe the mention is in the MDI suggestion.) You'll see what I mean.
"Anonymous" posted the Support link I could not remember:
(posted it after my comment in the MDI suggestion:
Adding my comment, because he was doing more than giving us the exact link:
"Yep, that's the one.
In the Support article it implies/makes clear that if the entry does NOT exist, you have MDI. MAYBE that is the case in a version that has MDI for a default. Hoever, my Excel 16 has SDI as a default and I was strictly limited to File|Open or everything created a new instance. After creating the entry and giving it the value of zero, rather than no entry at all, I got some capabilities back, for instance, being able to drag a file from Explorer onto the instance of Excel and it would open in that instance where before creating the entry, it would open its own instance.
So take the "no entry at all is the same thing" that they either say or imply with several grains of salt."
tl;dr? Make the entry even though it implues (or states) you don't need to. The instance above that I mention as probably when no entry is fine will NOT be the case for anyone suffering from the SDI issue so make the entry.
No. If I have two instances of Excel, they have completely separate Undo stacks.
I just experimented in case I was wrong. Completely and absolutely separate. If I fill four cells in one and four cells in another, the hit Ctrl-Z eight times, the first four are still populated. Fill the second four back up and hit Ctrl-Z, the fourth one empties. Switch instances, and Ctrl-Z empties the fourth celll in the first instance. So, no. They are NOT interrelated.
I meant it as a joke anyway, of the bitter variety. (Which is why I experimented before replying, in case my observational memory was not utterly correct.)
As to SDI allowing the movement of files between monitors, well, maybe it does. HOWEVER, as I noted a few weeks ago, I could ALWAYS do precisely that between my two, completely cookie-cutter monitors and my one built on the Dell motherboard graphics card. Maybe people with better hardware have issues, but I never did and I absolutely will not buy that I have some "perfect storm" of hardware, coincidence, and providential choices with bizarre setup otions that every other person in the world missed.
So... No to that as well. Whatever it DID alleviate, it was not an endemic problem with multiple monitors. To be honest, it was probably a stock watcher complaint. Gamers. People using systems meant to run on obscure combinations of settings because full-bore, cutting edge tech just isn't perfect. The rest of us, I'm betting, experienced what I always did and never bought the other anyway.
And finally, SDI means one instance after another of Excel. Literally the same as opening Calc and Excel give you separate instances of programs for spreadsheets. MDI allows a hundred files to be opened in one instance using ANY method of opening Widnows itself supports, not just opening from that instance's File|Open method.
With that registry fix I mentioned, I have some of that back. Just not all, and since one glaring one lacking is opening atachments from Outlook, it's still a huge problem for me, not just an occasional annoyance.
But no, the first thing is completely wrong. And the second is not a matter of any concern for any average user. Nice they fixed it for a few unhappy people, but did they have to ***** 130,000,000 others to make 50,000 stockbrockers and day-traders happy? No.
Hey guys... they solved this already!
This has to be what SDI is all about, right? If every file is opened in its own instance of Excel, then every file has its own Undo stack, right?
For MDI rather than SDI, see:
but, hey, don't support it! It solves Undo completely! Yay...
Office does not use the Clipboard the way Windows pushes it to others and never has. They did other unfortunate programming along the same veins. The "Undo stack" is one of them. They took the core of the program and made the shortcuts when they reset it as Excel and positioned it to work properly in Windows. They "rolled their own" instead of using their own Windows tech. So now... it'd too deep to rework easily. And I, for one, suspect they DON'T muc like things that don't lend themselves to "easily done."
Accordingly, this kind of request simply involves too much effort on their part. If they could have rewritten their usage, they would have decades ago.
Nothing has a higher level of votes. Or a higher level of frustration and pain. And yet, even though THIS suggestion has been here, buoying to the top endlessly, you'll note the Admin comment came 2½ years after it existed. And the comment is a pseudo-"we're looking into it, we really are" kind of comment meant to string us along rather than either promise solution to the problem or meaningful commentary on how they are noticing, after getting into the meat of what needs done to solve it, that there are several strains of interest and therefore several problems they need to work on.
Instead, we see a mendacious comment about sub-issues meant to: a) Temporize, and lull us for some length of time, and b) To imply there really are sub-issues ifurther implying "boy, this will take some time here" and "it really isn't one thing, so all those votes are really not so impressive"...
Thank you "John, Excel" for your shilling effort.
But there is ONE single issue here, ONE single need, and there are absolutely NO sub-issues. There isn't even anyone with legacy work that would be destroyed, or someone who just doesn't want to change 30 years of keystroke habits. No one is arguing any point at all in these comments. No one thinks we are better off like we are. There are absolutely NO sub-issues to balkanize the support or to complicate solution efforts. This is NOT like the people yammering to return to every file opened is in a new instance of Excel and can only (poorly) use the Clipboard for interaction and after every one of those suggestions, a following one demands a return to every file opened is in one instance of Excel and nothing is shielded from the rest. There is ONE SINGLE DESIRE here: Undo stacks for each file, nothing to do with each other, and if you want changes reverted in more than one, you do that, yourself.
And they don't even promise a solution, just that after getting a stunning number of votes and 2½ years of being noticed by them (yea, I know, good boy John is suggesting they just noticed there in March), they are going to use mendacity to ease us along for another year or two (it's been another ½ year already).
This has ONE root: their own programming choices decades ago. For the love of God, they should just add code to work as desired and trap the calls to the sad legacy work sending them to the nice, modern code yielding the nice, modern (30 year old modern) need.
Mendacity. It ain't just a funny word from "Cat On A Hot Tin Roof" Excel. It IS pathetic though. Sad.
But yeah, Anonymous, it is an EMPTY response.
Oh, sorry, I was rolling way past my fingers in the lower 640K thing... I didn't mean to point it as if I meant no one thought computers would ever have more memory or that anyone could write a program that needed more. Because people said that was so much, professionals, not users, it was why would anyone spend the money (that sense of why would anyone ever buy more than that). Just buy 192K, say, and run the little programs of the day. You're unloading each one to run the next, so it's all there for it! Err... except for the footprint from DOS. But seriously, set the lowest 384K for the system instead of the highest... IF there is any... and now you're forcing people to buy 384K for that alone, then more and more to be able to run programs.
So the guy with 192K for programs would have had to buy three times as much to have that. (It warn't cheap either.) NOT the best idea to underpin your marketing on. For all I know, IBM knew and approved. And people did write worthwhile things in space like that, though lots wanted to have infinite memory. Another for all I know is that MS nannied that by forcing them to limit size and therefore shiftless programming.
But whatever the set of concerns, the 640K thing blew up on the DOS world but was NEVER EVER fixed. Because of ALL the compromises and ALL the "dishonest" programming (Did I do that? Address hardware directly? Yes, "Urkle, Excel", you did.): fix it and every program addressing that memory would fail. So it was never fixed, just bumbled along. Like this and a few other things.
Anyway (rolling again), lots of folks would've bought a Mac if they could have gotten a second mortgage instead of a PC if they had to spring for a ton of memory or else. That was the meaning I meant to convey. And Lord, a Mac world is just all we need. (No need to even bother with the SARCASM() function for that last sentence!)
Lol, they hear us.
This is one of those things whose underlying reasons were no one programmed "honestly" once upon a time, for Windows, not even the owner of Windows, and then there were choices one had to make that limited one's future options because something limiting was chosen and then inserted deep into the code.
(For limiting, remember how the obvious choice of using lower memory for DOS programs (Because who would ever buy more than 640K, right? And by then, they'd be migrating upward and away from DOS and replacing every program anyway... yeah, remember the telephone company mode of progress? And cell service providers today?) led to years of limitations? And who ever chose those ridiculously small memory heaps for running Windows? "Resources" is a four-letter word to my gneration.)
For an example of something with a wonderful utility (possibility) that wastes along, fairly (not completely) useless and never upgraded to its logical wonderful capability and launched across Windows:
Buried too deep to drop and too deep to make sing with greatness, it limps along and makes a few of us happy, but mostly makes anyone who thinks about it... just sad...
Don't get the idea this last year or two is the only time period they've hear (or seen) complaints of the quantity and quality herein. It's been something to hate for decades now and surprise... it has been! Even the laziest users ever that still have enough gumption to say "Well, what if I change that value? What could go wrong? I'll just UNDO it in a second, oh, hello boss, yeah, let me bring that other spreadsheet up..." has regretted this and sworn at MS for it. And his little boss too.
So thank you "John, Excel" (who has a middle initial of "," anyway???) (oh... need that SARCASM() function again, to wrap that kind of thing in, or a set of related functions... a lot of people would hate the "invalid argument" error message when trying to use (misuse) the IRONIC() function, eh?) but you folks ain't doin' nothin', not soon nor never. Except gladhanding us along. I'm less than 60 and I will die in my sleep before you do. So keep that gladhanding to yourselves please:
HOPE springs eternal, they say, so even the above hurts. A "corporate lie" is still a lie "John, Excel." And the hurt is the same.
Indeed, about cross-workbook links. It is hard to see how, when the work is always one-way (note that linking back and forth is two one-way flows, not somehow a single flow).
Link from A to B and the work is in A's undo stack. And vice-versa. Not complex at all, and I doubt complicated either.
The truth is that this must be a function that is utterly buried in original programming and MS fears what they might break by attacking it. But that need not be a problem. One simply programs the good Undo stack approach using what I s'pose would be called a new stack, distinct from the old stack's place in memory, and adds a bit of code to make the old approach's stack simply a dead end - anything that reads it is directed to the new stack.
"Simply" being... semi-loosely defined of course.
But it would make the old code dead since it can't affect anything, and give us a properly functional, modern if you will, Undo stack. With code that can be accessed in the future.
Wow... all we have to do is keep voting! Over two years since ADMIN pumped us up with that "Under Review" and 509 votes and about 1 person in 10 even comments...
... and ... here we are still wishing without even a mendacious update from ADMIN to keep us pumped up...
Seems to me I remember Pavlov's experiment had some data showing dogs, after conditioning, still having some degree of salivary response that was measurable as far as 10,000 trials without rewards. Something to consider?
Since nothing else seems to be getting considered here.
5 votes2 comments · Excel for Windows (Desktop Application) » Other · Flag idea as inappropriate… · Admin →
Yes it would.
******** password display is one of the stupidest things ever created. (Requiring passwords to be changed on a regular basis is another.)
One should at least have a click-to-display full plaintext of a password if desired. At an utter, absolute minimum, when establishing the passwordd.
(Only time I ever saw a use for the ********* masking was a car company for their car dealer software that put up random numbers of *'s when each character was typed so an eight character password might show 11 or 17 or 15 or whatever *'s rather than exactly eight. Still not enough value though. Sure beat the usual "Here, let me show you he uses 7 characters so you don't waste time crunching those pesky 4, 5, and 6 character versions trying to ***** it..." dislpay of one * for each character.)
By the way, to be safe while holding your breath for them to fix this, you can type it out somewhere else, be happy with it, then copy and paste it into their dialogue box. Sucky workaround, but it does work.
2 votes0 comments · Excel for Windows (Desktop Application) » Charting, Mapping and Visualizations · Flag idea as inappropriate… · Admin →
3 votes1 comment · Excel for Windows (Desktop Application) » Viewing / Navigating Workbooks · Flag idea as inappropriate… · Admin →
3 votes0 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →
2 votes0 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
2 votes0 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
Found another workaround for the decades until Excel does this. Fairly clever, the fellow used an AND() to hold his formula bit and also to hold his comment making sure to test the comment in a way he could be sure would come out true so it had no actual effect, only the true/false from the formula bit would matter as the comment half would always return true to AND().
And basic example:
=IF( AND( A1>=600, ISBLANK( "A1 holds the balance due on the loan. This formula checks that the balance is higher than the regular monthly payment. If not, it returns the formula for payoff result." ) = TRUE ), A30, C30 )
and one can use Alt-Enter to make that appear on separate lines in the formula editor:
=IF( AND( A1>=600, ISBLANK(
"A1 holds the balance due on the loan. This formula checks that the balance is higher than the regular monthly payment. If not, it returns the formula for payoff result."
) = TRUE ), A30, C30 )
so that the comment stands out. You can do that with the approach below too.
If it is of any assistance, you can wrap those text portions, EXACTLY as they are right now, do not drop the double quotes, in the N() function.
So it would read:
= 431 + N("explanation 1") - 1 + N("explanation 2")
The N() function has been used since, oh, Shakespeare's day for this purpose (that of sort of commenting inside a formula).
That won't help you convert directly, though one can use some, or a lot of, typing to convert formulas you'd hate to have lose their functionality.
Another approach would use FORMULATEXT() to get them into strings, then standard text manipulation to locate where the double quotes are and insert the N( and the ) strings as appropriate, or break them into cells with Data to Columns and recombine via concatenation, or insert the needed characters, then use TEXTJOIN().
Still probably hand done, so tedious, and not overly amenable to a macro. But it would give you a way to keep the functionality and probably see it last.
Another way might be to check into Google's offering. I do not know but it might handle them as is so you'd have immediate functionality back while you consider what to get after and what to let die a sorry, unnecessary, death. Or to have use of while getting after it in a different way, rewriting the spreadsheets. One might, for instance, use them with Google while editing to put either the whole formula (probably easiest) into a Comment for each cell to keep the comments, then editing them out of the cell's themselves. Or one might one-step it extracting the comments into current Comment functionality leaving the formulas workable in the single edit. Or just edit them away, just Find and Replace ("*") wiping them all out, though I get the sense you don't want to do that!!!
Good luck. Oh, you can always change over to using Excel for a rough database and writing those oh-so-wonderful PowerApps, paying their subscription pricing, overbuying licenses, and entering the future (the MS makes 10x the money for the same functionality future, that is). Watch Bill Gates (find it on YouTube) impress us about how benevolent monopolies are and how good they are for the advancement of mankind. You won't mind paying $300 a month for a few people to have the same abilities $50 a month used to buy... and you'll understand MS is just leading us into the future! It's the future and it's oh-so-bright!
For the monopolies anyway.
4 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
17 votes3 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
3 votes0 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →
3 votes1 comment · Excel for Windows (Desktop Application) » Printing · Flag idea as inappropriate… · Admin →
1 voteRoy shared this idea ·
44 votes3 comments · Excel for Windows (Desktop Application) » Performance · Flag idea as inappropriate… · Admin →
2 votes0 comments · Excel for Windows (Desktop Application) » Other · Flag idea as inappropriate… · Admin →
2 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →