Recent Discussions
Index and match mystery (for me that is...)
Hello, I am having a hard time with an Index and match function I created. It's very simple but I am overlooking something. I am creating a file in which I can keep track of my golf progress. In golf you use a handicap system. This means that based on your formal handicap you get a number of extra strokes on a course. For instance: The formal handicap of a player is 21.3 Ranges: From to Course handicap additional strokes 16,4 19,4 3 19,5 22,5 4 22,6 25,7 5 In this example the player is awarded 4 additional strokes based on that 21.3 falls in the range from 19,5 to 22,5. I've created a function but it doesn't always seem to work...It does return a value but not always the correct one.... =INDEX(C21:C36;MATCH(C48;A21:A36+(C48<=B21:B36);1)) In cell C48 people can fill in their formal handicap. In cells A21-A36 the 'from' values of the range In cells B21-B36 the 'to' values of the range In cells C21-C36 the extra strokes for the course handicap What am I doing wrong? Merry Xmas!!!! :-)
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!
Sending out E certificates
Hello, New 365 user here. I would like to ask if there is a way to use 365 to build surveys & automatically send out e-certificates to respondents similar to Google Form & Certify em? Also would there be a daily limit as with Google Forms, they only limit up to 100 & 1500 if you have a Google Workspace account. Thanks, Marc
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.
Loop development has seemingly stopped
Our team adopted Loop since many of us prefer the personal knowledge management system style of Notion or Obsidian to other classic note-taking and sharing software. However, many core features are still not present that have been Day 1 features of these other tools, and a quick check of the Loop Roadmap leads me to believe that most features will be security/governance focused. That's disappointing if true, because right now Loop is clearly inspired by those tools, but incredibly far behind in a way that makes it hard to justify their continued use. For example: Cannot embed documents inside Loop-- in Notion you can just drag and drop a PDF to upload, and the /embed command lets you put an interactive document inside the page Cannot select multiple pages to re-arrange structure No Copilot to perform page cleanup-- Notion lets you use AI to fix formatting, and it works very well. No command palette-- this is a must for operating in a knowledge management system No Mac app No mind map a la Obsidian I could continue to produce these examples, but I am hoping to find out that once some core governance features are completed, Microsoft will be focusing some development efforts on improving this platform-- we hope it is not abandonware a few years into it's launch.
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 ArraztoaMoving Exchange Account Source Account
I have a very complex environment I'm hoping someone might jump start my search. We have two domains syncing to Entra ID. One domain is a resource forest where our Exchange environment sits. That domain contains disabled stub accounts synced to our primary domain where the actual user accounts sit. The source for all EXO mailboxes are the stubs in the resource forest. Those accounts are kept in sync using FIM 2008. We're wanting to decom that entire resource environment and move all of the attributes to the primary domain. The resource domain schema is the last version of Ex 2016. The primary domain schema is Ex 2010 SP1. I know my first step is to update the primary schema, however, has anyone encountered a situation like this? Any help would be greatly appreciated.
Android Teams login fails during ADFS federation with SSL error
Hello Android mobile users cannot sign in to Microsoft Teams The login fails during the ADFS federation step due to an SSL error Environment Android OS versions 10 to 14 Microsoft Teams mobile app Entra ID federated with on premises ADFS ADFS service URL is masked Public certificate issued by Sectigo Issue description After entering the account in Teams the sign in process redirects to ADFS The page does not load correctly and shows infinite loading or a blank screen The same account works normally on PC browser PC Teams and Outlook Web The issue occurs only on Android mobile apps that use WebView Android log summary OAuth2 WebView client received SSL error Primary error SSL untrusted Wildcard certificate for masked domain Certificate issued by Sectigo Public Server Authentication CA Troubleshooting performed Device date and time verified Teams app cache cleared and app reinstalled Issue reproduced on multiple Android versions and devices PC authentication works with the same certificate Questions Can Android WebView or Microsoft mobile authentication fail with SSL untrusted when the ADFS server does not provide a complete certificate chain Is full chain certificate configuration required on ADFS IIS for mobile authentication Can SSL inspection or proxy interception cause this issue only on Android apps while PC browsers work normally Are there official Microsoft recommendations for certificate configuration when using ADFS federation with Android mobile apps Additional information The same behavior occurs in other Microsoft mobile apps The suspected causes are incomplete certificate chain or network SSL inspection Thank youGraph http 449 throttled
We are experiencing a lot of Microsoft Graph trolling errors from some of our applications that are hosted in Azure Web App Services and other third party such as Front App to name one. I am trying to find an approach or strategy to figure out and narrow down what may be causing so many of these events. Whether I can narrow down by application or process, I am not sure yet. We enforce MFA on all our users, but of course, on Azure Enterprise Applications, we don't, which are used extensively in our ecosystem of apps. Any help is much appreciated here.Is there an acceptable method to convert pub to docx?
Unfortunately Microsoft has decided to end Publisher. So users are forced to find methods both for archiving old files and converting current files to be able to continue working. For the first PDF will most likely be the easiest. For the second I've been trying to find a converter. This proves to be a challenge. And the results have been very unsatisfactory so far. My main publication is a brochure which is printed double-sided on A4 paper, stapled and then folded to A5 size. The publication contains 99% text boxes (with formatted text) and pictures. Here and there a shape like a horizontal line. All things that Word definitely supports. So converting it should be a no-brainer - at least I thought so. Of course Publisher can save as DOCX. Unfortunately the function only supports plain text. So the text boxes are removed, which means a lot of manual rework. Also the text isn't converted continuously. For exapmle the text that was on the first sheet in my Publisher file, ended up on page 5 in Word. Bottom line: not acceptable. Microsoft therefore don't even refer to that save function in their official communication to Publisher retirement. They suggest a workaround instead: save the file as a PDF and open it with Word. The suggestion is embarrassing enough, since it implies that Microsoft are unable to provide suitable conversion between their own formats. I tested it anyway. But the layout ends up in Word in a way that requires a lot of manual rework. Tab stops are partially removed, page margins are shifted. The pagination is also incorrect, so that some pages are half empty. Bottom line: not acceptable. Also tried some online converters. Unfortunately the results haven't been satisfying either. Most of them convert text boxes into shapes with integrated text boxes. And even worse: big text boxes are often split into several smaller boxes. In any case there still remains a lot of manual rework. Bottom line: halfway acceptable. Disappoiting Conclusion: At present, I consider it the best option to rebuild the file from scratch in Word using Copy-Paste. If someone knows a better method, would be happy to hear about it.
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(...))In "Per-user multifactor authentication" I disabled MFA for one user; All got disabled
Hi, I'm the 365 admin for our org. Today I had a user that got a new phone and became stuck in a MS Authenticator app loop. (The app required MFA to login to the app, but they can't login to the app because they aren't logged into the app. This happened once before, and is a ridiculous Kafkaesque situation; but I digress.) To solve it, I went to disable MFA on their account, allowing them to login to the app. Then MFA could be re-enabled. Problem solved. And indeed that did work. However, on the admin side (per the screenshot) I checked off their user account (the user starting with the letter "B") and hit the "Disable MFA" link. A confirmation appeared asking me if I wanted to disable MFA for all selected users. Because I'm the careful sort, I could still see that only that one user was checked off as the popover div didn't cover that much of the screen. Hence I confirmed that I wanted to disable MFA for the selected users (i.e. that one user). I then refreshed the page, and all users are now shown as having MFA disabled. I'm 100% sure only that one user was selected, not everyone in my org. That would've been foolish. Trying to figure out what's wrong with this portion of the admin site.New windows opening under other windows
I'm tearing what's left of my hair out here. How do you make new Excel windows NOT pop up UNDER existing windows? I've made it work in the past by moving a window multiple times, closing it and then opening it back up. Sometimes this works, sometimes it doesn't. It seems you have to know the secret knock. 9 times out of 10, the new windows will open up under my existing window which has never been a thing I have wanted ever. Yet it's the default. 75% of my monitor space is unused yet Microsoft will only open windows under existing ones. What's the solution here?"all the merged cells need to be the same size" during a Custom Sort
On ASUS StudioBook x64 PC, Windows 10 Pro for Workstations, ver. 20H2 (OS Build 19042.1288)Microsoft 365, EXCEL ver. 2109 (Build 1443020306) ... While attempting to do a custom sort of an EXCEL table of 96,600 + records, I received an error stating "all the merged cells need to be the same size during this operation" Following online help directions for finding merged cells, none were found! (I didn't knowingly create merged cells, but when clicking Options > Format > Alignment, under Text Control, both "Wrap text" and "Merge cells" have their squares filled in with black fill.) The same error occurs when I attempt to sort the field 'editinitl' (A to Z) in the table. The field holds 3-character-long abbreviations of persons name in capital. Why is this message occurring when the suggested find procedure finds no merged cells? Here's the procedure I tried to find the merged cells. Find merged cells You may encounter an Excel worksheets that has https://support.office.com/en-us/f1/topic/merge-and-unmerge-cells-5cbd15d5-9375-4540-907f-c673a93fcedf?NS=EXCEL&Version=90 that you don’t know about. This can be frustrating because Excel doesn't sort data in a column that contains merged cells. Following the steps below, you can find all the merged cells in your worksheet and then https://support.office.com/en-us/f1/topic/merge-and-unmerge-cells-5cbd15d5-9375-4540-907f-c673a93fcedf?NS=EXCEL&Version=90. Click Home > Find & Select > Find. Click Options > Format. Click Alignment > Merge cells > OK. Click Find All to see a list of all merged cells in your worksheet. When you click an item in the list, Excel selects the merged cell in your worksheet. You can now https://support.office.com/en-us/f1/topic/merge-and-unmerge-cells-5cbd15d5-9375-4540-907f-c673a93fcedf?NS=EXCEL&Version=90. Need more help? You can always ask an expert in the Excel Tech Community or get support in the https://go.microsoft.com/fwlink/?linkid=827514.Formula/method to link the data/responses I get from my forms into a different table.
Hi everyone! I need help with a project that I'm creating. Im making use of Microsoft Forms in order to input certain information. I made use of branching since some answers depend on the previous choice. So moving on to my problem, I want my table2 to get the responses/data from the data table created by the forms. Table 2 Table of responses For reference for the spill error, here is the formula that I used. Any insights will help a lot. Thank you have a great day everyone.
Recent Blogs
- Rich alt text is automatically generated when you insert an image, making your content more accessible from the get-go.Dec 22, 2025
- Powered by Microsoft 365 Copilot, Explainer offers instant summaries and explanations of text.Dec 19, 2025
