Feedback by UserVoice

Roy

My feedback

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    @Tom: Not so. You would use the standard body of work to make the ranges specified dynamic. My example was just a quick version to show the pattern. You could also add more than two arrays together even though I only show two.

    Standard stuff, How-To's available in many places, for the creation of dynamic array range addresses.

    More importantly perhaps, in a theoretical sense, is that you WOULD know the arrays or you couldn't put them together even handcrafting the addresses. Eh?

    Most of the techniques for making dynamically sized array addresses for use in range addressing also allow the values they are using to come from "outside" sources, like, for instance, someone directly typing some of it into particular cells, or selecting dropdowns, or a formula checking for certain information and passing whatever is then appropriate.

    A hassle, for sure, but one you'd have even with a direct purpose function. All of that, the dynamic array addresses, for example. So the principle applies and the method is valid, you just have to do the work that you are willing to put into it. It can croak out a tune or it can sing, depending on the effort YOU put into it because:

    It works.

    Yeah, my example would really only be useful if you could fit your tables of hundreds of thousands of rows and hundreds of columns (or whatever) into my two example 3x3 arrays. I agree, not very useful otherwise.

    Except... you know, if you put some work into it. Does XLOOKUP() work for you if you always type "lookup_value" instead of something like "L33" when the Intellisense prompt says "lookup_value"? Must not be very useful...

    An error occurred while saving the comment
    Roy commented  · 

    Got my vote.

    For the moment though, the following can combine arrays into one:

    =MID(TEXTJOIN(",",,A1:C3,D10:F12),SEQUENCE(6,3,1,2),1)

    and it is fully functional.

    Then one can pull out a VLOOKUP/XLOOKUP sort of return of a single cell with something like:

    =XLOOKUP(F10,J1:J6,H1:H6)

    or a row with:

    =XLOOKUP(F10,J1:J6,H1#)

    I imagine that can all be dressed up and/or combined. Also, if building references is a must, the ranges work fine when constructed with OFFSET().

    One can construct the array out of "pieces parts" too, a 3x3 overtop a 3x1 a 2x2, and a 1x2 for example. Then use XLOOKUP() which one cannot do directly with XLOOKUP(), apparently.

    MID() can really sing with the Spill funcitonality. Not limited to sets of 1 character each cell either.

    But why have we never been able to naturally join arrays? I hate to burst anybubbles at MS, but matrices can be constructed with matrices as elements themselves... or, "and so on"... but we can't do that here.

    Though obviously, creating ad hoc tables or combo tables would be a much larger use!

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    Nevermind, I got it.

    Yeah, after all, if it is otherwise treated as one cell, any reference to any component ought to actually naturally return the single value even without a function.

    In the odd case that it is possible for it to become unmerged, or cycle back and forth, that'd become awkward as the component might become used for something unfortunate, but that oughtn't to be often, eh? And they could write things so that the unmerging process would/could change those references to the hallmark cell, A1 in this example.

    Roy supported this idea  · 
    An error occurred while saving the comment
    Roy commented  · 

    What — precisely — do you mean by "merged cells"?

    My guesses don't make sense with the rest of your text.

    It sounds like something I'd like to vote for, but I have to understand it first.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    Here is the link to their website:

    MyOnlineTrainingHub.com

    Cool folks, not pushy but like to send emails if you sign up for that. Some have been useful.

    Just search for the Excel 4 Macro manual and you should be good. Or do a Google search as they show up in a lot of the links found for the search.

    Roy supported this idea  · 
    An error occurred while saving the comment
    Roy commented  · 

    This functionality is available through the old Excel 4 macros (you would use "GET.CELL") for a humungous number of formatting possibilities including:

    "The horizontal distance, measured in points, from the left edge of the active window to the left edge of the cell. May be a negative number if the window is scrolled beyond the cell."

    AMAZINGLY enough!

    You must use these function IN A NAMED RANGE, not just type them in the cell.

    Also, you will read that MS says gosh, they may go away any time. But it has been saying that since 1992? 1994? There is some decent evidence they cannot make them go away since things they do require this portion of the programming. As well as they have shown a huge resistance to changing portions of the program that are that old.

    But that leads to a different problem that has direct bearing on your Suggestion:

    Functionality that exists in the Excel 4 macros is functionality they have been incredibly resistant to "duplicating" in the main program. Getting the text of a formula was a cry, a clamor, a downright begging proposition, for two decades and longer, but as it existed (in GET.CELL, actually) already, MS seemed to see no reason to work on it until the finally gave in after two decades.

    So, since it exists already, MS may see no reason to move the functionality into the main program.

    GET.CELL, by the way, can do a stunning number of things for you. Easy to use.

    If you are interested, there's a training website that makes a copy of the Excel 4 Macro "manual" available. Not advertising them so I won't comment the link here, unless someone comments that they'd find it useful.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    Wow. It USED TO persist, but somewhere along the way, it stopped that. (Sigh...) As you can maybe tell, I prefer the default and only use it now and then.

    Oh, also, as one would expect, this is available in Find and Replace, as well as Find, since they use the same functionality.

    An error occurred while saving the comment
    Roy commented  · 

    The default setting for Excel does exactly what you mention. It looks only at the "formulas" (as it puts it but really it is looking at any text ("constant") entry as well as the individual parts of actual formulas). It does NOT look at their resulting values.

    However, there IS an easy to locate when you realize where it is setting that will do just what you want. It will persist as well, as long as the Excel session is open, but each time you start Excel, you will need to remember to change it.

    When the Find and Replace box is open, the lower right has a button labelled "Options" and you want to click that. The box enlarges and there is new material in the lower left corner. Three dropdown choice things and two checkboxes.

    You want the lowest dropdown selector, and may note it currently says "Formulas", but click it and select "Values" from the choices.

    That's it, now it will find formula results that match your search criteria as well.

    One related item: the second of the two checkboxes. By default unchecked, it allows any part of a cell to trigger a result, checked it requires the entire contents to match your search string.

    In the unchecked condition, searching with the default "Formulas" will find an entry of "1000" AND an entry of "=1000". If you check the box, it will NOT return "=1000" because the "=" is not part of your search string. So you can distinguish between those kinds of contents, if you ever want to.

  5. 196 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    22 comments  ·  Excel for the web » Formatting  ·  Flag idea as inappropriate…  ·  Admin →
    Roy supported this idea  · 
  6. 730 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Great suggestion – thanks again for taking the time to put it on this site and for the thoughtful followup comments. This is pretty related to some other work we’ve got going and already has a fair number of votes, so we’ll work on getting plans in place now and hope to get started on this soon.

    Thanks,
    John [MS XL]

    An error occurred while saving the comment
    Roy commented  · 

    A new thought on the value of a true null available to formulas...

    That ignorant SPILL error. One doesn't always want a 12,000 element result to be fully displayed... Criminy.

    One might just want the single "head of the beast" displayed so people know where it is, or perhaps 10 or 20 cells of it. One might place a blocking formula to end that display.

    The idea being that the formula would work as intended, display what it can, put a little green triangle in the corner of the cell, and let us move on. I just created a SPILL formula that could fill 12,000 cells, or 37... do you think I didn't notice it didn't? Geez...

    So... two types of blocking cells to consider:

    1) Cells that, unfortunately, are just in the way. One COULD place an activator cell by the SPILL formula, like "Type "Show" to see full list." and if a person types "Show" (or, say, anything at all, just not an empty cell there anymore), then all the identified blocking cells could have been noted and fixed with an IF() wrapped around them that gives this NULL result and the SPILL formula would no longer be blocked, instead would fill there and right on past.

    One can dress up cells around that which would distract with the hoary IF() with a "" result, but NOT those in the way of a SPILL formula. But a TRUE NULL could.

    2) The other kind could be the opposite, not legacy or just awkward material but rather intentionally placed to limit the SPILL. Say I want the first 14 of those 12,000 cells to show. Or 33, Or however many someone types in a nearby cell for the purpose. Perhaps I place formulas below (or to the right, whatever is appropriate) which watch that cell with an IF() that results in perhaps a blank (just the formula with the "" result), thereby stopping the SPILL SPEW™, but that can shift if an appropriate entry is made in the activator (um, deactivator I guess) cell, shift to that true NULL so now the results can SPILL onward.

    Various ways to implement that, from a few hundred cells under it with the formula, and X-number of them NULL out letting the display SPILL into them if "X" (value X) is typed in the cell, to slick ones. Even brutal approaches like 12,000 formulas and you go down to the row you want things to end at and type an "X" one column over. Slick or brutal though, the point is, when the condition is met, a given cell would be treated as a true NULL by Excel so suddenly the number of the results that are displayed would increase (or decrease back when cleared).

    After all, by the way, MS stresses that the only place with an actual formula is THE cell the formula was entered in. The rest is just display. Hmm... even Conditional Formatting could have helped here if they'd only asked me first. But no, they had to pretend I didn't exist and just bull their way through...

    But yeah, a true NULL could allow formulas that give a benefit by acting for all that Excel's engine knows, as if they didn't exist in a given cell. And that'd be handy with these SPILL functions.

    An error occurred while saving the comment
    Roy commented  · 

    A null value would be different than the current blank.

    "Blank" being what you get if you, for example:

    1) Enter ="" in A1, the copy and paste values back on it, or to some different cell
    2) Have a formula in A1 that returns "" as its result.
    3) Have some value in A1, then select it and hit Delete or Clear Contents

    and some other things.

    Anyway, Excel is currently able to distinguish between a cell that has a history but is currently blank in the English language sense of blank: literally nothing entered in the cell, and those cells which have never, ever, had a value in them. So it should currently have no issues distinguishing, in program, between types of "blank" or "empty" it encounters.

    Alright, you have A1 which has never had an entry, ever. Presently, "=A1" would return a 0 in whatever cell or formula it is in. "=ISBLANK(A1)" would return TRUE. Put "=F10" into A1 and now A1 would return 0, =A1 would return 0, but ISBLANK(A1) would return FALSE (after all, there's a formula there now). References to any of these cells would produce currently expectable results.

    Now delete the entry in A1 and you're back to the original. However, Excel can still see that though it is blank, it has been used and will act accordingly, saving information for it and doing things like sorting it into a different place than it would if it had never been used. (Ever delete the last 10,000 rows of a data set and find Ctrl-End still sends you to the last cell in them? That is Excel still realizing they've been used. Just blank, not empty.)

    Now close all that and open a new spreadsheet, then put the formula I mentioned in B1 (assuming a NULL() function now exists). Excel would return a truly empty cell result in B1, not a zero. That result would be precisely what is thinks about A1 itself since we have never entered anything there and it's really empty. So =A1=B1 would return TRUE as the value of the never used A1 and the NULL() function's result of a true null is the very same thing. If you enter =B1 in D1, D1's result would be a true null (as in "empty, never used") as well. So "empty" from your choice of that or zero.

    Notice the effect of the formula was to pass onward a null value for B1 even though it has a formula in it. Of course, there are functions in Excel, like FORMULATEXT(), that need to return the underlying material, but they already are programmed to ignore the result and evaluate the underlying material. A Table would also have to look at it this way, else one might have odd effects if a formula in the first cell of a row looked utterly unused to the Table. Or maybe not. But whacky things like that could crop up with issues. Don't mind a wait while things like that are gotten right. DO mind a wait if it's just "you're only 671 people out of our 800,000,000 person user base so..."

    An error occurred while saving the comment
    Roy commented  · 

    Well, first off, the current situation has sucked for 30 years. I'm completely willing to spend the next wrapping "=A1" to get "=IF(ISBLANK(A1),NULL(),A1) and have a result that Excel treats as it does never used cells. NOT A PROBLEM dude.

    Second, Most of us asking for this almost certainly do NOT have any misunderstanding about the return and therefore how we'd have to use it.

    Third, part and parcel of any real solution would be for MS to treat any blank as it treats never used cells. Presumably, it stores only a cell address and value (in a simplified version of things) and would simply have to not store such if blank. "Blank" = empty: either never used, or contents deleted or cleared. "0" would need to be treated as an actual value, not all the absences of a value as well as an actual entry of "0". Since what's stored likely includes more information than I imagine, things used internally, not just formatting and values and such, the simple version is unlikely and Excel would have to make provision for a value of NULL and handle it however works best for them. Then deleting/clearing contents would set that value, not just leave no value at all.

    But fourth, we'll all of us be pretty happy if they just work out the FUNCTION asked for rather than a fuller solution. Nice if they did the whole enchilada but if we could just have the function and Excel acted properly (or at least "as expected") on its use, we'd be really happy.

    "Properly" ("as expected") would mean things like sort a set of data and NULLs appear at the bottom of the sort result just like never used cells would rather than as a separate grouping, above those.

    Also, considering sorting, it is clear Excel can already distinguish between current NULLs (never used cells) and blanks, but also it can clearly already distinguish between 0's and blanks as well. So since it can already distinguish all three "values" and so MIGHT be able to already do the underlying work the function would need.

    No one's even asking for perfection, just better, by this much.

    An error occurred while saving the comment
    Roy commented  · 

    Hey guys, take heart. Even though they've had 3½ years to have a change of their own hearts, they still consider it PLANNED... no downgrade!

    What's the gift type for four year anniversaries again?

    An error occurred while saving the comment
    Roy commented  · 

    Especially to Lori, but in general:

    The USE CASE is needing a cell without data to BE a cell without data to EVERYTHING.

    Until that is the case, nothing has been solved.

    And if you want to use the result of "nothing" as valuable data to what you've written, then don't add the NULL() wrap.

    Or... with luck, the null would be a complete, true, default in all cases blank and you would set your flag to have it be non-blank... sigh... no, that last would break tens of millions of in use spreadsheets. But the rest of us deserve this and future spreadsheets could use it instead of old workarounds thereby spreading world peace and allowing all children to go to bed by 9:30pm so the beauty contestant queens could finally be happy and choose other answers. This would benefit the world!

    An error occurred while saving the comment
    Roy commented  · 

    @Lori:

    He wants a FUNCTION(), something we can wrap other functions in and produce a NULL if the output of the wrapped formula is "" (with perhaps an option in the NULL() function itself to do so if it is "0" also?), OR to give as an element of something like the IF() function.

    NOT looking for a general underlying change to Excel so that all such give a true null as the default. Everyone understands a 30 year base cannot permit that kind of approach.

    Actually, a middle between the nice first part and the rather extreme general default might be a page option (why can't "pages" be generally formatted directly rather than by selecting all first?) that works like the display 0's option. Then charts could act upon the data as displayed (blanks treated as nulls, if so selected), regardless of the literal cell non-content. Or not. An option in setting up the chart.

    (That could also be done solely in the chart's options, but as a page display option, it could let Excel treat non-content cells as nulls WITHOUT having to wrap results in a NULL() function. If one cannot use that due to 30 year base constraints, one simply wouldn't.)

    An error occurred while saving the comment
    Roy commented  · 

    @Lori:

    How would this break anything for you? YOU simply would not use it. The rest of us would.

    Instead, you'd have the rest of us contort ourselves, making UDF's and having to overcome VBA-resistance in many places we send spreadsheets to? Making poor folk doing charts continue to use workarounds for the reason that you'd have to use it because it existed instead of simply... not using it yourself?

    I am clearly missing the logic here.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    @shan:

    Fully understandable on their part mostly, but not really here as this is an actual MS product from a purchase they made nine years ago. And conceptually, tools FROM MS that help make spreadsheets MORE reliable... well, they ought to fall all over those!

    Spreadsheet reliability and accuracy are big issues these days.

    Could be worth asking. They may say no, but that's probably the worst they can do, right?

    An error occurred while saving the comment
    Roy commented  · 

    @shan:

    MS has a web page showing how to activate their addin and a minor amount of info about how to use it at:

    https://support.office.com/en-us/article/analyze-a-workbook-with-spreadsheet-inquire-5991e8fa-f1c1-401a-ae3f-469384ae3e3b

    They have much more info about using it at:

    https://support.microsoft.com/en-ie/office/what-you-can-do-with-spreadsheet-inquire-ebaf3d62-2af5-4cb1-af7d-e958cc5fad42

    (Both of those are MS sites, in the Support.Microsoft.com family.)

    UNFORTUNATELY...

    This was an addin offered around the time Excel 2013 was current and somewhen along the way, Excel removed the addin from downloading.

    On the following website, MS says it is still available for "...Office Professional Plus and Microsoft 365 Apps for enterprise editions." And that even if you have access in theory, IT people where you work may have removed access to it:

    https://support.microsoft.com/en-gb/office/turn-on-the-inquire-add-in-6bc668e2-f3c6-4729-8ce1-75ea20aa9d90?ui=en-us&rs=en-gb&ad=gb

    So...

    If you follow directions on adding it and it is not showing in the dialog box AND you have a suitably (expensive) option filled (expensive) version of Excel (expensive), you might ask your IT people if they have removed access to it and if that was intentional or just a careless setting of a setting that they can fix.

    Failing that, a web search might locate someone with a copy available for download. Be sure to look for a copy with dates more recent than Excel 2013-time just in case it was originally pulled (for release of Excel 2016) due to problems interacting with the update. The version available (for rich people and monopolists) would presumably fix anything like that if anything like that even ever existed.

    But barring those two things, this is not for the hoi poloi.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    Shift - Shortcut Icon works too.

    But such things would leave a person shy here as Chris Newman needs the file open in the original so could not open it in the second/third/twentieth instance.

    However, one can open multiple windows for a single file. I have not tested this with PQ so conceivably one could open 30 windows on the file and they all get Bogarted by PQ so none of them can be seen. Hopefully not. Since they should themselves also be SDI (yuck) windows, one should be able to size things on a single monitor or drag the "see" window to a second monitor while using PQ in the original one on the first monitor and then see both views, PQ and the file. Interactive too, maybe, and likely nicer than screenshots though whether it is better than screenshots I cannot say.

    The multiple instance of Excel thing used to be really useful now and then. Open three files with the same name, different directories? Use multiple instances. Couple other nice things. But since only one application can have a given file open at a time and SDI makes Excel and Windows see the separate instances as utterly foreign to each other programs, it seems unlikely it would let the second one open the file too.

    Maybe if the file were set for sharing. Excel shouldn't care that the others are all "on the same computer" as they are just fully separate instances on the same computer.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    The following can find a desired instance number in a delimted data cell:

    =MID(SplitSource,SMALL(IFERROR(SEQUENCE(1,LEN(SplitSource))/IF(MID(SplitSource,SEQUENCE(1,LEN(SplitSource)),1)=SplitDelimiter,1,0),""),SplitDesiredElement-1)+1,SMALL(IFERROR(SEQUENCE(1,LEN(SplitSource))/IF(MID(SplitSource,SEQUENCE(1,LEN(SplitSource)),1)="$",1,0),""),SplitDesiredElement)-SMALL(IFERROR(SEQUENCE(1,LEN(SplitSource))/IF(MID(SplitSource,SEQUENCE(1,LEN(SplitSource)),1)=SplitDelimiter,1,0),""),SplitDesiredElement-1)-1)

    It just looks brutal. Lots of repetitive stuff.

    One could add error checking to make sure the SplitDesiredElement value does not exceed the count of elements.

    Since the elements are simple and repeated a time or two, it lends itself to simplification of appearance via Named Ranges yet would still be easy to maintain.

    I suppose ways around the problem have existed for years. I just don't know them.

    It has two basic components, to find the n-th (desired) delimeter, then subtract 1 for the one preceding it, then to return what's in between. Could be altered to allow more than one segment to be returned. 1st, last, any particular one, a count, all can be achieved via COUNT() acting upon the element inside IFERROR().

    It would fail if a delimiter were the first character, and if there is more than one contiguous delimiter (like in "abc $$ def$gh" if the "$" were the delimiter).

    The internal array produced is of the positions of the delimiter so it can feed directly into anything working off position. A multi-character delimiter is possible if one uses LEN(SplitDelimiter) for the early "+1" and very end's "-1" as well as the length of return for the first and last MID()'s but not the middle one.

    Anyway, possibilities seem to exist in it. Someone usefully simplifying it would be handy. Until MS programs in a proper function!

    Roy supported this idea  · 
  10. 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  · 
    An error occurred while saving the comment
    Roy commented  · 

    While you are waiting, you can force the in-cell behavior to work out by adding a wee bit:

    =MIN((ROW()=ROW())*1)

    The TRUE is returned in the Row()=Row() parentheses, then the *1 forces it from TRUE to a 1 and MIN() can act on it properly inside the cell while you do each part with F9. Since Excel eeks out the right behavior when calculating on its own, it might not be of too much interest. But it is useful for the same purpose elsewhere, and even could be helpful for this kind of looking into a formula for where it fails.

    By the way, Excel does this a LOT with its interim results in a formula. Returning an internal array like {"1","2"} instead of the expected {1,2} can dynamite a formula when Excel does not, as it does with this one, figure out how to get the right result from those internal strangenesses.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    You can use:

    =TRANSPOSE(SEQUENCE(4,3,1,1))

    to generate the exact thing you use as an example, and modify it for similar production.

    The only trick here is since you are having to use TRANPOSE() (because SEQUENCE() seems to want to go across, then down instead of down, then over a column, etc.), you have to do "columns, rows" in your SEQUENCE() function instead of the Intellisense help nudge for "rows, columns"...

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    Alright, so the trick for constructing a partial table was eluding me, but not anymore!

    Stuck on using FILTER() which I just plain like a lot, but it has the problem of taking the "upper, leftmost" address and "lower, rightmost" address from any and all given when trying to set up just some columns and dynamites you by using the full range from the one to the other including ALL columns in its return. Then you have to restrict which ones some other way.

    INDEX() is that way. You can specify the oclumns via the headers which you have at least five ways to do. Putting the work in Named Ranges and using something like:

    =INDEX(Table,SEQUENCE(ROWS(Table),1),AllowedColumns)

    will limit your ClerkTable to only the columns in the AllowedColumns name and that can be as simple as:

    ={1,3,4,5,7,8}

    if you then use that to get the "real" header names. No problem doing that either.

    Then FILTER() can be used to return just the set of records desired. It allows more than one condition for inclusion and they can be set up as an OR instead of an AND so you wouldn't have to do some all people in a city return to get Jim and Abdul records in the same return table. This allows setup of reports as well as working with actual data (add/edit/delete type actions via controlled forms).

    I say "then" but it seems likely the easier route, computer work-wise, is to do the FILTER() on the source data, then INDEX() on that rather than the other way around.

    Not great for security though as one could try a couple ways to draw excess information. Using INDEX() first means the excess simply isn't there to be drawn out. Then FILTER() for the work.

    In any case, it all gives you a full table return if desired. 2-D, naturally. Andone can use any desired tool upon it, VLOOKUP() included.

    To minimize file size and keep the data from being included in any version of the worker's file that could be taken on removable media or emailed, etc, one could write several macros that, for instance, create and populate the work table on a hidden sheet when the file is opened, and delete it before any save can be performed. So the data only exists while in use and never when being saved. Smaller file size, very small being very possible, and no data transferrable since it never exists anywhere accessible to the user.

    An error occurred while saving the comment
    Roy commented  · 

    By the way, if data security is important, you can have your actual table of data, then use FILTER() to create a Spill Table™ wherever convenient, protect them both (hide, protect, etc., use FORMS for data adds, edits, and deletions). The Spill Table™ would be used for the USER's Spill Table™.

    The data could never then be touched by a user if the user did not have permissions for alterations via your Forms. It might be hard to limit what a user sees with standard table layouts, but if you add columns that store various data your Forms save with each record creation or alteration, one could be able to simulate various permission schemes.

    The user's Spill Table™ would be like traditional filtering but without the current hassles that presents as one filter changfe affects everyone (presently). So even just for that point, the idea has use.

    The user's Spill Table™ can be sorted via SORT() according to his taste and needs. It can be further limited by a slicer-like concept for each column.

    Your forms could verify the record asked for is present in the user's Spill Table™ so that would cut off a huge way of accessing records they have no rights to.

    One problem with this simple version is that FILTER() seems to return ALL columns for each record returned. One can get past that by creating the intermediary Spill Table™ by using FILTER() column by column for the columns of the source table one wishes to present, then use FILTER() on that intermediary Spill Table™ in the manner described above. It's just the formation of the intermediary Spill Table™ that is different.

    One could obviously have various intermediary Spill Tables™ to serve a variety of users and needs.

    But yeah, it does seem careless of them to have not allowed for 2-D spilling.

    An error occurred while saving the comment
    Roy commented  · 

    If you are looking to recreate an entire table elsewhere,

    = whole_table_range

    will do it. (For "whole_table_range" enter whatever you use to reference the table, cell addresses, a Named Range, a Table name, whatever.)

    (If you prefer a formula to a simple range, then

    =FILTER( whole_table_range, whole_table_range=whole_table_range )

    will do it.)

    If you want to do a lookup and return ALL the potential matches in the order they appear in the source data, rather than the entire table as above, then

    =FILTER( whole_table_range, whole_table_range=your_lookup_value )

    will do just that.

    So either the whole 2-D table, or a 2-D table of lookup results, are easily placed elsewhere without copy and paste.

  13. 327 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Thanks for the suggestion Jessica. We’ve got a few other suggestions on the site that are similar with scientific notation, etc. We’ll take a look at this area, and pay special attention to any cases that get a lot of votes. So please keep the votes coming to help us do a good job at prioritizing asks like this!

    Cheers,
    John [MS XL]

    An error occurred while saving the comment
    Roy commented  · 

    The problem is that MS looks at these and says " 'November 27, 1908' votes... WTF??? Don't pay attention to fools like that. They wanna someday not be childlike and really vote so they can be counted, then we'll pay attention."

    Welcome to the "November 27, 1908" of us, will lassek.

    An error occurred while saving the comment
    Roy commented  · 

    They ARE paying attention to this forum: note the new text appearance in the comment creation box. (Not text handling, just the font is different.)

    Of course, it could just be an unexpected jacking by a silent software upgrade doing a "no by your leave" major change via a minor little thing to them, you know, I hear SOME renegade, thoughtless software companies do that...

    Unfortunately, updating this yet NOT updating even their comments on the subject/s, suggests a LOT of neglect on the "producing" end of the concept. "Oh, we'll collect a lot of input, so you don't go anywhere, but you know, we won't really do anything with it... you understand don't you?"

    The stunning result in Pavlov's work was the part where, once an animal was conditioned, how many thousands of times the stimulus would elicit the reaction even though no reward was EVER again provided. The rest of his work was interesting, sure, but that part was sttunning.

    Ding-ding, ding-a-ling, Master made a little comment 3½ years ago, don't go switching to Google...

    So, sadly, yes, other forms of pressure are likely needed and some might be more effective. I don't use social media myself, but most-a-y'all surely do, and those avenues might be helpful, if only to educate people about this forum and the idea of voting.

    Consider the 800,000,000 (estimated) users of Excel and the 280 (nope, not even one comma in that) people actually voting here... I'm sure MS looks at this as more of a suggestion source than any kind of urgent need, "gotta act and act now!" before the press gets wind of this, kind of forum.

    Even the Python suggestion with 5,000 votes and a survey conducted getting over 10,000 responses, all acknowledged in the Admin comment, only merits a "so we'll certainly think of this now and then" encouragement.

    I, for one, must encourage other avenues, even if they seem to be off-focus, because SOMETHING more is clearly needed.

    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 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  · 
    An error occurred while saving the comment
    Roy commented  · 

    It'd be a lot nicer though to be able to just do it directly instead of using these workarounds! So you have my vote either way.

    An error occurred while saving the comment
    Roy commented  · 

    I could be misunderstanding what you have in mind, but I shall bull through anyway...

    The usual technique for AND-ing is to multiply one array (adjusted to a 1-0 format) by another (also adjusted. So TRUE's times FALSE's or any of the other three combos producing an output array of 1's and 0's to multiply a data array by giving the final answer or input forward in your formula.

    The key to AND-ing is the multiplying. One or more FALSE's in the source/s will give a 0 (failed) while all TRUE's result in a 1 (matching the idea with AND).

    The similar idea for OR-ing is to ADD, not multiply. Then, even a single TRUE will ensure a non-zero result. Since Excel really looks at 0 as FALSE and ANYTHING else as TRUE rather than only 1 as TRUE and all other results leading to errors, 12 TRUES will result in 12 but it will still be seen as just TRUE in the end by Excel so the output (answer or feed forward) will be TRUE even if only 1 source item is TRUE. Which is the idea with OR.

    If that is not on point, disregard it! If it is, then the takeaway is:

    OR: use ADDing for the input arrays
    AND: use MULTIPLYing for the input arrays

  16. 4 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  · 
  17. 2 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    You CAN highlight text within a cell at present, have been able to do so for aa LONG time.

    Press F2 to edit in the formula editor and highlight the text you wish to format differently from the cell formatting. Right-click and edit to your heart's content. All the possible font formating is available.

    Make 2 letter 36pt while the others are 12pt. Bold some. Italicize. Superscipt and use strikethrough. Like Prince, go crazy, get nuts,

    For a pretty interesting effect, Format the cell for Horziontal: Distributed (Indent). Enter three words with spaces between them (or more words). Drag the border of the cell to allow them to all display on one line, not wrap. Then edit the last word, at least, just one letter will do, a color change is easiest. Watch how it suddenly jups to overflow the cell border. Well, stuff does that all the time, right? Nay. Put some text in the cell to its right, should curtail that spillage, eh? Again: NAY. It goes right overtop whatever's there! Been so for a while. MS. just never fixes it.

    Important part is, it can't happen without editing as you describe above, some (at least one letter in this case) of the material in the cell, formatting it differently from the rest.

    (Oh, now stretch the width some more and watch the spillage widen out.)

    I've used Excel for what you describe for 15 years.

    I will make a last note though: It does NOT work if you have a formula in the cell. Period. At all. So you can't edit text that appears in a formula, perhaps an error message, in this manner. You can achieve some effect with conditional formatting but it's still an all or nothing thing: no "STOP!" in red followed in the same error message with "Call Jim." in black.

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

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Roy commented  · 

    Also, for the more complicated ones, building your expression via concatenation (any of the various ways) can work wonders with things that you can find a pattern even in just parts.

    For instance, for the shown example, you might enter the following in a row of cells in the columns needed:

    ="A1*" & CHAR( 64+COLUMN() ) & "1"

    It will add 64, plus the column number (2 for column B) and get some value (66 in this case) CHAR(66) is "B" so it then "unions" the three elements (A1*, B, and 1) to give "A1*B1" (without the quotes, of course. Then a formula like TEXTJOIN() can combine all of those in the range you copied that to. It can even use "-" for a delimter to make this one even easier.

    Finally, you copy and paste the value of that result, then press F2 to edit in the cell, and copy the whole string. Go immediately to where you are forming your formula and paste it in.

    Prepare for that by starting with ' or just no "=" and "write" it in words (like ' prices*quantities - 4% of prices*quantities...). Then as you have finished building the component strings, paste the in replacing the word version of what they are, so in my example here, you'd paste something in place of "prices*quantites" in two places. Use spaces between things to make them easier to separate in your mind as you do this.

    Things like my example's "4% of", I'd actually suggest writing "properly" (finished version (" *.04 " or " *4% " (yes, the second one works and is more obvious when maintaining the formula over time)).

    And some bits would be MORE work trying to do this with. Just do the main parts, and when it looks like the rest would be easier to simply type in, simply type them in. Finally, remove the ' or add the "=", whatever you did to keep it from being a formula as you worked on it.

    Hopefully all was done as needed, went well, and Excel does not claim an error exists!

    I've used this for some very long concatenation strings before TEXTJOIN() came along and hardly ever use it now. But if the problem is formed of patterned parts that are long enough to not just be simply typed, this can be a dream.

    But algebra first!

    An error occurred while saving the comment
    Roy commented  · 

    Well, the EXACT formula you have shown can be manipulated algebraicly to be:

    =A1 * [B2 - (SUM(C1:AP1)]

    Of course, it might be a simple representation of what you mean and not a much more complex example of what you really face, but one should always remember algebra is your friend even in Excel.

    When appropriate... it won't help much with the average VLOOKUP() or any at all with TODAY().

  19. 4 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  · 
  20. 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  · 

Feedback and Knowledge Base