excel
44862 TopicsFILL lambda: Fill blank cells in Multiple direction (↓↑→←) across 2D arrays
Hey everyone!! Sharing something I've been putting together: a Lambda for filling blank cells across ranges, with full control over direction and proper 2D array support. =FILL(array, [directions]) Clean single-function interface. The [directions] argument takes: "d" -> Down (default when omitted) "u" -> Up "r" -> Right "l" -> Left Want to chain multiple fills? Just pass an array constant, each direction runs in sequence, output of one feeding into the next: =FILL(A1:D20, {"r","d"}) Super handy for tables with both row and column headers that got merged. Five Lambdas under the hood. Only FILL is meant to be called directly, the rest are internal, prefixed with Dep_ to make that clear: Name What it does FILL Call this one, handles all 4 directions Dep_Fill_D_or_U Vertical fill engine Dep_Fill_R_or_L Horizontal fill engine Dep_VMirror Flips row order -> enables fill-up Dep_HMirror Flips column order -> enables fill-left Blank cells and empty strings are treated the same way throughout. Drop any questions or feedback below, and if you find a use case for it, I'd love to hear about it! All functions available on my GitHub https://gist.github.com/Medohh2120/14697b32ff089aa559d26547a5bd82a99Views1like1CommentOverlapping times within one row
Hello, What I would like to do is identify if two time ranges within the same row overlap with each other. My ideal end goal would look like: Start 1 End 1 Client Start 2 End 2 Service Overlap? 8:55am 8:59am NAME 12:09pm 12:19pm SERVICE FALSE 10:01am 10:48am NAME 10:00am 11:10am SERVICE TRUE 10:17am 11:17am NAME 10:40am 10:58am SERVICE TRUE 10:30am 11:00am NAME 10:30am 11:14am SERVICE TRUE 10:50am 11:05am NAME 2:01pm 2:38pm SERVICE FALSE 10:50am 11:00am NAME 9:00am 10:05am SERVICE FALSE I have attempted methods like SUMPRODUCT to identify overlaps, but each one appears to work until I double check and find that it does not identify all overlapping times. For instance, these two rows will be listed as false, even though the times are overlapping. 9:59am 11:04am NAME 10:00am 10:45am SERVICE FALSE 9:58am 10:55am NAME 9:45am 10:25am SERVICE FALSE Thank you in advance for your help!Solved16Views0likes1CommentExcel - COUNTIF Function
Happy Friday! I am trying to use the COUNTIF Function on a worksheet that tracks how much liquid chlorine is used at 3 different water wells each month, each well having 2 chlorine cylinders. I'm not sure if my brain is fried from reading/watching all kinds of videos for the past 3 hours and trying to follow their instructions, but any time I put in a formula, I get a pop-up saying I've entered too many arguments. Even if I enter individual cells and use only the 3 that it highlights for me (more than 3 does not highlight). I've attached a screen shot of the worksheet (Figure 1) and have highlighted the area I'm trying to figure out. As you can see, it will be for multiple dates, on multiple lines, going through to the end of the month, with the total chlorine (CL2) tallied up on the right hand side. Chlorine tanks are changed out quite often and each tank begins at 150, depleting down to 0. Figure 2 shows a screen shot of what I'm getting when trying to enter the formula. Am I misunderstanding how the formula works or am I using the wrong formula all together? Figure 1 Figure 2Solved104Views0likes3CommentsWhat is this Madness? Weird Average and Sum results
What is this devil math? What am I missing? The darker green value and blue value should match. I checked for truncated decimal values, even rebuilt the sample to ensure nothing was weird about the original cells. This is not an insignificant difference for what I'm doing, and I don't understand why these aren't lining up. I'm sorry if I'm missing something simple, but please, help!112Views0likes3CommentsProfit/Loss per Day Calendar
Hi All, I am looking for formula for these 2 items. 1. Want total Profit/Loss per date in Calendar (E.g. Total Profit on 02 Feb 2022 was -90.65). 2. If Profit Cell to be Green, If Loss cell to be Red Link to Excel Sheet. https://1drv.ms/x/s!AlvQCyQGg78NgkNEsm2PYzAfJIEo?e=bF7ctc10KViews0likes15Comments"Clear Filter From" Option is Never Available
Once a column is filtered, the option to remove that filter just from that column is always greyed out in my Excel (Microsoft 365, v2604, Build 19929.20090). I have seen multiple references advising to remove protection, check for hidden columns, or ungroup workbooks. None of these apply, since I have also tested this with a new worksheet containing a single column, with nothing possibly hidden, and with no shared access. Here is a screen shot of what I see: Thanks to anyone who may have some advice on how to re-enable this feature.66Views0likes3CommentsDrop-down list
Good afternoon! Could you please tell me what I need to do to make the drop-down list in the visible part contain more rows? I have a list of 15 rows. I need all of them to be displayed in the visible part of the drop-down list window, without scrolling the listSolved149Views2likes7CommentsSpreadsheet corrupts upon saving it
After reaching out to MS Support regarding this, they suggested I post my issue over here. Starting this week, a spreadsheet that I've been using for years is no longer working. When I launched it yesterday, it opened without issue as it usually would, I was able to make some edits and save. But after opening it up again, it came up with the prompt: "We found a problem with some content. Do you want us to try to recover as much as we can?". After clicking on Yes, comes up with "Excel was able to open the file by repairing or removing the unreadable content. Removed Part: /xl/drawings/drawing1.xml part. (Drawing shape)". At this point, I restored a saved copy from backup (from around five days prior). Once again, file opened and was working fine, but only after saving it would it corrupt upon reopening. To make the issue even more strange, this same behavior is now retroactively occurring if I try to open a template version of this spreadsheet I had saved from 2024, or even as far as 2020. It's almost like an update was pushed which made objects used in this spreadsheet retroactively no longer viable and corrupted. This behavior is not occurring on new spreadsheets I create, nor other ones I tested that are completely different to this specific spreadsheet I'm referencing above; though I haven't tested every single spreadsheet on my system. I've tried repairing MS Office, I've tried saving as different extensions (i.e. from xlsm to xlsx), but no matter what I try, this spreadsheet and any version of it historically saved (and I have years worth of copies) are now all exhibiting this error if I open and save them. Thoughts?102Views1like2CommentsPrinting Gigantic Spreadsheets To A Plotter
Hello Everyone. Very much in need of help. In my office we are needing to print a very large spreadsheet (by which I mean it has many columns and rows) to a plotter printer. After many painful hours of adjusting settings, looking online for answers, and so on, we have not managed to make it work. The closest we have gotten is for it to fit mostly on one A0 page, with the last few columns spilling over onto a second page, which means vast amounts of the remaining paper of "page 2" are printed white, and then we have to manually cut and stick these remaining columns on by hand. Please help us be spared the next year and beyond of manually cutting and sticking these spreadsheets! The spreadsheet goes up to column LL, and has 167 rows. The plotter is a colour Canon IPF770. It prints at a width of 914mm, and can be set to any length as you desire. We've literally printed 3 meters long before so there should be no issue printing. We use it frequently for prints from acrobat or preview or other programs, up to very long lengths, much longer than what is required for this document. The reason we are only printing up to A0 length is that any more and the document still spills over onto page 2 and just generates white space on the right of page 1. It seems like the document would fit snugly on a size only slightly longer than A0 without issue, if we could get this to work! We have tried adjusting the print page breaks, the lines turn dotted and cannot be moved any further. We seem infuriatingly restricted by the program, not being able to adjust the actual page size of the document above A3 (on the page layout tab), not being able to shrink the document to less than 10%, "fit to 1 by 1 pages" not doing so, among many other irritations that make it hard to decipher the source of the issue. Happy to go through everything and anything to eliminate any possible problems. ANY suggestions are very welcome!3KViews0likes2Comments