Forum Widgets
Latest Discussions
Playing with conditional formatting
This formatting is something I used to restructure the way I wrote formulas between 2015 and 2018. In the original I used VBA but now, as often happens, worksheet formulas used within conditional formatting are sufficient. The formula defining the condition is = DIRECTREFλ(A1) //Function to test for the presence of direct cell references DIRECTREFλ = LAMBDA(cell, IF(ISFORMULA(cell), REGEXTEST(FORMULATEXT(cell), "\b[A-Z]{1,3}\${0,1}\d{1,6}\b")) ); The original was pretty effective as a training aid to avoid the practice of relative referencing and use defined names! That said, not many followed my lead! Mind you when I posted a discussion on Chandoo it didn't meet with overwhelming support, though subsequent events in the form of spilt ranges have made my life so much easier! The post did however stimulate more discussion than I had anticipated. The A1 notation is an abomination that has no place within serious spreadsheet design -- discuss? | Chandoo.org Excel Forums - Become Awesome in Excel
Gantt Chart Weekday Function
I am trying to use a gantt chart with conditional formatting for a project. I have my conditional formatting set up the following: =AND($D9<=H$5, WORKDAY($D9-1, $C9)>=H$5) problem is, some of the tasks have a duration of less than 1 day (column C) and so adding the -1 is blanking out several tasks. How do i correct this? I'm just starting to learn conditional formatting so some of this is still like a foreign language to me. Thanks!
Chart linking with Name Manager
so it's known chart behave different than other items, i linked it to a box that indirect the limit of which it should take values from a column, like i used this code to do it: =OFFSET('Study '!$F$25,1,0,'Study '!$BD$26), but this fails when i want it to take starting and ending limit with offset command, it just re input values, like if i want beginning to be 100 and ending to be 300 it reads first 100 and then reread them plus the extra 200 so i have 400 values. is it possible to make it so from column F it starts taking values from lets say box BC and the ending limit to be from BD? i tried looking it up and figuring it but you know how it goes with chartsHow to make a chain selection with drop-down lists
What formulas can be used to generate a chain of filters from a selection of data that, in turn, filters the data again until a specific element is selected? For example: I have lists of various pieces of equipment categorized by type, brand, and model, and what I need is to select them in stages. That is, I position myself in a cell that displays a series of data from the "type" matrix (a long list located on another sheet). When I select a piece of data, the next cell only shows me the list of data, but also the previous cell from a list on another sheet. And in turn, the next cell shows or displays the list from the selected cell. It's a chain filter where each selected piece of data depends on the previous one.
Unprotect in Office Scripts
I am writing an office script that requires sorting on a sheet with the overall sheet protected and specific cells not protected. I need to be able to sort an unprotected range using an automation but the protection is restricting it even though I have "sort" allowed in the protection options. Is there a way to unprotect and then re-protect the sheet as part of the scripts? Including passwords? function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getActiveWorksheet(); // Clear auto filter on selectedSheet selectedSheet.getAutoFilter().clearCriteria(); // Custom sort on range range C9:I6001 on selectedSheet selectedSheet.getRange("C9:I6001").getSort().apply([{key: 0, ascending: true}], false, true, ExcelScript.SortOrientation.rows); // Apply values filter on selectedSheet selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 6, { filterOn: ExcelScript.FilterOn.values, values: ["1"] }); }Full Dark Mode in Excel
Currently you can set Office Theme to Black which acts as a dark mode. In Word, setting this to Black makes the page dark and the text white when it is set to “Automatic”. I was wondering if anyone had an idea if Microsoft is planning to release this same thing for Excel too? Right now Excel when using the dark theme makes everything dark except the cells. Which kind of makes no sense to me. This is where your eyes are most of the time. It needs to be a proper fully dark theme. Is there a way to get a full dark mode without manually setting the background color of the cells dark and the text white? Can a plugin be made to do that while keeping the actual cell background “empty” and the text “Automatic”? Is there already a plugin that does that? Thanks!
Native FIFO Inventory Costing Function for Excel (Proof of Concept with LAMBDA)
Excel currently lacks a native function to calculate FIFO (First-In, First-Out) inventory costs when products are purchased at different unit prices and later issued in partial quantities. FIFO costing is a standard accounting requirement under IFRS and is widely used in inventory management, retail, manufacturing, and financial reporting. Today, Excel users must rely on VBA, Power Query, or complex multi-step formulas, which are error-prone and difficult to maintain. As a proof of concept, I created a native Excel implementation using modern functions such as LAMBDA, LET, SCAN, MAP, and dynamic arrays. The function calculates the FIFO unit cost of the latest outgoing inventory movement, given: an array of incoming quantities • an array of unit costs for each incoming batch • an array of outgoing quantities While this demonstrates that FIFO costing is feasible using native Excel functionality, the level of complexity required highlights a clear feature gap. A built-in FIFO inventory costing function would significantly improve usability, readability, performance, and accessibility for non-technical users. I believe a native function such as FIFO.COST() or INVENTORY.FIFO() would be a valuable addition to Excel, alongside potential support for LIFO and weighted average costing methods. I’m sharing this example to illustrate both the feasibility and the need for native inventory costing functions in Excel. =LAMBDA( InQty, UnitCost, OutQty, LET( PrevOutQty, TAKE(OutQty, ROWS(OutQty)-1), LastOutQty, INDEX(OutQty, SEQUENCE(1,1,ROWS(OutQty))), OutIndex, ROWS(OutQty), RemainingFromPrevious, LET( InMatrix, InQty, OutMatrix, PrevOutQty, ApplyFIFO, LAMBDA(InMatrix, OutMatrix, LET( n, ROWS(OutMatrix), RecursiveFIFO, LAMBDA(self, Remaining, i, IF( i > n, Remaining, self( self, LET( OutAmount, INDEX(OutMatrix, i), CurrentStock, Remaining, Consumed, VSTACK( OutAmount, TAKE( SCAN( OutAmount, CurrentStock, LAMBDA(acc, qty, IF(qty > acc, 0, acc - qty) ) ), ROWS(CurrentStock)-1 ) ), IF(CurrentStock > Consumed, CurrentStock - Consumed, 0) ), i + 1 ) ) ), RecursiveFIFO(RecursiveFIFO, InMatrix, 1) ) ), ApplyFIFO(InMatrix, OutMatrix) ), FirstRemaining, SCAN( LastOutQty, IF(OutIndex = 1, InQty, RemainingFromPrevious), LAMBDA(stock, qty, IF(qty >= stock, 0, stock - qty) ) ), SecondRemaining, VSTACK( LastOutQty, TAKE(FirstRemaining, ROWS(FirstRemaining)-1) ), FIFOQuantities, MAP( IF(OutIndex = 1, InQty, RemainingFromPrevious), SecondRemaining, LAMBDA(stock, qty, MIN(stock, qty)) ), FIFOUnitCost, SUMPRODUCT(FIFOQuantities, UnitCost) / LastOutQty, IF(ROWS(InQty) = 1, UnitCost, FIFOUnitCost) ) ) Have a nice day. Juan Miguel Arraztoa
name manager rejecting lambdas
I tried pasting a lambda in name manager but excel refused. I belive that it's because either it is too long (which it isnt) or it has too many optional parameters (8). Anyone knows why name manager will reject to paste my lambda? These are the parameters: =LAMBDA(array,slice1,[index1],[return_index_slice2],[index2],[return_index],[if_not_found],[logic],[headers],[function],LET(...))
