Feedback by UserVoice

Roy

My feedback

  1. 702 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Hello,

    Thank you for your continued feedback! We are happy to let you know that the work to enable this feature has started. We’ll share more details as we make progress.

    Thanks,
    David [Microsoft Excel Team]

    Roy commented  · 

    I'm surprised that 219th comment could be saved...

    Roy commented  · 

    Not looking for it, no, but thanks, of course.

    Excel still fleshes it back to out to its complete and utter formal pathway so it won't solve the length it adds to a formula issue here. Sadly, since it's such an easy fix. Been using that approach literally since hard drives were still too expensive to buy. Sigh... I remember that first 2 MB hard drive fondly... And still hate Radio Shack for selling me a 40 MB hard drive but selling it as a 32 MB hard drive and me never suspecting they did that for about three years. And I bet some of them still wonder how they went out of business. At least IBM simply sold you the lesser equipment for a higher amount. Sucky, but not dishonest. Ahhh... memory lane... the place this stupid limitation belongs! Memory Lane... seems to lead to Memory Avenue to Memory Court to... now I see how old folk get confused... stupid limitation!

    Roy commented  · 

    @Tom:

    That will work if there are not external references to resolve that lead to the "correct" file location (rather than the saving before moving location) AND you do not need to build references to its contents in other files.

    First kind might be related files located together with it. If referencing the right location in the formulas involving them, you'll exceed Excel's ability to resolve them as they are the things that exceed the 218 character limit. Not actually the path to the location, directly, but rather its length's effect on formulas referencing it and other things in a path that leads to the reference exceeding the limit.

    Second kind of failure, even though the trick worked to save this file and relocate it, would be other files then referencing it using that path and thereby themselves exceeding the 218 character limit for a reference in a formula.

    Depending upon how you or others involved handle errors returned by the applicable formulas, you might never notice errors while building them as the error handling catches this along with others leading you to not think anything new and unique has happened.

    But when you go to save that file...

    Sad, sad situation really. Can't even alias a location as "drive Q" or what-have-you as Excel still resolves it fully and dings you for the length.

    Maybe someday, b*ttm*nch programmers and/or their bosses will stop saying "who would ever want more than that..."

    Roy commented  · 

    Yep, true... and just what IS "manifested"...

    Seems it's an evolution of the .INI type file that used to be prevalent. Sort of.

    More importantly, operationally, how do you know it is?

    If there is a file with an ".manifest" extension for the executable, it is manifested. Checking, I find a file called "excel.exe.manifest" immediately after my EXCE.EXE file so it looks like Excel is manifested.

    Roy commented  · 

    @Ken Holmes:

    Yes, there is an additional step:

    "Enable Win32 long paths" in the Local Group Policy Editor

    Do this by clicking the "Start" button (I'll call it that until death I suppose, whatever MS calls it currently) in the lower left corner of the screen (usually), type "gpedit", and open it when done typing.

    (I have seen the internet say the editor is only available in Windows 10 Pro. When wanting to do it, I feared I would have to involve IT and argue for it, but it works from here and doesn't even require Administrator priveleges, or opens with them by default.)

    Once open, navigate to: (double-click on)

    1. Computer configuration (in the tree on the left)
    2. Administrative Templates (over in the working area to the right)
    3. System
    4. Filesystem

    In Filesystem you will see "Enable Win32 long paths" — click on it and enable it.

    I have attached a screenshot of the point at which you select it.

    While there anyway, you might want to navigate about some other options. I found a couple that were hard to interpret without thinking that ALL my efforts at using privacy options in Windows provided software, including IE, had always been worthless because the settings here were not enabled. That they provided the fiction that you were protecting yourself but because you never knew to come here, nothing much actually took place outside some (ineffective) things like deleting cookies and such.

    Anyway, without doing this step with "gpeditor" the Registry entry will do no good. It seems.

    Roy commented  · 

    @Ken Holmes:

    There are two aspects to the problem and they differ in detail.

    First, OPENING. Windows ships with a built-in path length limitation of around 260 characters. (The particulars that make it greater than "256 characters minus a couple" don't matter.) That is the usual downfall for folks opening files. Their Windows installation is limited to that as-installed length limitation.

    So they work in, say, SharePoint which itself will resolve paths of somewhere around 400 characters. Naturally, people use those characters, not just because they're available, but because the nature of SharePoint and its like is to have files in upstream servers or off in "the cloud" somewhere with all the characters those places add. They try to open something with paths longer than their Windows system allows and it doesn't work. If they try to open something with a short enough path, say 240 characters, Excel is perfectly happy to do so.

    Because OPENING is not where the 218 character limitation comes into play.

    A side note: a file can be created in a local directory, not running afoul of the limit when SAVING it, then be moved to a much longer path name location and not be openable. The writer might respond to complaints by opening his local copy... everything works, must be a user problem.

    Second problem is SAVING. Excel will actually refuse to SAVE a file if the path selected is over the limit (218 characters, though often WAY less: I've documented paths of 174 characters being the max for some files. (Oddly, "some" not "one.")). Again, this does not affect opening, just saving. As long as one saves it to a path under the limit, the save works.

    Then one can see a further complication. Some people make the fairly easy change to their Windows implemtation being able to use paths 32,767 characters long. Easily surpassing operational limits for things like SharePoint. So one of them may be able to open a longer path file while others report NOT being able to open the same file.

    So for opening, you might see people limited to pretty short, but longer than 218 in any case because that applies to saving and is no issue to opening, path name files where others might be able to open those files with no difficulties.

    And for saving, a person might never have a problem with the 218 characters because he always works with a path shorter than the 218 characters (local copy, strangely jacked directory names to work around the issue, that kind of thing...) but users who do work, then try to save the in-use file (not in a local directory, for example) might find Excel refusing to save it. Saving a local copy of it, then moving it to the proper home could work if other users are luckily not involved at that moment, but otherwise...

    And bear in mind the opening problem is solely an issue affected by one's Windows implementation but saving is an internal, Excel ONLY problem. It does it to be able to resolve function references and has a coded limitation, rather than drawing upon a Windows feature that limits it.

    So one can fix the opening issue, but not the rest of the interaction: creation, saving of day-to-day work, and the one folks don't mention which is all those functions that Excel might not be able to resolve due to the path to some file being linked in being to long to resolve. That, admittedly, might not occur too often, but folks stepping up to bat with higher level things like SharePoint often have multi-file spreadsheet set-ups for various reasons so... it likely affects some folks.

    Worse, how would you know? If a result you look at still comes up with a number, not an error, and it's close enough to an expected range of results, why would you ever notice that one or more inputs to it are simply not there? Maybe they're even averaged with several other values so there is no error result even really possible. You just aren't using all the inputs that make your results valid. I can picture that breaking a federal law now and then. I can picture a bid for a public-private contract with 300 pages going way south, profit-wise, due to this kind of thing.

    But essentially, the 218 character path length has nothing to do with opening the files so your experieence is not surprising.

    Roy commented  · 

    The 260 vs. 256 characters expl;ained:

    (from StackExchange: https://stackoverflow.com/questions/1880321/why-does-the-260-character-path-length-limit-exist-in-windows )

    (Entire Answer quoted below.)

    "Quoting this article: https://docs.microsoft.com/en-us/windows/desktop/FileIO/naming-a-file#maximum-path-length-limitation

    Maximum Path Length Limitation

    In the Windows API (with some exceptions discussed in the following paragraphs), the maximum length for a path is MAX_PATH, which is defined as 260 characters. A local path is structured in the following order: drive letter, colon, backslash, name components separated by backslashes, and a terminating null character. For example, the maximum path on drive D is "D:\some 256-character path string<NUL>" where "<NUL>" represents the invisible terminating null character for the current system codepage. (The characters < > are used here for visual clarity and cannot be part of a valid path string.)

    Now we see that it is 1+2+256+1 or [drive][:\][path][null] = 260. One could assume that 256 is a reasonable fixed string length from the DOS days. And going back to the DOS APIs we realize that the system tracked the current path per drive, and we have 26 (32 with symbols) maximum drives (and current directories).

    The INT 0x21 AH=0x47 says “This function returns the path description without the drive letter and the initial backslash.” So we see that the system stores the CWD as a pair (drive, path) and you ask for the path by specifying the drive (1=A, 2=B, …), if you specify a 0 then it assumes the path for the drive returned by INT 0x21 AH=0x15 AL=0x19. So now we know why it is 260 and not 256, because those 4 bytes are not stored in the path string."

    Roy commented  · 

    There are two separate issues stemming from a single problem.

    The first is that Excel seems to have a 260 character limit for resolving a cell reference. The apostrophes and exclamation point in the sheet name all get added in and then seven more for the old style cell references (65536 rows, 256 columns) for 41 characters max, so 260-41 = 219. One character seems to be taken somehow by the Excel system leaving 218 characters to be safe, sort of, for the rest of the path to the file that holds the cell. Literal characters, apparently. If that 260 characters is exceeded, the cell reference won't be properly handled and so things could go very wrong.

    So there's the problem, inside Excel.

    The outer difficulty is that Excel doesn't save files, it has the operating system do it. So an operating system with no such problem saves anything valid to its own rules and limitations. Windows, for example, can use as many as 32,767 characters if YOU enable it to. ("Enable Win32 long paths" in the Local Group Policy Editor — where it says the "normal" 260 character limit even though Excels support bandies about "256" even though things add up to 259...)

    Excel makes no check to see if you really have an issue, it just checks the path length against its hardcoded limit. You might be able to do a 250 character path, or 238, etc. with "the facts on the ground" but no...

    Worse yet, experimentation on my computer before enabling the long paths had various files with no cells addressed in them, just a couple cells of text, failing to save and re-open with anywhere from 168 to 190 characters vs. the 218 promised. So... there's more here somewhere. Oh, local drive C, no network paths, I didn't want the vagaries there to affect the experimentation.

    The aggravations mostly noted come from the totally outside problem that we now routinely have very long paths possible. Various programs that want you to buy them and collaborate roll their own" to enable longer paths, but they all vary, most likely due to each's own focus. A Balkanized world rather than the operating system simply providing and Excel working with it so middlemen don't have to figure it on their own.

    Worse yet, some code inserted into Excel that would read an address as text, provide an alias for the path, if needed, maintain it until the file is closed, and then take the resulting string and regard that as an address could fix it all immediately. No major rewrite, just an insertion. (Lol, I know, "just a wee little insertion..." but really, it should actually be that.)

    So even enabling the operating system only works for a program if it is "manifested" for it and wouldn't solve (doesn't solve) Excel's internal problem anyway.

    Meantime, all those programs and services have likely got a way to give a location a short alias and one could use that. Take a few hundred characters off the path that way, let Excel see the short path, allow the Save, and then the environment program would trade out the alias for the real path and present it to the file system for saving. If they can't do that, they really need some complaints too!

    But given MS's push at collaborative solutions, it would seem this is a thing that would be hugely important to them... oh wait... stuff like PowerApps doesn't have problems and would earn them a TON more money... sensible people should just pay up and everything would be great... things are much clearer now... poorer, but clearer..

    Roy commented  · 

    The upside here is that hope cannot possibly be futile in this case.

    Consider how MS wants the computing world to move forward. Things like Sharepoint are essential to it as are things like moving people to the "cloud" in its full glory: app server farms as much as storage farms. Either side of that is going to require much more complicated paths.

    Forcing everyone to use shortened naming and substitute pathways is mostly a non-starter for a litany of reasons. Result? Not just what the IRS likes to phrase as "I'm feel like I'm, feeling some pushback here when you claimed you were being cooperative" and not even outright refusal to do so but rather a simple ignoring of their desires no matter what and the folks buying things regarding a variety of products as "broken": as "not career enhancing"... and so simply no one purchasing their efforts. A flat "we're not even saying 'no', we're just buying something that works" result.

    So not only can and does Windows support a 32767 character path for anything with a path so they already have an incredible upgrade in ability available for no cost or effort at all, but also, they must do it or wither on the vine they are choosing to force us to.

    The only difficulty then is rewriting the portion of Excel that limits, on its own, how much path length a function can address. It seems functions can address slightly less than 256 characters and some of that has to be left for the file name and cell or range of cells being addressed. 31 characters seems to be the assumption, though reading the explanation they give for that made it seem like it should really be a few more... it adds to more than 31 (just the sheet tab name alone can be that long, exactly that long, so it suggests that's the real reason for the 31 and the cell or range isn't in the sum at all)... with the rest being available for the actual path name to the file. (Add a full cell range to the 31 and the 218, and one exceeds the 256 max possible: maybe there is something in that for why certain things mysteriously fail. Hmm...)

    In any case, from THEIR OWN viewpoint, this is an utter, ABSOLUTE MUST. So it is very likely they mean it when they say they intend to include it in upcoming work.

    (Of course... if, in 3½ years the ADMIN comment is 3½ years old and still no changes... feel free to question my mother's species... Bill Gates must have loved the movie Brazil.)

    Roy commented  · 

    They limit it so as to keep the files theoretically findable and openable/delvable by formulas in the program itself.

    Windows itself can allow 32767 characters if one sets it that way. Excel is interdicting the operation. Why not change it to allow any old path length? Bet it's tied up in a kludge that involves still maintaining some 8-bit limit that impacts other things too.

    Why not fix those? Well... might as well ask why some functions can reach into a closed file and obtain data and why some can't. Literally. My wager is some use the old methods associated with this problem and CAN do it, while along the way someone added a new approach and the ones that cannot reach in are written using the new approach. Or maybe the other way around makes more sense.

    Hopefully they really mean the "PLANNED' thing instead of it ending up like a couple others aging like a Hollywood starlet. Seems like this would be a fairly simple change and the only downside going forward would be that the functions that would no longer reach into files would seem to fail to do that for some unknown reason. Or maybe it's all upside, if all of them now would. Old limited paths would work fine in a larger setting, so there's no obvious downside there.

    So, one hopes...

    Roy commented  · 

    No. But thanks for thinking I might be.

    Are you an a**hole? Walk like a "duck"... talk like a "duck"... people might think so...

    Roy commented  · 

    Mr. McGuinn!

    You are a troublemaker! Me, me, me!

    OTHER people don't have this problem! (One lives in Texas, about 40 miles west of Dallas, the other lives in Idaho, sorry, no references there to be miles outside of, it's just a lot of empty.)

    If those two can handle working with the limitation, surely the other 800 million of us, you included sir, can do so as well.

    Bring back America's CAN DO attitude! No more of CAN DON'T. That doesn't even make sense! How can you "don't"??? Note, a CAN DO attitude does not apply to monopolies or big bullies. So do not judge MS, lest ye be judged! Oh, well, judged again?

    Remember the successful two who firmly believe that Apple does not have directories and structure hiding somewhere, and that Windows can work with everything in the single directory they call "root." Just put every little thing in the root directory and you can easily let SharePoint consume almost any number of characters and still save your file!

    It's so simple. I mean "simple" like in "otherwise mentally enabled" not like in "easy", of course, but still simple! No further need for "wishful thinking" or "improvements"!

    Roy supported this idea  · 
  2. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    Keyboard insertion already exists.

    You use Ctrl-+ (hold Control and press the "+" key).

    It works as appropriate:

    1. Highlight a row and do it, and a row is inserted immediately.
    2. Highlight a column and do it, and a column is inserted immediately.
    3. Just have a cell selected and do it, and the dialogue box asking right/left, up/down that one gets using the mouse pops up and you go from there.

    However, "insertion" is taken to mean ABOVE or to the LEFT of the selected item, rather than how you describe.

  3. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Excel for Windows (Desktop Application) » Other  ·  Flag idea as inappropriate…  ·  Admin →
    Roy commented  · 

    Actually, you grab the title bar itself, same as any other compliant program and move it to wherever you wish. If you want it maximized, just take it by the title bar and bang it against the top of the screen.

    Lucky you... you're getting your wish a million years sooner than the, say, "each spreadsheet has its own Undo stack" people!

  4. 2 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  5. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy shared this idea  · 
  6. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    The problem with getting data OUT of a PDF is that a PDF is more like a program than a simple document.

    Worse, anyone creating a PDF can take a bajillion different paths to creating it than someone else does. All that matters is that the PDF displayed result looks like the original source would have.

    So Excel, or any other program MAKING a PDF is simplicity itself (for thems whats knows how to), as they have easily accessed data and full control over how to handle it. But going the other way with the huge variations in how their programmers chose to create them is a WHOLE other story.

    Wish I could, but I can't ding Excel for this one.

    By the way, if the need is to just sometimes take a chunk of data out as it appears on the screen, rahter than the bizarre way it sometime is connected and split up, try pressing Ctrl, holding it and marking a rectangular block with the mouse. Leave some extra white space, as you are able. Then copy it to the clipboard with formatting.

    It will try to OCR it and will make it a pastable block, like it appears on screen. I find it... 85-90% effective. The loss is never in the block chosen, just the OCR result. So say you want a table and highlighting it you get some of it highlighted but find yourself getting text from five pages later too and it's all over the map as well, this is very effective at solving things for you.

  7. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    Same, and almost the same (1911.etc.)

  8. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  9. 118 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  10. 18 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    True, and my point said that was the reason for having to use wildcards with COUNTIF(). I'm not precisely arguing clever usages don't exist to solve each and every roadblock, but who needs that? One needs routine, and direct solutions to the general problems faced fairly often. This is one reason many perfectly clever folk decide they have other things they must do than learn some of the arcane usages of some functions. You know, "it's searching for a 2 and will never find one so it picks the last thing not a 2 and you have contents of the last cell used in a row" kind or arcane.

    IFF (not a typo, if and only if) regular expressions became a part of non-VBA Excel, fully implemented, I wouldn't care much about this. But they aren't now and seriously, won't be in my useful lifetime. So I, and clearly, at least 17 others, find a straightforward function, one that ought've been around for a very long time if COUNTIF() is the wonderful workaround for the need, to be a desirable thing.

    Clearly we do not make the impact the Python folk do (and should). So we shall not have it. Not gonna stop us from wishing and trying to be constructive about it.

    Pertaining to that, I do love knowing the basics of a workaround ESPECIALLY one I never thought to use before. COUNTIF() could be the workaround I might use for the rest of my spreadsheet life and I thank you folks, you constructive folks who offer it to a fellow who never considered it before.

    But given I've experienced quite a few times needing this ability, it just cannot substitute for a straightforward function that does this thing and would in almost every instance I've needed it rather than just 80-90%.

    And I love to be clever with Excel, while being practical about it. I used to say computers could not win the world until they were essentially toasters: you're concerned greatly sometimes about the item dropped into the toaster, but other than handling your choices, you don't care the least about the toaster itself. It is simply to work and never really involve you in that process. (Not Apple's we will dictate to you for megadollars approach, but not DOS and ?Windows' concerns about interrupts and COM port addresses you can use (a couple out of thousands possible, what was up with that?) either. Now they are toasters.

    Rightly or wrongly (wrongly, but it's their choice, the USSR lost) many people regard Excel that way. They don't wanna know a thing. How they expect to use it, I wish I didn't know. Remember when the guy who could do anything was a guru and valued? Now he's a must for any averagely functioning office. Remember when the only thing holding him back was geekhood? Now it''s that he does everyone else's work and gets no credit, rather gets dinged for his own suffering (even if only in quantity not its quality). I'm tired of being that guy and want more straightforward, but meaningful functions than just this one. Something even someone too good to learn much about Excel looks stupid for not being able to use. Those channel changers don't need to have me adjust their vertical roll (oh, and can you change it to channel 43 while you're at it?) if TV screens no longer can roll.

    Selfish? D*mn skippy, and I admit it. I want it for my own use. I want it and other things like it to move the folks above along on their own, surprisingly to them, two feet. I don't sit here wishing for workarounds, especially odd ones, arcane ones, to be the only solutions.

    And I might point out, when is the last time the average person used a statistical function in Excel? Average person. There's a constituency and a need, but not for the average person. Sine, Cosine? Complex numbers? Heck, I don't even try to work them into the quadratic sheet I give people, I just craft them in it when needed as strings and tear them apart to reuse the result if I have to. Any one using the sheet has too as well, eh? It's harder to mix complex and non-complex numbers in something learners are going to use.

    But no one roars that those things should therefore be removed, just because the constituencies are small, comparitively. Yet that's often the battle cry about things like this function idea... "Who needs it? NO ONE! When do they need it? NEVER! Then it shouldn't be added or even desired! QED, go away boiy, you're bothering me."

    Again, thank you for constructively offering something I'm sure I WILL use, even though I'd usuaully prefer this.

    Roy commented  · 

    No, COUNTIF() returns nothing unless there's an exact match. So if searching to see if the substring "hors" is in a cell containing "horse", one MUST use wildcards. It rather stupidly cannot find a substring on its own, but instead matches an entirety vs. an entirety, hence the requirement for the wildcard usage.

    So the person Phil describes, and whom we all know, has to consider wildcards before the string, wildcards after the string, and who knows, maybe wildcards all around volleying and thundering. But they don't ride into the Valley of Death, they call Phil.

    Not to mention how allowing for these possiblities affect the (should be) simple task of writing the sub-formula that might be the input of the COUNTIF() in a more complicated situation, especially not accounting for the "helpful" things people might do to aid you.

    Just need a simple function that does something at least one of MS's FIND() andSEARCH() functions should have done from the start. But no, they're essentially identical, right down to taking a start number rather than an instance number. Why couldn't one of them do each? But that's a different Suggestion I suppose. 'Tis a granular world. Identical grains, it seems, no variety in this nature preserve.

    But no, COUNTIF() is not really simple for many places the idea might be used. Not at all.

    Roy commented  · 

    @Anonymous: As applies to this Suggestion? Nothing is "wrong" with FIND(). Don't get me started on FIND() in general though.

    However, it:

    a) Will not take a range which one would hope the proposed function here WOULD do. Nor does SEARCH() take a range, so that's not on either.
    b) To use it in the manner you mention requires IF() handling, actually, WITH IFERROR() to handle both sides of its result.

    I know I do, and I assume the other voters would, prefer both of these things taken care of. If it could take a range, that'd solve a) and it would, in either case, provide a clear result to use without IFERROR() at least.

    Remember one thing about this "forum": it is about what we'd like, not about continuing to think any workaround, no matter how simple or convoluted, is preferrable.

    And, as Phil mentions, consider the many users with a more limited skill set in Excel. (Yes, FIND() is pretty basic, but No, non-straightforward uses involving it (like wrapping it in IF() and/or IFERROR() to aschieve what seems like a simple goal are not basic to them.) Or people for whom Excel is A tool and nowhere near their most important one, and for whom nothing is going to change that. Yes, they can kludge something together on the table saw using the miter gauge, but just want to go over to the radial arm saw and cut the thing. Forever. Yes, that's too bad for them, mostly, but this is where they, or others on their behalf, wish for their needs.

    And my own. I cannot use FIND() on a range and that will NEVER change, so my hope lies with a new function that can be "right" from the start. Because you may have noticed, functions never, ever change like this because of backwards capablity concerns. Even though MS could put the new functionality into arguments that come after current arguments sp that formulas written 30 years agp would not be affected.

    In fact, I would hope that applied to a range, it would return the cell along with the location in the cell, in a form that could be directly (not INDIRECT()-ly... so not returned as text for the cell reference...) passed into other functions in a formula. Perhaps as an argument that indicated which to return.

    This forum is our hope chest, not our nope chest.

    Roy commented  · 

    Or check length vs. length after substituting "" for the string.

    Sigh... they know the problem enough to work out a formal suggestion for a workaround, but don't just simply program the function.

    It could even just be a "last parameter" option in a function like FIND() (as in FIND("xx",a1,,"contains") or just true/false).

    You wouldn't believe the workaround I have to use for our shop software reports that come out simulating their look on printed page instead of columnar Excel files, then have to be converted (there is NO PDF conversion software that really works reliably). Just a simple CONTAINS() applied to 6-10 possible cells that might contain the string and associated data would simplify things incredibly.

    Roy supported this idea  · 
  11. 9 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    No, not cell comments.

    Also, as an aside, can people get OFF this drone about volatile functions? Most spreadsheets are simply so small that it cannot matter in the least on a modern computer. (Not a poke at you Harlan Grove, just I hate seeing that endlessly, as if everyone writes only spreadsheet with 4,000,000 full column reference array formulas using 2-50 volatile functions in each one. These same people (on tip sites) then often go on to use equally rabid resource eating techniques, creating their own volatility, but that's ok, right, 'cause it's THEIR clever thing? Color me TI-red on that subject.

    I know a BUNCH of in-cell commenting techniques. But look on this site alone at all the people who just simply find them obnoxious and fairly close to worthless. This would let you comment to your heart's content, even "real looking" tabbed over from the end of the line looking comments if one used Alt-Enter freely, and they all go away, just like compiling, with that simple SUBSTITUTE() wrapped around the formula.

    Though I have the feeling Excel's recent change from 3 lines in the formula editor to 5 lines presages some improvement on this to be announced in the relatively near future. Not "coming"... just announced. But someday. (They did not shout the 3 to now 5 line change all over the site or internet, so I think it is not to make life more pleasant for those who've been asking for literally that, but rather something larger.)

    Not to mention how a function like this could be used to take a piece from here and a piece from there. Now you can take whole resulting values as pieces, of course, but there are many times a function uses material in a different form internally to a formula, and cannot use a formal result from the same functions. Set this kind of piece up as text so it can be literally any form needed, and you could overcome how Excel doing the above sometimes shortcircuits because the form it needs to start out swiftly becomes unusable to the next portion, while the pieces you set up could handle that form change OUTSIDE of the final formula so the whole formula works rather than chokes. (By internal processing result, I mean the kind of things you see with F9, not things flowing in their programming itself. Like an array being created intermediately, but it isn't {1,2,3} but rather {"1","2","3"} and works, but stays so pushing further on when it simply won't so your clever idea fails for a truly SAD reason.)

    Roy commented  · 

    Good Lord, just realized this could make it possible to create coimmented formulas in an almost natural way.

    You'd have to write the formula in a different cell, say on a hidden page/tab that follow the current page. Write it with comments in a multi-line format with the comments "wrapped" by a fairly unique character before and after. Then in the cell where you want the formula, use FORMULATEXT() (then string functions, if needed at all) to take the cell's contents minus anything between those unique characters but largely using SUBSTITUTE() to remove them (and probably TRIM() as well to clean it up a tad) then wrap the result with this function to turn the result into a working formula.

    Almost natural. For, as they used to say, some values of "almost."

    But since you can't really write commented formulas now and this would see you editing the commented version, not the workhourse version in the cell...

    (Probably you'd wriote the formula without commenting, copy and paste its content (F2, then copy it all) to its holding cell and break it into a multi-line format that you'd comment. Then return to the original cell and strip the comments and make it a working formula.

    So, natural commenting not funny bits stuck into "N("Insert comment here")" additions.

    Chandoo has a simple 4 line UDF to bring cell comments into a cell. One could use such, but preferrably, may Excel would bring a partner function into existence that would do it for you, then you could put these into a cell comment, not the hidden sheet thought. Extract them with the partner function then continue as above. Or, create a new kind of comment to link to a cell for this precise purpose.

    Roy commented  · 

    First time I've seen that annoying Search when typing the header line for an idea work.

    Absolutely would be HANDY.

    Using lookups to pick a formula to use, for instance. Usually a lookup searches for some value and returns some value related to it stored to its right in a table. Picture a table of values with formulas to use if one is the case. Lookup a value (or set of values) that conditions dictate and find to its right a formula to use.

    That would HUGELY reduce complexity based on nested IF()'s. One can do a version of this now, but it is greatly limited and according to answers provided to questions on the internet, it looks like no one really does, in practice.

    But say one has four conditions. The IF()'s will be nested four deep and use of IFERROR() is likely not possible even if it could have been useful. Maybe a couple of the conditions are more involved AND()-ing or OR()-ing possibilities. Instead of taking easily understood logic and forcing it into an arcane nested IF() structure with the formulas to use when the conditions resolve buiried in it, one could instead do a multiple condition lookup to match the conditions that exist that moment to a formula that is in plain sight in the formula table. Result achieved. Understanding possible when maintaining the spreadsheet over time. Yay.

    It would also be easily possible to create dynamic formulas as INDIRECT() and its weirdnesses would not have to be used. Rather, one could use simple string functions to build the formula. Much easier to do and much easier to maintain as anything would go whereas presently there are limits due to such things as INDIRECT() not being able to access a closed file*). This would create a completely live formula that could access anything the typed version of it could. (Hence my "as anything would go" statement.)

    * Yes, I know there are ways toi sometime force it to, using the Excel 4 macro command EVALUATE for example. But those ALL have severe limitations. For instance, XL4's EVALUATE requires a Named Range be created for it so it's a one-off solution. Have 648 formulas you need to do that with? Well, that's 648 Named Ranges...

    It could even be used inside formulas to add a better dynamic portion than currently available. Pieces could come from lookups, as above, or IF()'s, and make the larger formula it is part of easier to understand (therefore maintain).

    Roy supported this idea  · 
  12. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy shared this idea  · 
  13. 2 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  14. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    Or just END() with the four directions as parameters, spelled out.

    Roy supported this idea  · 
  15. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  16. 2 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  Excel for Windows (Desktop Application) » Other  ·  Flag idea as inappropriate…  ·  Admin →
    Roy supported this idea  · 
  17. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy supported this idea  · 
  18. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    You CAN do that. What you do is to Select All cells and format them ALL as unlocked/unprotected via the normal formatting dialogue box. Last of the tabs, make sure both the Locked and the Hidden checkboxes are unchecked.

    Then do the opposite for the cells you wish to have Locked: format them by checking the Locked checkbox. Leave all other cells alone.

    Now set the sheet protection and it will protect everything you checked Lock for, and nothing else. By the way, as formatting, it copies with a cell, so do it, enter a formula, then propogate the cell and its formula via pasting or filling and they too will be ready to be protected when you set the overall sheet protection.

    Yeah... that's pretty backwards feeling too, isn't it? Not only do you have to tell Excel to protect the cell, you have to tell it again and really insist this time...

    So..., it feels really backwards, really obtuse, but it works and is not a workaround, just a functionality operated from a backwards standpoint.

  19. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    It does. Scroll to the bottom of the Custom formats list and yours will be there along with any other Custom number formats you used in that spreadsheet. Ones you don't want there, maybe experiments perhaps, can be deleted so only the needed additions show.

    They are not then available from spreadsheet to spreadsheet, but creating 30 new ones a day and having to type it 30 times does not sound like the many "and over" instances you are talking about so it seems like this'd solve that.

    Be nice if that list of added Custom formats was kept outside the spreadsheets, in Options somewhere, so it could then be available to all spreadsheets, new or old.

    (I bet that's the source of the lack too: each added format gets a childish label like "34" which causes no problems so long as it's only in that spreadsheet and no other. Do a list outside the individual spreadsheets though and Excel could encounter on sheet where "34" is a bare time and another gets opened in which "34" was used for a 6-decimal number with "horse" added to the end. Cause no end of trouble. But they could overcome most of that if they did it like this but "jumped a level" on the childish labeling so they ensured uniquness and hence no conflicts.)

  20. 1 vote
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Roy commented  · 

    They already are decimals, from beginning of existence to deletion.

    When they LOOK like times, they are just being displayed as such.

    To see the decimal version, just format the cells to display in any number-type format you like. Or just General.

    Since they already are decimals, you can do math with them regardless of their displayed appearance. So 5:14 am + 11:13 am = 4:27 pm, whether displayed as times, or as decimals.

Feedback and Knowledge Base