536 votes41 comments · Excel for Windows (Desktop Application) » Viewing / Navigating Workbooks · Flag idea as inappropriate… · Admin →
Great suggestion E Bow. Thanks for taking the time to tell us about changes you’d like to see – we’re listening. We’ll take a look at this one. As always, folks should keep voting for the things they like the most – we’ll generally be prioritizing things according to the number of votes they get.
John [MS XL]
Along those lines Yaron, given the MS penchant for forcing "normal" windows on one, I guess to better push Windows vs. DOS. (Good Lord... but... yeah), one would think that anything that did NOT encourage people to use windows vs. full-screen program windows would be considered anathema at MS. Multi-monitor should just increase that penchant because now people could just really see that ol' windowing technology shine with 30-40 programs running at once! Yay! People would dump DOS like it was a lead brick!
So one could wonder how this got past that "filter" and came to be left to lie for so long.
Yay, four year old Admin comment, bet this one is at the top of their list, no matter how many votes it gets, absolute top of their list, yes sir.
When the age of the Admin comment (assuming there even is one) gets near to being written in digits, not text, in proper English, you know they care very much.
1 vote1 comment · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →
I've looked through the spreadsheet you give and cannot find any "N/A" errors. Or even text. Every VLOOKUP() formula I found works exactly as one would expect it to as written. No errors,
In fact, the place the spreadsheet opens has VLOOKUP() functions in use and the logic of them seems to be meant more for verifying the lookup data DOES exist in the table. Rather than the usual idea of finding something other than the lookup data itself: If the data item is present in the table, the formula returns the data item which it coulddd do by simply referencing the lookup cell (like "=A33"), no need for the VLOOKUP(). Since it DOES do the lookup, it sort of looks like the logic is to see if it exists in the table or does not exist in the table. So it can be added if it doesn't, or be corrected if mis-typed. I guess.
That implies the logic of the writer was that you'd see an error and you would know you had to go right then to add the information line in the lookup table. In other words, as if it were meant to prompt and remind you to do the housekeeping. Once done, the error would go away. Presumably OTHER information in the line of that table would be used elsewhere so it is probably important to make sure you do the work.
That further implies that the errors are a GOOD THING as they let you know to correct the base entry if mis-typed, or to add the appropriate information into the underlying data table so other places that depend upon it (and almost certainly are not visible from this place) do not fail with their own errors which you might never notice.
If so, the errors are a good thing, and will go away naturally once you correct their input datum entry or add the line of data to underlying table.
In any case, can't help otherwise as I cannot find an error much less the ones you refer to.
2 votes0 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →
1 vote2 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
2 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
2 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
7 votes3 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
Interesting idea – thanks for the suggestion Tomiam8. We’ll consider this and prioritize by vote, so please keep voting if you want to see the priority raised!
John [MS XL]
Good thinking, everyone. Especially MODE() itself, Mr. Barber's right, they'll never really consider updating something they've said has been deprecated.
But using the non-deprecated functions with text, that'd be nice... Sounds awfully good for RANK() too. Wonder which others the thought could be applied to.
1,188 votes912 comments · Excel for Windows (Desktop Application) » Viewing / Navigating Workbooks · Flag idea as inappropriate… · Admin →
Thanks for all of the votes – the team has definitely taken notice of the activity around this issue. We moved to SDI as a result of customer requests, but it looks like we’ve got work to do to really nail the use cases people care about. From a read over the comments, I see a number of cases that we will want to investigate further as we think through MDI vs SDI. We’ll get someone from the team to take a deeper look, and we may reach out to some of you for more clarification as we go. Thanks again for all the voting and passion here!
Actually you can drag an Explorer icon onto the maximized window for Excel, wait a moment for it to stop saying no and indicate you can drop it and it will open nicely in the same instance. It is still not a/n MDI opening, they have their own ribbons and so on, but it is the same instance and that was with the window maximized. I do this all day long, drag and drop the icon onto the open Excel instance and it is always maximized. Always. I do NOT do teensy little windows all over the screens like MS seems it would dearly love me to.
The hassle here is I used to be able to just click to open the Explorer icon and the above happened. Did not have to jump through so many hoops.
Are you perhaps saying you get a/n MDI opening if you do explicitly what you say below? That would be interesting.
What you can't do is drag a file from some programs, like Outlook, drop it the same way, and get the same instance. Of course, if you open a file from Outlook first off, before any other Excel work, then keep that instance open while opening and closing files, all the others (from inside Excel or via Explorer, even others from Outlook) will be happy to open in that one instance.
But basically, it's broken still, just less inconvenient, because it always will start a new instance if you are not careful every single time, and because you never have MDI.
All they had to do, if they were REALLY doing what they say they were doing, was to make the Shift-Click on the icon technique of forcing a new instance into a command line switch (like the "/e" we use to keep a useless new file from opening when Excel opens). Then those who "needed" SDI could have had seamless, effortless opening as they wished while the rest of us (the other 99%) continued on.
Maybe that one change was why we get so poor response here and in other Suggestions. Not just the turds never replying, never updating, never saying anything that isn't mendacious, but such poor response concerning DOING the Suggestions. Maybe they did that one lickety-split, and it backfired so horribly with their reason for doing it looking so utterly stupid as to seem a flat lie, and it's "burnt their fingers" so now they hem and haw forever before making the least change.
Because that one was so incredibly, pathetically stupid if really done for the reason they've stated in the past. And don't even bother with anymore. So freaking stupid... or a lie from the beginning... not sure which they'd prefer we think. (Partly because I don't think they care what we think.)
It used to be fun to poke fun at the advertising about "use Office whose programs all are built to work seamlessly with each other" failures. They never did more than Windows allowed anyway and they never will. But that's not even bruted about anymore so...
It's still ironic though, that the lack of working together is so deeply rooted that separate instances of the same Office program can't even coordinate better than the chosen program and Jim-Bob's shoe shine app do. Usually, "ironic" usually includes "sad, so freaking sad..." and this is no exception.
16 months... "You wait 16 months and what do you get? Another day older and... "
Wikipedia says (and it fits what I've read over the years):
"... implementation of Microsoft's event-driven programming language Visual Basic 6, which was discontinued in 2008 ..."
(VB6 being the thing discontinued, not VBA)
"... the VBA programming language was upgraded in 2010 with the introduction of Visual Basic for Applications 7 ..."
(apparently just added 64 bit support, though not complete support)
"Office 2013 and Office 2016 include VBA 7.1"
(nothing specified; just housekeeping for the new versions?)
"... Microsoft intended to add .NET-based languages to the current version of VBA ever since the release of the .NET Framework ..."
"... allowing even .NET-unaware applications to be scripted via .NET languages. "
(always wondered why not when they ended VB)
(oh, that would have been nice...)
"However, VSA was deprecated in version 2.0 of the .NET Framework, leaving no clear upgrade path ..."
(oh... I see... )
So, for now, I'd say our best hopes go with MS leaving the path open to other languages to write versions that, like Python to whatever extent, can replace, or work with/within VBA. That way, the onus is on others and their own development paths are not cut nastily short by decisions in other departments that they cannot really influence.
As for this applying to our issue though, I'm thinking a laundry list of deficiencies will get made up over time since MS is NOT going to address them. With luck, they will become a list of reasons to use one language or another that might step into the void rather than staying an ever increasing list of reasons to revile MS and Excel.
In particular, VBA was clearly never really pictured outside an MDI environment so there are definitely issues one might want to take note of in some of this Suggestion's comments. Flickering seems a small point (I know, important to the experience and some people's products) when compared to coordination issues that have been mentioned, for example.
The bottom line is SDI creates what are in essence entirely different programs running in Windows and even though they are fellow Office programs (how much more "fellow" could they be?), it just isn't seamless and never will be, or would have been. Excel had a fantasy if they thought it could be. Drinking the "Office works seamlessly with itself" Kool-Aid does NOT make the advertising a reality.
Evidence of that? Well, 30 years of living the dream... Of ALL the complaints in ALL the Suggestions you've all read, the sometimes complicated set-ups and uses of Excel, the writing of workflows maybe more complicated than many standalone software programs... of ALL those, when have you even one time read someone mentioning, even in passing, writing VBA code in Excel that reached out into other running Office programs to accomplish any task? Ever? On some other website? Some guru website telling you that your goal is best accompished by workign the material in Excel, perhaps, firing up Word to present it, and firing up Outlook to mail if off to 12,000 addresses. And using VBA from Excel, since it is the first program in the flow, to control it all, not just the Excel portion?
No one even tries to suggest doing something like that. Not even MS. So just how well can we ever expect VBA to coordinate, safely and accurately, two or twelve instances of Excel, even if it is "just" instances of Excel, in order to work with all the files required by the work flow?
Bring back MDI, allow one to set a choice, especially one available per spreadsheet rather than application-wide, and life is good for everyone.
People who want separate instances could even have a "/switch" in the program shortcut (like the "/e" switch I just love). The complaint about moving spreadsheets between monitors was always spurious, though it is often said it was either THE reason for this, or at least the straw that broke the camel's back. Anyone desiring that could have always opened separate instances, or they could have normal-windowed Excel, drug its borders to cover the screen, then drug the appropriate one over onto the other monitor/s and moved MDI windows in the huge workspace. So even the rumored thing never needed a nuclear fix.
It's all just sad and wretched. I'll eat a five pound pot roast with all the fixin's if it ever gets satisfactorily addressed. (I mean, I might do that anyway, but it's the thought that counts, right?)
Sorry, I wrote that badly:
Physically set the monitors only once when setting up the computer. NOT every, every day! Just the shortcut keys once a day when logging on to match what Windows presents to the screwy physicality. Lordy, not moving the monitors themselves each day!
Too bad we can't rotate monitors 90°. Windows can be easily rotated too, in 90° increments, so one could just do that each day. Ctrl-Alt-(arrow of choice) and your computer matches your monitors in their "goofy" orientation. If I have to logon with a password to MY computer, I can do this without feeling put upon! (Well, one has to go to the Action Center once after buying the computer to turn on the use of those shortcut keys. Oh my, that does it!)
When you'd need width too, you could spill the windows you tiled off the edge of whichever monitor so they cover both (the once a year I do this, I get columns A through AU). LOTS of columns too then.
So many of the difficulties we are listing in here could actually be addressed individually by Excel, in ways that do not depend on MDI/SDI. Even the more involved thoughts, like if you open a file from INSIDE Excel, use Excel to do it (File|Open), not Windows (by Exploring to it and clicking, say), Excel could almost certainly easily keep track of those (it does now, build 1904, in that only one instance shows in Task Manager while using Windows to open the 2nd file shows two in TM) and if you tell it to, allow presentation of the files like it did for MDI, even though they are truly SDI, and provide a single ribbon at the top. So whatever window, maximized or normal, that it starts with, ribbon at the top that keeps track of which file you are in at the moment and acts upon it, like tiled windows and MDI did.
Literally, how hard would that be? Probably? Not very since they can keep track of the files a single instance opened, and all the pieces are simply windows anyway so they can just work with the pieces differently, and use both those things to present it AS IF it were MDI. Who cares? Real estate-wise.
Solve half a dozen of the little things without the major switchback to real MDI, like they did with the copy/paste issue the switch created but is now solved, and I bet ¾ of us could make nice-nice with it all.
That could be seen as "divide and conquer" but it would be "responsive to customer needs" in my book.
Remember all those years ago when MS and everyone else fell all over themselves proudly declaring they now had MDI enabled applications? Such a bright, new future we all were facing...
It's like "1984"... OR IS IT?
(Yes, it is.)
Bummer is, the part I wanted worked for one (1) day after I "woke up to" the 1903 Build. Then back to business as usual.
"Woke up to" Build 1904 today, no joy.
Looks like 1903 is pretty up-to-date. I found an Office Insider person saying he has 1904.
Interesting in its own way... Given your 1902 and my 1903, that may mean that 1904 will be rolled out next and contain the Spill functions, rather than it being more months.
Maybe. But an interesting thought.
I saw the same, so "real estate" issues and the all open spreadsheets in the work area still seem to not be restored. One could probably use "Normal" windows now, for something like that, though on the Windows canvas, not in the old "workspace" below the menu, etc.
If one uses VBA much, there are macros (fairly) readily available for positioning file windows exactly, and repeatably, so one would not have to do it by hand every single time.
Of course, that probably could be done with multiple instances as well, maybe, but maybe not as a single macro but rather a set of one each per spreadsheet, but those instances could not have shared data like in the "old days" and almost certainly could not share macro services very well.
Now though (see how has moved from "hope it's permanent" to "it's permanent" for me?), since they are all in the same instance, it might be a workable thing to set up.
I seem to have: "Version 1903 (Build 11425.20228 Click-to-Run)"
In human terms, it is Excel 2019 (I believe) installed from the Office 365 subscription the computer was set up with. The initial installation would have to have been Excel 2016, considering, and then updated over time. Other places here and elsewhere make it seem pretty solid that the version number is "19" and "03" as in 19 = 2019 and 03 might mean third update release, or March (third month) update release, or something of the kind.
I cannot say concerning Access. My usage goes the other way, massaging data in Excel, then taking it into Access.
Reporting a change in my copy of Excel:
Today I found, quite by surprise that a large portion of what I want concerning the MDI to SDI change has suddenly appeared.
I had been unable to open multiple files in a single instance of Excel using all the normal methods (File|Open, click on a file in Explorer or on the desktop, drag a file onto the program window, open directly from Outlook). I could work around the Explorer issue by dragging a file onto the program window, but nothing would keep a file opened from Outlook from starting a new instance.
But now ALL normal "person" opening methods open to the single instance. (Have not tested VBA or opening from other programs.) Task Manager reports only the single instance, I can close all of them by clicking the Taskbar icon and choosing close all, and I can copy and paste between spreadsheets just like between pages (formulas paste, not results, and so on).
Since I have no indication as to WHY, I am planning on enhjoying the thought that it was some upgrade last night and is permanent. Honestly, I don't know that to be the case, but always have hope.
If anyone is now seeing the same thing, perhaps some of the related difficulties people face over the change are also fixed, or at least accomodated if "fixed" isn't the appropriate word.
On the other side of my "Hope balance"... I would expect an updated comment from the Admin for the Suggestion if it is a permanent change rather than a transitory one. I can picture it being an experiment rather than a final idea. That could lead either way: many of us like it and find our biggest problems taken care of so it ends up being permanent, possibly with further adjustments, OR, well, taken away. I will say that there is a noticeable lag in a file opening when clicked on in Outlook or Explorer, say 3-5 seconds, rather than pretty much immediately, which IS suggestive of a work in progress. (Note to Excel: if that can't be improved, I promise I CAN live with it for the functionality! Don't throw the progress out for that reason!)
Bright, shiny day!
Ooh... I'm going to try entering a number with leading 0's next, see if they remain! Either way though, BRIGHT, SHINY DAY!
Judging by some other Suggestions' comments from MS, I think Mr. Portwood's use of the word
"last" is probably pretty spot on, though in a way we won't any of us find palatable.
(Sigh...) Though I did see one for a different Suggestion that is less than a month old at the moment. So someone still looks at things here.
The new interface is like some 5yo's refrigerator drawing. "Crude" begins to describe it, but antonyms to "graceful" like "graceless" (not to mention "hideous") well describe the aesthetic.
I guess they're saying today's new users, and the more recent ones like 00's Yuppies, I mean Hiptsers, have no sense of form and function that does not include the words "childish" and "garish"... so complimentary MS.
I get that it was not written for me, that I share it with my closest 800,000,000 million friends. So the part that bugs me most is the losses of funcitonality. For instance, did a loss of beauty and grace really HAVE to include a loss of ability to work with sheets by navigating them? It would seem like they could have just made the tabs bigger, crayoned in them, and left the functional programming the same. But no.
So the appearance, and its actually required (only given that you're shifting from fine point gel pens to Crayola's box of eight starter crayons with six of them removed) real estate losses (all the framework components can't get bigger without the real estate disappearing) are just sad par-for-the-course victims of marketing and "our product's too boring since it hasn't had a facelift in years" thinking.
But what about that required going under the hood and ripping out functionality? ADD TO functionality? That's rather understandable. Rip it out? Not understandable.
Here's a thought: How about the build the framework as its own unit, so to speak, with choices available for sizing it. So my vision is weaker nowadays? I set the framework to be at 120% the shipping version "normal" and so buttons are bigger, the ribbon is bigger, tabs are bigger, and so on. Jim would prefer 80%? Great. All that is smaller for Jim. And all the rest is real estate.
What's the difference? NOW you can magnify or reduce the size of what is being termed "real estate": your actual worksheet space. The framwork stays how it came. So instead of reducing the infrastructure to increase the working space, all I can do is affect the sizing in the workspace.
It could even be written to set the framework as desired, above, and when you activate a portion of it, say the ribbon, it would blow up from (or reduce to) your standard framework sizing to full installed shipping sizing.
Lordy though, this current aesthetic is like that drawing the angry little daughter made of the family and Daddy says "Sweety, where's good old Daddy?" and she yanks it away and comes back shortly and points... we're just saying "Hey, MS sweety, where's good old User?" and they are angrily handing it back, pointing to the tombstone in the corner...
Enough digressing though.
Yes, hence part of the desire to break the spreadsheet into parts. You are going to be one of the data sources? Then yes, you can try to mangle my work (I, um, mean the company's work) to ease your pain but you only have access to one little corner and nothing else. So even if (when) you find a way to break things, it is easy to overcome.
In that approach, the individual users have no real choice except to request changes and live with whether and how they get them. Naturally, politics often trumps good thinking, but at least they usually have to be done by the maintainer of the spreadsheet.
Life is definitely complicated by everyday events and workloads though, and a busy creator sometimes has to leave modifications to someone else. These ARE company owned, not creator owned...
For less important spreadsheets, life is more complex. Not being critical, they are less cared for... chewed upon.
But approach them as if one is (slowly) normalizing an unruly database and life with them becomes easier over time. Work smarter, not harder, and one's productivity rises.
Yep, I remember IRC. Had a six digit ICQ ID. I do not reach back to bulletin boards, never having liked them, but started out in '79 with a TRS-80 and have used phone receiver modems. Jumping Jack Flash seemed oddly primitive until I realized "banks..." And I remember all the little Hitlers pretending to love the freedom of the internet. Until we breed different people, that's not going away, whatever a thing is called. John Calvin is around every corner it seems. Even Stack Exchanges are going that way. (Sigh...)
Neither here nor there to this I suppose. The shame of it is that something harder to manage and maintain is be forced upon us solely for the money. BG is in rapture telling us monopolies DON'T do that, but it is not only what they are doing here, it is what they do, period. Because we aren't, apparently, succeeding at breeding vile selfishness ("Mine! Mine! It is ALL mine!") out of humanity while preserving mild selfishness ("If I don't look out for myself, who will?" "You have a point there Bob, you do. And more to the point Bob, who should if not you?").
What I'm saying is this is the future: there will be no fixing things, 1,000 votes or 20,000 votes, just a slow deprecation of Excel to something that can only handle an inventory list at home for insurance concerns. Figuratively. We shall overcome, like we have through the decades, but the pool we overcome is shrinking down, so to speak, to a kiddee pool. The kind adults look funny in, their b*tts wet, but arms and feet dangling outside. Ahh... metaphors... There will be no fixing, no returning to old functionality. Even freshly selling Excel 2010, say, is not in the cards because these people really believe every human would have to buy 10 copies of the most recent version if there is no other choice. Indians with annual incomves of $300 would pay $500 for Office if those pesky pirates would just go away (to the h*llhole prisons they of course belong in... right?). They wouldn't eat, as whole families, for two years just to do it, gosh, if it weren't for that. That kind of "thinking" just does not lead to good news for this kind of problem.
Absolutely. All sorts of flaws that have minor and sometimes major impact, but normalizing is an exceedingly useful concept, those DB people are on to something there...
And it applies, or did, in the Excel world. For reference data, loading live data from various sources and types of sources, separating code into portions (or altogether), separating what is done with data (proprietary things especially) from sourcing the data, the list goes on.
It all relies though, on the spreadsheets being available to each other and to VBA. Which is spotty at best in SDI.
Of course, in a perfect world, one just puts a full spreadsheet together, makes it tick, works out the kinks and unexpected difficulties in the data feed and how people use it, and then management angels step in an hand over the cash to have programmers write an applciation that uses the essential approach, sources, people, and lessons of the spreadsheet. No boss calling you in one day and asking cold what specs such an app would need and your off-the-cuff reponses turning out to be the contracted for application. Rather a fully successful working better-than-prototype spreadsheet solution to inform the programmers every step of the way.
Or the bosses ante up for an existing software that does the trick, after seeing how nice life is with a steady, accurate, safe, and private data flow is created and feeds your (proprietary) uses.
So I am hardly opposed to seeing a treasured spreadsheet die by being replaced with an application. It's often the goal, really. But FORCED upon me? No, not so much... Especially when all that's really cared about is the new cash.
And there are a million things we do that are "involved" but never rise to the "now let us write a program" level, if only because they are temporary needs (even, you know, "permanently temporary"), or fast changing, or just gradually become "involved" as one user or boss after another asks for this one little change. And "involved" means different things to different organizations depending on talents available at any given time. None of these situations cry out for writing a formal program, and the simple-minded things that are apparently PowerApps' focus are not necessarily on point.
Additionally, managing a "normalized" spreadsheet is tough enough as the environment becomes complicated. Can you picture the management nightmare of, say, eight PowerApps apps that achieve the same-ish normalization? Managing them for results would be hard enough, but now picture managing them for not paying unneeded licensing fees.
As to folks in forums... well... they might as well be religions, each one. "Choirs" at the very least, as in they are all "speaking to the choir." Each seems to have its set of absolute interests which must be nodded to, explicitly, and never, ever challenged. You might get lucky and post when a schism is imminent, but... those are not often. The self-validation they urgently need feels pathetic, but is in truth just sad. I avoid them mostly, have for a couple decades. Give a try when I'm optomisitc, but... it's also disgusting and I'm replacing lightbulbs with LED's that I will never have to replace again, so I don't have time to be interested in much of their cant. Good luck trying to swing any of them to a new view. It's like science: the people believing in (I, um, mean "who can prove continuous creation") continuous creation and who see the "big bang" theory (they even named it) had to die out before the big bang theory took the solid grip it has today. Overgrip actually, as there are actually some interesting explanations for some things if continuous creation is happening as well. That's physics. Imagine how that plays out in all other arenas in which opinion can have a role. Forums are almost nothing but opinion, so...
Though if I need to know something, I'll suck it up and try again.
Let me get in my daily thank you to MS for the wonderful world they just let us pay to live in.
And looke d at more starkly in my example, if you figure Excel was worth $5 out of your $12.50 (Outlook IS pretty important), MS would now have $285 a month, vs. the $5 a month, from the things you used to do with Excel alone.
5,700% increase in revenue.
People get bonuses for stuff like that. Not promotions, 'cause they can NEVER repeat that success, but bonuses, sure.
Here's the simple honest root of it all,no ifs, ands, or buts:
MS wants to shift Excel away from being able to do anything much complicated or needing any real security or programming so that we all shift to PowerApps.
Excel does a million things for a nice subscription price of $12.50 a month for a good version of Office. Well, a portion of that since you get all of Office and certainly use Outlook a lot and Word to some degree.
PowerApps... that gets a nice subscription of $8 or so per month per application per user (including per designer).
So you have 10 employees including yourself, all using Excel from an Office 365 subscription? That's $125 per month for "all you can use" (assuming they weren't silo-ing us into these newer, high cost applications and so made Excel useful and safe again).
If you have five things you do, with an average of 7 users of the 10 using each one every month, they will now get $8 per month times 7 users each times 5 things = $280 per month for the five big things you used to do with Excel... PLUS the same $125 per month they STILL get because you still have to have the Office 365 subscription for not-the=five-big-things Excel uses, Outlook, and Word.
So... by hammering us down to SDI so we cannot normalize our spreadsheets into a set for a task allowing better sfaety security as well as knowledge security, ease of use due to minimizing conflicts, reduced training by limiting a worker's interaction to what actually applies to him, and, by making each spreadsheet actually used smaller, having less load on the computers opening up he world of devices that can use the spreadsheets, MS is intending to force us into PowerAps, amongst others, raising the farming revenue they make, for no real work, by that example of $280 a months, tripling the example office's payout each month.
Add in the required subscriptions to be able to share the app about ("publish" it) and access for maintenance of it, and you have raised the farming revenue even more.
THAT AND NOTHING ELSE IN GOD'S WORLD is behind this SDI kick. Why do they not respond? They want such a future well-entrenched before we revolt.
How many of you have been forced to consider the conveniently now available, oh-so-wonderful PowerApps subscription program as one of the alternatives we talk about below? How many of you are being told by bosses, who seem to be getting addressed on the subject outside of inside channels, "Look into PowerApps, it looks like something we need..."? I think Google, but worry, for good reason, they'll just drop their spreadsheet one day. They do that A LOT. No obvious reasons, just a thing is gone. But it isn't Google MS is angling at, it is PowerApps.
PowerApps is meant to do every last little thing we keep complaining ("yapping" in MS-speech) has been lost and there is simply no coincidence to that. You sell the new bananas but people are reluctant to change? Buy some of the plantations and burn the crops. Pay other farmers to drop the crop for no rhyme or reason (Google) (yeah, that's anti-trust law there). Then begin sewing people's fingers together so they can't peel the old bananas and point out how the new bananas pop open with a mitten-paw squeeze and are oh-so-yummy. This is what monoploies do. Then they imitate Trump and simply tell some other story that also has no basis in reality and that's the life of the peons like you and me.
Bitter? Cynical? F*ck yes it is. But it is also the absolute truth here.
No nudge from me or from others has even bumped a comment out of MS. They're just happy "We the Sheeple" are busy forming a not in the least perfect Union. While they are opening new bank accounts as the old ones reach the physical limits of current bank software for recording balances. Sorry Ms. Gates sir, the account balance field is just a double integer field so you need several new accounts to hold the new money...
Happy Days was a show created from a skit on Love American Style and Fonzi spent a year wearing a blue windbreaker. The story they all telll is utterly different. This is like 1984, things you did yesterday are now recorded as different than your memory.
What we need to do is find someone who offers something like PowerApps, but works, not like the usual cr*ppy MS rival, and all go with that, smoothly leaving MS at the altar. Or talk to lawmakers. Every generation or so, that works a little.
And God forbid you split a task into multiple files so each person can do his part without affecting others and without everyone handling the real collection of information and program logic.
Would MS prefer to give us real security (something the word "granular" could apply to)? Would they prefer to also give us the ability to segment a single file so each segment could limited to particular people and also give us the ability for true communal work (i.e.: real sharing/multiple workers using the file without miserable limitations?)?
So no? Then let us open multiple files in the same instance without having to go to trouble about it and without all the attendant issues arising when that does not work out.
I might mention there are a number of functions that will not reach into a closed file to extract data. They regard a file open in another instance as being closed. So they have to be opened in the same instance, utterly reliably, not in a sketchy (and useless) "sometimes people don't get a tricky thing like file opening right and it all goes to H*ll" way.
Users DON'T get everything right, and the further up the food chain, the more effort one has to apply about that (I have weight issues so it's no surprise my metaphors involve the word "food"...). We all know that: bosses boss and they regard you as making excuses if they ***** up. File opening shouldn't be tricky anyway... FILE OPENING! How should the word "tricky" EVER apply to that? But it does now. Yes, I can make the files open with VBA which WILL put them in the same instance, but part of the reason for spreadsheets at all is to not have to program as much. And I have not tested how well VBA does without the Registry fix mentioned in the comments, and if it is required for VBA's opening of files to land them in the same instance, well...
I must say though, I don't understand the real estate point mentioned:
1) Surely you understand you can reduce the magnification to, say, 20% or 30% and all the real estate problems just go away!
2) Surely you understand you can just upgrade everyone in the company to nice 50" or 60" monitors, like they have, probably, in every cubicle at MS, and the real estate problems just go away!
Sheesh... must MS bear responsibility for everything??? Use the 30% magnification for those older folks in the office that would otherwise need reading glasses. They should be retired and enjoying the golden beaches of Tahiti anyway, no company should keep them as wage slaves after 35 or so anyway! Do them a bigger favor and fire them all. So really, that 20% level should be awesome, right?
The bit at the end about the Registry entry is not correct. Many things in it are, of course, no argument. But the apparent purpose of it was to force Excel to ALWAYS operate that way, not only when the user did a special thing and by his distinctive action forced a new instance to open.
There could easily be many reasons one might wish to do so. I myself would now and then to isolate spreadsheets from each other and sometiems in order to have more than one spreadsheet with the same name open at once. Perhaps someone would to have separate Undo stacks. Maybe they had formulas in something they were working on that would update from some second spreadsheet they had to open upon its opening, but did not wish that to happen yet had to open it anyway.
Whatever though, it doesn't why they might've, they apparently needed it and asked about it and Excel's answer was, yes you can do that if you like, just make this Registry entry.
Everyone else can keep on keepin' on, and you'll be happy too.
HOW it applies to us today though is different as we represent a different part of the user base, one that wants the opposite and can't have it.
What little it does for us is that it gives us back a few ways to get files to open in the single instance of Excel. So I can drag a file off my MRU list in Explorer and drop it on the instance of Excel I want it to open in. I could NOT do that before the fix. ONLY if I used the instance's File|Open function could I do so.
So yes, it actually does bear on things here, and while it is no kind of argument for or against the Suggestion, traditionally folks in the Voice forum do not limit themselves to only such things, but also allow for helpful thoughts for difficulties expressed, even though that is not, of course, the actual purpose of this place. (If you like that kind of **** behavior, go to StackExchange instead...)
I whole-heartedly agree though that there are contradictory desires expressed in here. My own comments decry SDI and cry the wonders of MDI. But as you can see, I agree with you that we should forget arguing about what's under the hood and express the functionality we need and a lack of appreciation for the functionality destroyed. We want a result and probably should evolve to discussing the results we desire without directly mentioning SDI or MDI. This would be natural since I think many of us are realizing some of what we want actually might be a bit SDI, not MDI.
My understanding of human nature and my experience of general human intelligence says to me that there is no actual confusion about all this on Excel's part. Their people are surely fully aware of the things we want by now and capable of ignoring "yay SDI" and "boo SDI" commentary because they can realize we are perhaps thinking seemingly related changes came with that change and seem related and so mention them, but they aren't fully on point. They are curely able to catch on to that. But... counterpoint... you did not, so maybe they never, ever have in the years of reading these comments and talking to people and so on. I don't think so, no. But maybe.
So I accuse them of "mendacity" and "neglect." I believe many others would harshen those terms and add "contempt" to the list. And it's well-deserved.
But incredibly clear that MS misunderstood the requests? NO. Maybe for a week or two at first, but NO. They may still react ("trigger" in the modern parlance?) when anything SDI vs. NDI arises, but that's emotional, not intellectual. They're smart people and certainly as socially aware as myself, or a great deal more, and so much more than likely DO pretty much understand our points even if they trigger when seeing SDI/MDI popping up. There is 100% certainty they DO NOT 'clearly MISUNDERSTAND.' Bad feelings? Not care? Figure that we would think them meally-mouthed to explain they are bringing this or that back, but it's not really an SDI/MDI issue so...? Oh yeah, lots of that and more variants. But they simply do not "clearly" misunderstand. Maybe "somewhat" or "you folks are kind of confusing in small ways now and then" but not "clearly."
I do think it's a good idea for us all to shift to saying the results we need, and/or the features we desire, since we may not have a full understanding of everything in the SDI/MDI thing. Just use MDI as a reference when possible.
Lol, they took " s..e..x..y.. buzzwords " and made it " **** buzzwords "
But that loser who tells them to blah-blah-blah and lards it up with the "F word" sees most of the "F bombs" post. And then reposts them 40 times. Some programmers with heads up their backsides!
Two posts to get it all in since it seems there is a character limit (no, it doesn’t occur to me that maybe that means I should cut things…) Gonna post “halves” in reverse order so that it will hopefully read in the right order when seen together, though anyone following the thread will get email in which they come the wrong way. Sigh… yeah, I know trying to address two concerns at once is exactly where MS Excel’s programmers find themselves so there would seem to be some irony here…
I should perhaps mention I hate the ribbon interface:
1) I understand some people think "menu" is a four-letter word, in the worst sense, but the old menus worked like a charm. One thing they could have used would have been a right-click option of "Place on a quick access bar (single "line" button bar above or below the menu bar, say, with this single click of my mouse instead of the hassle I have to go through to achieve this now" so one could easily (literally, not in marketing speech-land imagination) have a bar of often needed commands. But whatever. This anti-menu thing was a religious thing, once upon a time, if you recall, with people hating to clock a menu choice then a sub-menu choice, then something else and a dialogue box opened with the real interaction... So now we have either a huge ribbon taking up real estate where one clicks a thing, not a menu, which opens more choices, and one clicks a thing, that then opens a dialogue box... but the priest says it's not what everyone hated about menus so it isn't, um, I guess...
And now once has a hard time even learning Alt-shortcuts, when they even exist anymore. Yay religious fervor.
2) One CAN reduce it to a menu looking thing, which I can't live without since I hate even seeing it at all nowadays (we evolve), and at least the loss of real estate is no more significant than with the menu bar it then looks like. But I'd prefer to get back the old menu interface, updated for new/newer commands and features, like Tables. With Alt-shortcuts always available and shown.
3) I always hated the single workspace. I want the same functionality, but not that workspace bit. Talk about limiting real estate!!! Geez people:
a) A single workspace with ALL your files sharing it? So you have four files open and somehow your real estate is greater than whole windows for files?
b) You hate switching between windows? But switching between files that completely filled the workspace so you didn't lose real estate is different? (In concept... I know it's slightly different in some of the things that can interject themselves.)
So whatever it takes to let me have separate windows rather than the single workspace is fine until it starts hammering and destroying other essential functionality.
4) Just flat-out, the ribbon sucks. I hate it. Especially the huge space it takes and the very odd, in many cases, arrangement of features in it.
I just want real, separate windows for the fact they give me a full window to work with, both sizing-wise and moving around-wise. One that interacts with the Windows Taskbar (which is the last time anyone will likely see me call it that instead of the incorrect "Start Bar" or variations on that). If SDI is required for that, then I applaud SDI.
HOWEVER... since SDI apparently forces an incredible baggage train onto the program and utterly destroys big chunks of essential functionality, I don't want "it." Or at least, I don't want the ham-fisted, cut-the-baby-in-half approach taken to implement the single aspect of gain I see it having. I'd rather go back to full one workspace Excel than have what we have now.
As to that Registry fix, it was apparently (based on the language at the Support site's way of putting things completely implying you wanted these things but currently did not have them, that it was written as if to an audience in that exact situation: certainly NOT the audience I am part of) meant to allow you to FORCE SDI upon your installation. So it was apparently written to ham-fistedly overcome what the audience did not like and creating the key with a value of 0, actively NOT seeking to do so could only keep the particular behaviors available from implementing the key from happening because of the key (with a value of 1).
It, of course, was apparently not written to completely prevent those things, just not implement them because of the key... so if they were implemented in other manners, the key cannot help us. And if other programs (Outlook for instance) were updated assuming the full SDI concept in Excel, then abilities might not be available in the interaction (like how opening an Outlook attachment still opens a new instance, even with the fix).
So... not a panacea. But better. A bit.
Seocnd half of the post:
Bad sides of SDI that we are stuck with and need to be done with include everything arising from the multiple instances thing. Almost everything. So no, I DON'T want SDI, I want the functionality of separate windows. Everything else that's been mentioned, I want the abilities offered by MDI... but NOT in the world of the LITERAL single workspace on a single screen with thing tiled or lapped over each other or maximized within the workspace so one tabs between them, etc. I want the stupid things free, physically, of that.
But why can't Excel do that? Offer the separate windows one can arrange over twelve monitors however one likes, each with some basic command bar (my precious menu bar of the past, or the hideous ribbon interface of today, with the minimization, only covering half the screen when you are actively using it, ability) so it has full usability, BUT with EXCEL still seeing them in a single workspace, just not requiring that they be displayed to YOU that way. A program doesn't have to see a thing the way it presents it to you any more than you have to make the screen jump around following a macro's selection of cells as it works on them. In fact, that's usually not the best thing, eh?
But the files HAVE to be utterly and fully integratable. In the sense that MDI allowed. Not just (though it's an incredibly annoying loss!) allowing something as simple as full cut and paste but macro integration and formulas reaching into other files and so on. (I have a Suggestion out there, with no interest or support for it, sigh, concerning that whole silo of issues. Why should a function that can reach into another file for data require that file to be open when other functions doing the same thing can use closed files? Anyway, in separate instances, the file does not look open to those functions so... SDI strikes again.)
To use an analogy, I don't care about the engine, I just want the CAR. And when I turn on the radio, I want it to work IN the car I'm in, not launch a new car in which it is working fine, but that has no air conditioning... that's running in a third car it launched... so I get the worst of all worlds.
I just want to farm my fields and plant at the right time. I don't want to have to worship a pantheon of gods just to know what time is the right time for planting each year. I know many prefer that alternative, but I just want the functionality and don't care about the trappings. Office's original marketing point (not just theirs: even the horribly limited spreadsheet in Open Office's Harmony program from 37 years ago did the same marketing, before Office existed) was that you could more easily share between its programs. They did whatever they did, but YOU lived in the world of it all can flow between as needed without worrying about how they did whatever they did. I want THAT. I don't care what the techniques are the programs use, or the marketing, or the **** buzzwords: I just want the functionality. ALL of the functionality. No more ham-fisted muck-ups.
I'm looking for the following things:
1) EVERY spreadsheet opens in the SAME instance of Excel. NO MATTER how I open them, every last method of opening them works like this and it does not matter where I open them from (including from inside other programs, Outlook being an example). ONLY if I intentionally force an new instance does one ever happen.
2) EVERY one of them comes with a ribbon, etc., and looks and acts like it owns the world. (Better yet, bring back real menus and ditch the ribbon.) When they open, they open in their own windows which will be "normal" windows if I set it that way or maximized windows if I set it that way. There will be no tiling inside one workspace, or staggering, or whatever. They each get their own full window. If I wish to do side by side, I set that up.
3) I have full and absolute interactivity between them. Only if I intentionally open new instances do I lose that full interactivity. That includes full value copy and paste, amongst other things, and full access via formula to any and all open files.
4) I'd like a unique Undo stack for each but have lived 28 years without that so I can go a few days longer.
5) Like any window, if I move it to another monitor, well, it's just on the other monitor. Nothing fancy needed, just Windows letting me move an Excel window around. I can do this because each file simply has its own window, NOT because someone chose the nuclear war option of forcing new instances. But to be explicitly clear: no returning to primitive days where the instance had a framework and a workspace and all the spreadsheets lived in the workspace... and could never be lifted out of it as their own windows.
This is what I had using Excel 13 from an Office 365 purchase. IT couldn't make the 365 installation run on the new computer, so they dumbed it down to the Excel 13 version that could be installed instead. NOT 2010, 2013, no matter what people tell me.
(Supposedly, I could not have had what I describe above, but I did. All with the Excel 13 available instead of the 365 installation.)
For the time period I did try to use the suite's 365 version (it wasn't Excel that was unable to ever work safely with the computer), I experienced all the horrors I see today with Excel 16 (again from a failure of an Office 365 installation to work with a seriously plain vanilla Dell business computer). Then life returned to me. Now I'm stuck in this terrible place again.
So... basically, it DID all work nicely as described above in the Excel 13 world which I am assured was 100% SDI.
Which leads (finally) to the POINT:
I don't really care about MDI vs. SDI, per se. I simply want the above, numbered, things just like I had them before when supposedly I had SDI anyway. I apparently had an SDI program, but had all the nice things about MDI which I list above. I just want the usability. MS can leave it SDI if they give me the usability I list above, back.
I think most of us actually want precisely that and that some of the things said in order to attack and defend SDI are not really on point because apparently what is desired can be had in either situation. MS just has to return to accomodating MDI usability like they did in whatever version of Excel 13 they let one use if dumbing down from a 365 installation.
(Not only did it tell you it was Office 13, it had formulas introduced in 13. So... Possibly there were odd files and Registry entries that hung around from the 365 installation that made it a Frankenstein monster, but a friendly, happy one.)
As to SDI defenders, when I first came here, while using that version, I saw endless things here screaming to bring back SDI. So there is a big support for it. That's why, since it seems SDI is not, perhaps, the literal failure point here but rather removed functionality riding along with it, that there may a culture of ignoring us on the basis that we might be fervent, but don't know what we're talking about. (Thanks, MS, for giving us the benefit of believing we have the capacity to be ignorant, if that is so. Not every software provider believes that deeply in their users.) If they think that, then there's really nowhere for them to take this suggestion.
Frighteningly, the difference I see between 13 and 16 could mean they did not remove all the MDI-like functionality with 13, but as they put 16 together, they "cleaned up" lots. And that it could get worse and worse as further releases "clean up" even more.
All I care about is the usability I once had, and need, and apparently even when I had a full SDI version. Not about MDI vs. SDI, per se.
Some of the MDI side of things still exists but needs a Registry entry. After someone mentioned it, and I created it, I eventually had the ability to open multiple files in one instance if I used File|Open or dragged a file from Explorer ONTO the instance of Excel that I wanted it in. I can also achieve that opening a file with VBA.
However, most of MDI is swept away even still. For instance, find a file in Explorer and click on it? Opens a new instance. I can drag it onto the running instance of Excel, drop it, and it opens in it nicely, but click on it and it opens a new instance. And in no circumstance I've tried can I open an Outlook attachment by dragging and dropping (MDI effect): no matter what I've tried, they open a new instance of Excel. Maybe using a macro?
Anyway, the Registry edit is from a MS Support article and does give some MDI functionality back:
Value to give (DWORD) = 0
The Support article is:
(You may notice it was designed, apparently, for an MDI world in which one might want strict SDI, hence a positive value for the key forces SDI only. Hence the need for the value to be zero.)
Again, it doesn't bring my world back to being right, but it does restore some of the MDI world to me.
Folks aren't really aware of how change happens in many arenas... Science for example: the new ideas are never really accepted by most of the folks who preceded them. They have to work with them to keep their jobs, but like Einstein, NEVER accept their win and work as much as possible in their old paradigm. They move from the front line (teaching, working in labs) to administration and slowly to retirement. Then they die.
Like it or not, this is how Big Bang theories, etc., eventually are the only thing taught, or largely so, and called "mainstream."
LOTS of arenas, this is precisely how we (grudgingly) progress. Consider how it works when the results of experiments, and whatever stands in for experiments, are more a matter of opinion than clear and absolute (seeming) fact.
Point of mentioning that?
It's kind of looking like we will see one of two outcomes here:
1) With no promises or updates, one day a new version will come out that restores MDI, whether tubing S(ucky)DI, or giving us a direct option.
Yay! I wait the day!
2) MS just... never does do that... tosses an ADMIN comment to us every two-three years... (look at Pavlov's set of experiments: you'll be stunned at how long those dogs would salivate at the bells after the conditioning was set (or consider Cleveland Browns fans)) and just... NEVER does restore MDI.
Figuring, of course, that like so many arenas of human life and endeavor, if they just wait it out, what? 30-60 years?, that all of us who ever knew MDI could even exist will die and then there'll be no pushback on the subject at all.
Playing the "long game."
I know which one I'm coming to believe it is.
So... and I'm not there yet, but... alternatives. And as I've said before here, if I do significant work on that front, I will change and of course, never come back. I used three spreadsheets before switching to Excel and never returned to any of their predecessors after moving to each new one.
When someone gives you an absolute "Pick from these one choices" edict, picking choice three is ALWAYS the way to go. If it weren't for Google's perfectly understandable willingness to utterly drop a whole idea the moment they determine it might not have a future, I actually would have switched already. That risk though... it's significant. "Pick from these zero choices" is even worse than what MS is offering.
And finally (this comment, not forever, lol, seriously, right?) given how they are pushing the add-on programs like BI and the quick programming via Power Apps, I think they really want to break us from using Excel for anything other than quick and dirty databases with all the "fancier" work being done by one subscription app after another so they can add a pile of cash onto the initial purchase or basic subscription price. Honestly, breaking easy connection between sources and therefore the ability to separate a process into several spreadsheets so one interacts with only what his role needs to interact with, not fixing the Undo stack issue, ever, and giving VBA fits... at the very same time one is offering things like Power Apps and BI... it just smacks of a plan that is NOT NOT NOT going anywhere any of us want it to go.
Ever watch the You-Tube videos of Bill Gates explaining how monopolu power allows a wonderful, beneficial organization like MS to force development of the future along just the right path instead of those nasty fits and starts and dead ends the riff-raff come up with? Ah, those wonderful idealists at MS. And Apple. And Exxon. And so on... where would we be without them and that sweet, sweet use of monopoly power?
So... yeah... there's an old saying about sh*tt*ng in one hand and w*ish*ng in the other and see*ng wh*ch one f*lls up f*rst. MS is helping us with the "one hand"... will someone like Google step up and promise their spreadsheet will exist in twelve years? Not everyone left Eqypt in the Exodus, but enough did to keep a whole kingdom running for a few hundred years.
@Frank: That might be Windows, or MS apps in general. For me, Excel opens on the correct monitor, first of the day, almost always. Maximized nicely. Open a file then close when done? Pops over to monitor #2. (By the way, glad SDI solves multiple monitor issues, right?) Put it back and it stays put. However, the many times a second instance opens, it's the same cr@pshoot you see for which monitor it opens on. And whether it's maximized or not. And even if it IS maximized, the first window it opens (the one that persists if you just close the file, not the instance), sometimes called the "gray window" by people, is often a normal window trying to get lost in the shuffle and left open.
All s*cks. But...
I've noticed similar problems with other MS programs. Not precisely those, but Explorer, say, sometimes opens as an odd window for no obvious reason. Word? Never a problem. Anything else MS, whether Windows or Office, yeah, one odd behavior or another vis-a-vis the window the open, sometimes.
Most obnoxious of course, are the subsidiary windows: password entry boxes, file saving boxes, pretty much any dialogue type window, find, find and replace, sort... you get the idea... ALL OVER THE PLACE. They stay in place for days, then all of the sudden hide away in some corner of the program window.
It was using Acrobat (X, not that sh*tty DC version), its file export and import functions in particular though that suggested something other than the intentional forcing me to use a window if only for moments instead of everything being maximized unless I had a particular need to see two programs at once, though the second monitor solved that need forever — the intentional forcing of "normal" windows upon me that it seemed like MS was doing (As in: "Oh, if only the unwashed would JUST try them, they'd understand how wonderful sh*tty little windows all overtop of each other are! Then they'd sing their praises and use Windows the way God intended it to be used!") — that something else might be happening.
It was a glitch in Acrobat that suggested Windows, running on a computer that puts any I had before it to shame, now needs maybe 5-10 seconds to close out actions it takes involving files. I've experienced this for years now, not just the latest computer so it happens with regular hard drives and with SSD types and it happens at the computer level at home as well as with networked files at work. I just never focused on it before.
If I gave Acrobat a 5-10 second count before the next click, its glitch went away. Completely. Not ITS glitch: it's the underlying file operations of Windows, of that I am convinced.
With the exception of the monitor jump that ALWAYS occurs when I close the first file of the day in Excel (But not if I open a second one first using File|Open, even if I close the second one before closing the first one.) all of the things I hate vis-a-vis window sizing and location (for dialogue boxes) seem to be solved by making sure I remember to give Windows 5-10 seconds after closing a program before shifting to another or launching another. Basically, let Windows save final conditions for things, I think, before moving it on to another thing. I SHOULD have noticed this with IE, how if I re-launched it immediately (1-2 seconds) after closing it, it would abort deleting all the browing stuff it is willing to delete and turn the files over to the new instance rather than give it a set of its own while continuing to delete the old. Seems obvious in retrospect, but the Acrobat thing (not their glitch I am now sure) that opened my thinking.
Hence my saying what you experience might be Windows jacking you with inexcusably slow file operations work that it aborts the moment you launch something new so it can concentrate on the something new. Once aborted, it doesn't seem to go back to it, especially if you're re-launching the program.
Worse, given how MS now does the SDI thing, and you have constantly launching instances, some of which will persist (keeping a deathgrip on certain file operations) because you closed the file, but not the instance, I think you're seeing Excel launching and seeing incomplete or contradictory garbage for loading and choosing the default ideas that make sense to the 75 windows overlying each other people in Redmond. Kool-Aid drinkers doing OUR thinking and choosing for us. Yay.
Another way of putting it is that I think it's a MUCH larger problem than just Excel, though given Excel's extreme shakiness, its frailty, due to the SDI garbage-implementation, and its presence in EVERYONE'S day, we see it there, first and a lot.
(That frailty actually disturbs me more than any particular obnoxious expression of it does.)
@Deanna... Please Deanna... broken up? Have you never seen a YouTube video of Bill Gate$ patiently explaining to us all why monopolies are incredibly good for an economy, and let's not forget, for a society?
It seems that a monopoly can make decisions against the desires of consumers, for the better, of course, forcing them along the path from their current miserable existence to the shining future the monopolist, in his benevolence will allow them to have. I mean give to them, oops...
'Against the desires of consumers' because they control the market and will still make tons of money regardless. The phone companies planned for us to be moving out of ISDN lines roughly now. That sort-of-128Kb transfer speed would still surely be better than today's 100Mb transfer speeds. If only we weren't so greedy, we could still be living their vision. Bad consumers!
Of course, John commented nine months ago and there's no update on anything, no rumors, no experiments forced on 365 users so... it just doesn't look good.
You had a window open for each file with a full set of tools. You could ALWAYS position them however you liked, move them to different monitors, tile them, cascade them, whatever you liked. Multiple monitors weren't any kind of issue if my plain vanilla equipment in XP, Windows 7, and now Windows 10 could do it.
You could always open a whole new instance or three if you liked. Files with the same name, say.
It wasn't like the old days when there was one window with the program interface (menus and such) and every file open had to share the real estate below that because they couldn't be drug out of the instance's basic framework window.
So PRECISELY AND TRUTHFULLY, how was there ever an issue?
Obviously, the Undo stack was shared by all the open files in the instance and that sucks, but that is a separate thing to fix.
Then... SDI... Every file opened is in a whole new instance. Even with the registry fix, I can't open an attached spreadsheet from Outlook, their own directly related program, without doing it in a new instance. And others that I can open in the running instance have to be drug onto its window. I can't click to open them or... whole new instance.
It is a nightmare. There are VBA issues. There are issues with files that depend upon each other. YOU try explaining to the boss that he now has a new level of difficulty to work with to use your nice spreadsheets. We used to be able to group spreadsheets and open them in sequence. Took that away. Very quick and handy it was. Now it's write a unique macro for every set or deal with the links that don't/can't update even though the files are open. In the context of this development, I have to think they took that away some time back because of things that led to this issue or because they chose to follow a path to here that along the way lost us this capability.
Worse yet, the instances seem to be "owned" by the application that opened them. If I open an Outlook attachment and the new instance starts, I can keep opening Outlook attachments and they all open in that instance. That somehow has to be figuring into this as well.
There is NO control over how the new instances open. So instead of maximized and on the monitor of my choice, they come up literally willy-nilly on their choice of monitor and always just a stupid "normal" window that I then have to maximize. Not to mention the dead one that opens and launches the new file. Sometimes it closes when I close the file, sometimes it stays. It is always present in the Task Bar, until closed.
There is and was NO monitor issue unless it was very weird and almost certainly limited to a small slice of users. If plain vanilla has/had no issues, then there just wasn't an issue.
I will note though, that we finally read something from John that is certainly true-ish. When I first came here, there was a steady whine, though NOT a roar, about having changed FROM SDI to MDI. Made no sense to me because it had never existed for me: My experience since the ribbon has all been what I described above, 10 years of it, until I got stuck with this version. Even 2013 was the good stuff though everyone says no it wasn't. Strange, that. But there were constant cries to return to SDI though they always petered out with a few votes. Sad that more than a few votes doesn't seem to take us back.
Seems like it should be a choice in Options. Or at least with some registry fixes. (I'd be thrilled if the attachment opening could be fixed that way, or the dead window each new instance starts, or the window state when they open, or the whole having to drag because clicking would open a new instance thing... or all of them...)
Hmm... maybe MS is trying to hide performance issues in Excel by forcing each file into a separate instance? Whole new set of resources... alleviating performance issues some? Fix this MS or you won't have any reputation left as we think of more reasons why you're doing this to us. And if you go the way of 123 and Quattro Pro, WE will write the story of miserable failure to consider your market instead of your own weird ideas. Right or wrong, just like those who wrote the 123 stories, we will write how it looked to us and then it, like you, would be history.
Be nice if you chose a different fate. We DO actually like Excel, for the most part.
@Doug: Sorry to hear that... I would say though, for two reasons: 1) With it in the registry and set to 0 I at first thought it had no effect (the longer story is below somewhere) but eventually realized it DID have some effects, just not a wholesale changeover back to MDI. I could, for instance, drag a file from Explorer onto the running instance of Excel and it would open in that instance where without the key, it would not even open in a new instance. I cannot drag from Outlook, and maybe other places, oddly enough. However, before the key, if I opened a file and a new instance was created, that instance persisted after closing the file while now it 90% does not. I still cannot click on a file without a new instance. Also, files opened from inside the instance, say via a hyperlink, open in the existing instance where before they started a new one. So... maybe like me at first, the changes, not change, were not noticed because we were both looking for a "change" not "some changes for the better, but still sucky-ish"? And 2) The Support article does not explicitly say so, but strongly implies, it should work for anything affected from 2013 onward. Not definitive by any stretch, but maybe worth a shot.
But if not, I feel for you. SDI SUCKS. And weird things seem to happen. And to think I used to think it was nice I could have it when I wanted it (say, for two files with the same name... another thing MS is pathetic about given there's no way on God's green earth it should present even difficulties, much less be utterly not permitted... you can't even achieve it by accident without a fresh instance of Excel) which wasn't often, but I wish now that no one at MS had ever had the idea cross his mind.
Sounds like MS is beginning to look at Home the way some providers look at Evaluation copies. Sad, for something they sell for a pretty penny and certainly do not state is crippled.
Something I've seen as a topic here, bearing on the discontent with the subject, that I'd like to point out is NOT necessarily what people have said it to be:
"The investment of time in switching to a new spreadsheet would be just so huge that I have to have satisfaction here, or live with suckiness forever."
Well, for those not old enough to have gone through it, people said that about Lotus and Wordperfect once upon a time.
More tellingly, Lotus and Wordperfeect said that about Lotus and Wordperfect.
What are those programs? Something used in WWII to break German codes? Maybe Apollo used them for the lunar landings? No. Those programs were given to us by God as the only things that could ever be used, but they are gone now. Admittedly, a large part of that was an aspect of monopoly power, but I'd say Google, at least, is in a position to do the same thing. And guess what? Their spreadsheet is free too.
MS had better get it right. I ditched 123 for Quatro Pro, learning curve be d*mned and then Quattro Pro for Excel because they broke them, literally, broke and not paying bills, but still learning curve be d*mned. I gave up Locascript for WordStar and then Wordstar for Word, learning curves be d*mned. All of them lesser programs, at the time of shifting, than what I had been using.
And another for the record: the competition uses Windows, unlike MS in competition back then so no advantage to MS there. Arguably, because I hate it, but have no power to change it and it must change to this for society's sake anyway, the fact that Google has built to use the cloud (storage and app farms, to anyone who remembers Lotus and Wordperfect, "magic" to anyone who remembers Locascript and Wordstar) while MS is slumping its way there, in poorly conceived ways, and maybe not for another decade (the threatened sea changes in their engine, vis-a-vis the seven new wonder-functions, which it is hinted will make using older versions impossible as backward compatibility isn't part of the functions but rather a backward compatibility ("xlfn.") engine that is NOT mentioned despite being pretty revolutionary (and in place for a little while now) and that can be clipped out of the program in a second. With a SNIP... no more effort than that... no more backward compatibility.
At that moment, several hundred billion spreadsheets would be dead... except that Google stands ready to help. Lotus and Wordperfect all over again.
And in the coming move to a big iron cloud that you only interact with, seldom running things at your own desk, and having incredible implications for the cell phone market as well (important in itself, but also a thing utterly beyond MS control, which is how ideas win - Blu-Ray won the moment its capacity increased significantly as suddenly why would anyone buy a few hundred million computers with 60% of the optical storage - nothing to do with movies like the HD people aimed at - the whole second, unrelated market thing is massive in economics), Google is poised, like MS remembers itself, to go from a few percent of the market to 90% in a couple years, then enjoy 20-30 years resting on their laurels, milking it. If they make the milking choice, knowing their end would come similarly, someday.
So a big iron cloud, with no Windows needed, and a competitor built on that model... what is MS's profit going to do under those circumstances?
Or they can do a wee bit of work and satisfy both sides of this issue (there is an argument in here the OTHER way around, though it gets little interest as they are winning right now and not anticipating having to argue their side again). Stave off the liquidators a while longer.
Or not. But it is not actually that hard to shift major programs. We've done it before, as a society, and we will do it again. Here? Maybe? If we have to. It's a pain, but not an unending one and makes room for a whole new generation to rise bringing new (old) ideas with them. So... whatever MS. IGNORE us. P*ss on us with mendacity (read ol' Johnny's message to us) and outright lies. See where it gets you.
I will have a perfectly functional spreadsheet to use whether it is yours or Google's. To steal from NFL Films: "YOU make the call."
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.
Sadly, it is no longer my most recent registry edit and it seems to have been deleted from here, a whole string of comments and comments back, 6-7-8 by different people. So I cannot say for sure.
However, based upon memory, it was in a key in the Office structure and that key had a LOT of entries. In addition, it was a DWORD entry.
So I will say it is the following key you want to add it to:
or whatever corresponds in your system. It has entries for Custom Margins through Monitor Topology Fingerprint through Use System Separators.
The name to add is, I believe (not from memory this time, but because it seems to fit best and EVERY other one there is pretty clearly not it):
and the value to give it is zero ("0").
(Because it is asking if you want to DISABLE merging instances which would disable all the MDI characteristics it will let you have. So 0 = False, as disbaling is NOT what you want to do, and should leave things enabled.)
As I mentioned, it is not a return to MDI, and I went back and forth on it and deleted it too, hinking it had done nothing. Even thought it was a "ha ha" comment from a jack*ss until one of the comments had a Microsoft link for it (the real kind, one of their notes on how to work around their *****-ups, which, to be fair, though not happy, they DO come up with and offer while lots of others don't at all). Then I put it back in and suddenly caught on to the wording.
The wording is like the way people write ballot initiatives, so a vote against turns out to be a vote for...
Given the other stretch of comments is gone, I'm thinking this will only last so long and then be gone too. Stupid, really, given that I am less aggravated now I have this much, and one would expect others would be too, but... "beauro-think," aside from being the oxymoron of oxymorons, is a strange thing.
The registry modification DOES help out a bit. Now I can drag a file from Explorer onto the instance of Excel I have open and it will open in that instance where before the modification, it just wouldn't.
However, that's aggravating to do. Clicking on the file in Explorer still launches a new instance of Excel. Don't get me wrong, I'm not undoing the modification but it isn't MDI.
Further, I still can't drag an attachment onto the running instance of Excel and have it fire up. Won't open it at all. And every method of actually opening it opens a new instance of Excel. So... not MDI in the least.
Lol, boy, they sure showed all of us "How could you not have a separate Undo stack for each file" people, didn't they? With SDI, it's completely not a problem...
Lord, the way some people apparently think...
Yes, this is about "Unless I PURPOSELY choose an opening method that starts a new instance of Excel for opening some new file, I want every file I open in the same instance of Excel, NO MATTER HOW I DELIVER THE INSTRUCTION TO OPEN."
So I open 50 spreadsheets? They are ALL in one instance of Excel. I might have used File|Open, I might have clicked on a desktop icon, I might have "Explored" for the file and clicked on it, I might be opening an attachment in an email: WHATEVER way I told it to open, it opens in that one instance of Excel.
If that causes problems, they are my problems.
Further, the idea this has to do with solving some issue with two monitors is either very particular to certain situations or I have, without the least effort or knowledge on my part, hit upon, utterly by chance, the better solution to it because for two years using 2013 I never experienced the issue. That reallllly suggests it is only in certain very particular situations, eh?
And I can't even get the functionality by being careful in limiting how I open things. For example, I can "Explore" for a file and drag it onto the window for my single instance of Excel and I get the desired result though certainly not by just clicking on the file as I'd prefer. BUT... an attachment from Outlook simply WILL NOT open that way. I have no choice at all but to accept the second instance. So I cannot, through great care and effort even simulate the way it should work without effort. I imagine I shall find other instances in which this is the case.
Further, we often have pairs of files we need to open here and for it to be done by users with no interest in learning the least thing about Excel. Until now, we could set up a couple icons, label one "Whatever-First" and the other "Whatever-Second" and they'd handle THAT flawlessly. Now that is going to open them in two different instances of Excel and quite fail to make one available to the other. Yay.
And this back and forth business... New version of Excel? Oh yeah... it reverses the decision made on this in the last new version. So we have to go back and forth with this issue and when people have different versions... Spreadsheets are supposed to be quick-ish and simple-ish solutions to problems, especially ad hoc problems, not a new source of problems!
They won't make simple, obvious changes to functions (like CHOOSE()... we ought to be able to give it a range and it would choose from the elements of the range... but it treats the range itself as an element... if we want to select from the cells in the range, we have to type each cell or its contents individually: i.e.: should be CHOOSE(3,A1:A4) returns A3's contents, but now we get an error and must use, say, CHOOSE(3,A1,A2,A3,A4) to get A3's contents) because, oh my, it could affect millions of users and their now "legacy" work. OK. Pretty valid, as a starting point. BUT... how is it something like this which is extraordinarily basic to usage can be changed willy-nilly with not just one sea change, like I mention for CHOOSE(), but with each new version hitting the streets??? Back and forth, back and forth.
Of course, given the reponse to our desires and comments (as opposed to the useless and mendacious "attention" they get), maybe I should just be grateful the back and forth could have some very small component of responsiveness to the other side of this argument... Grateful to the gods that I got a crust of bread three weeks ago rather than railing at them for starving the three weeks since?
That's not acceptable.
And it wasn't really responsiveness to preference and utility that the other side claimed/claims anyway. It was that multiple monitor situation. Those folks railed for a long time, like we are now, and some still do because they still have versions that do what we like, and never really got a response. People with a "real problem" got a response, and the rest of us got slotted into a group called "whiners" and, you know, you can never content a whiner so why try at all, right?
Well, since the last comment was meant to clarify so that some don't provide comment arguing for the opposite of the idea (Hope I haven't somehow misunderstood myself... could be I need to find a different suggestion to follow and support... by now I could be arguing against myself!) further clouding the issue though MS clearly understands each side of it and would be indulging in mendacity if they claimed we don't even know what we want.
What I want is at the start of this comment: one instance with natural opening via all the ways Windows and Excel provide for opening files yielding them all open in the one instance and the ability via, among other possible ways, of forcing multiple instances by using Ctrl-Click on the Excel icon. Only intentionally opening a new instance would do so, not that being the default. (Hope I'm in the right place to support that... it'd be embarrassing as well as non-productive if I'm not...)
Good Lord. Some loser keeps putting posts in with the "F" word for two out of every three words (you've seen them by their dozens), and I put "S a t a n" (do I need to say "without the spaces?) in and THAT gets asterisked out???
This place is ****. I live in a world filled with ****-a-holics. Find a 12 Step program and work it you clowns. Just... do NOT come around to me in whatever step that "make amends" step is. Just live with that blot on your soul.
Just how hard a concept is this? This has even more support than separate Undo stacks for each open file.
And the opposite choice is just as supported.
So... Jesus... OPTIONS... a simple setting in Options people, pick the one you want.
Pick Always SDI and that's what you have. Pick Always MDI and that's what you have. Slight extra for the MDI folks: you can always CHOOSE to open a new instance of Excel and then open a file in it and so have SDI when desired.
And then it's all over. Everyone is happy. Each gets his desire.
My world was fine until IT overwrote my Excel 13 with Office 365 (32 bit even instead of 64 bit... who even thinks like that?). Now I am back to the evil world of SDI. Lived in that world four to two years ago, then lived in heaven, now today I'm back watching for ***** to come striding by.
OPTIONS. Man o man people, just how hard is this?
3 votes1 comment · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
(Sigh...) Would be awesome.
2 votes1 comment · Excel for Windows (Desktop Application) » Macros and Add-ins · Flag idea as inappropriate… · Admin →
Any time you want to do something to all sheets at once, or any chosen subset, just select the sheets (using their tabs) and then perform the "something" on one of them. It will occur in all the same cells in all the selected sheet.
Protecting the cells is no different. It's just a format. You could, by the way enter data, or formulas, perform a paste, move columns or rows, create filters... almost anything you can think of doing. With protection, be sure to do the whole process including going to the ribbon for the last step.
Your only worry is that you select a page that should NOT be included in the change. But there are all sorts of mistakes like that to make in every spreadsheet, so likely you're on top of that kind of thing.
3 votes7 comments · Excel for Windows (Desktop Application) » Viewing / Navigating Workbooks · Flag idea as inappropriate… · Admin →
Someone WANTS that horrid thing. No one NEEDS it.
Someone WANTS it. NO ONE needs it.
And GOD NO.
No one needs that horrible creation from WORD.
3 votes0 comments · Excel for Windows (Desktop Application) » Tables, Sorting and Filtering · Flag idea as inappropriate… · Admin →
Go brother, you finally found one that will happen in a very short time! Like nect 1st of the month. Then the next first of the month. And as the Teletubbieis used to say" "Again!"
153 votes36 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
Thanks Majid Mj.
I'd be more outgoing with my appreciation if it actually worked, but...
Be sure to vote here too:
The good news is that even after 3½ years, they STILL plan to do it, that hasn't been downgraded! Talk about slim threads but... Hope Lives!™©℠®♪
(Maybe that last one♪ will get the RIAA to take our side. Tenacious folk! Even Disney, the original Lawyers-Я-Us™©℠®♪, doesn't try throwing lawyers at THEM.)
Rather than changing 50 functions, or only someone's half dozen leaving the others to hobble on, we need a true NULL.
Just that, and it's all done.
If I will populate a region using formulas, then PASTE|SPECIAL|VALUES to clean the formulas out, but need cells to really be empty (NULL's), I currently have to use a string the data would never contain like "MMM" instead of "" and Find and Replace afterward with nothing. THEN I finally have a true NULL back in the cells. (Ugh, don't even get me started on how, when first doing this, decades ago, I figured the string "***" would NEVER be in any of my data... great, never was, but then the boss sent one of my spreadsheets to someone outside the company...)
I have needed this for DECADES now. Plural. I am not interested in tinkering with a function
or two, maybe half a dozen popular ones. I need this for sorting. I would have used more dynamic formulas a LOT earlier if this had not been an issue, but it's mainly for sorting.
Do you have the least idea how much time I've spent over the years JUST answering and explaining to users of the spreadsheets (bosses mostly) why sorting did not work out as expected, how to solve that, etc? Every second wasted and often left a suspicion in their minds that the spreadsheet was flawed, so it could be VERY flawed.
Not to mention the time wasted with the "MMM" and F&R.
But it's NOT limited to sorting something you've done and maybe could have done differently (somehow, when it's YOU telling ME how I should choose a different method, that's all fine, but if I tell YOU some other way is better, there might be a million reasons you would offer why not, but usually people settle on "I don't want to do it differntly, I want to do it this way," and that is sufficient... but it just meant I was stupid when I used it — not pointing fingers at anyone here!, just sayin').
No, not limited to those at all: Importing data. Nulls in, say a CSV or TXT file, come in as "" type entries. So EVERYONE importing anything in which that can occur, then adding data, ends up with the problem. Again, the sorting issue. Cell counting issues. How many items in a column. And so on.
Unless the import is sorted, column after column, the collection of NOT NULL's found, and deleted so you have true blanks, true Null's. Up for a little bit of work today boys? Importing 38 columns with this one. Sort and so on away me hearties!
So I need a true NULL available in formulas AND in the data import process. Once I have it, no function will give me any trouble. For anything legacy in all the spreadsheets out there, until it is upgraded (or until forever, eh?), there's still good old "". It is ALREADY in ALL those legacy works, so nothing will be broken.
Now, from that angle, I do understand the let's tinker with my favorite function approach. Instead of upgrading one's work, which admittedly might never happen, I imagine lots of folks would like their favorite don't-work-functions to suddenly work. Nice, for that I guess. Nightmare for most of us and not on point for the sorting and data import aspects at all. But I guess I get it from that slightly askance viewpoint.
Give me a real NULL, available in formulas, and as a value for a NULL in imported data. Give me PRECISELY NOTHING. That's all I ask for: PRECISELY NOTHING. And lots of it.
1 vote0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
2 votes6 comments · Excel for Windows (Desktop Application) » Macros and Add-ins · Flag idea as inappropriate… · Admin →
Does NOT exist: he'd like it to be immutable and even LG DES believes it to be changeable via VBA code.
Actually, it's harder to change than that: must use the VBE tool, not code. But still, easily done if anyone wishes. WHY anyone would wish to mess with it when they can juist change the tab name and satisfy whatever their urge was would be unclear, but they still can dynamite your work. Not immutable if not unchangeable, so... does NOT exist.
(And... users are insistent about that kind of thing sometimes. Boss-type users. Like the old David Letterman bit about "Stupid Human Tricks" where he tried to make sure you knew he meant stupid tricks by humans, not stupid humans doing tricks, wink, wink... bosses seem to be that latter sort all too often.)
But like everyone says, the Code Name is the closest there is now.
Be real nice if it could never be changed though, and you could count on that fact, writing your code using it so the code would never suffer from user-touch.
As JanR says. But also, using the same VBE tools, one can change it so long as the new Code Name begins with a letter.So if a user wants to change it, the user can.
Also, not usable in formulas and it would be pretty nice if it were. And if immutable, your formuals and macros would never fail. For that matter, if it displayed in a formula with the Tab Name, there'd be no screwing around.
Be nice if one could set a property, a formatting, that could be protected so that things showed in formulas as they result, so build a sheet name when building the spreadsheet, change the setting, then see the resulting name from then on, perhaps differently colored to let you know a formula underlies it. Nice for understanding your workbook's logic. Hmm...
2 votes0 comments · Excel for Windows (Desktop Application) » Opening and Saving Files · Flag idea as inappropriate… · Admin →
8 votes2 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →
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.)