Recent Discussions
Find and replace mystery
I am attempting to replace all the occurrences of "$23" within a formula with "$43" [I'm not using the quote marks in the box]. Every single time, it replaces it with "$34" instead of "$43". For troubleshooting, I tried: Find "23" replace with "43" - it worked correctly. Find "$23" replace with "$6300" just to see if a different 'replace with' value would have a different outcome - it still replaces it with "$34" I have another find and replace in the same cell and same formula where I am replacing "$18" with "$38" - it works just fine! I do not have the SUBSTITUTE formula anywhere in this cell. I read somewhere that SUBSTITUTE could affect find and replace. I am stumped. If it matters, the "$23" I am trying to replace is within a SUMPRODUCT formula. I am using 365.Need help with Excel formula
=IFERROR(INDEX(TBL_REG[Appointment Effective Date],MATCH(TBL_SF[@[License '#]],TBL_REG[License Number],0)),"!-No Match") Need some help with this formula. It works fine however, in the TBL_REG table there are multiple rows that have the same [License Number] so it will return the first [Appointment Effective Date] it finds when matching [License '#] from the TBL_SF to the [License Number] in the TBL_REG table.. I would also like to match on [State] from the TBL_SF table to the [Appointment State] from the TBL_REG table. Thanks, ShaneHow SharePoint Online Intelligent Versioning Interacts with Retention Policies and Labels
Intelligent versioning recently appeared in SharePoint Online. The purpose is to save storage by removing unnecessary versions. But retention policies and labels can stop the removal of versions. This article explains what happens when SharePoint Online attempts to trim (remove) unwanted versions of files under the control of retention policies and labels. https://office365itpros.com/2024/11/13/intelligent-versioning-spo/Use Audit Data to Improve Finding Inactive Copilot Users
A previous article explained how Microsoft 365 usage report data can highlight inactive Copilot users. If we add audit data to the mix, the analysis becomes much richer because we can see exactly what use people make of different Copilot apps, like Word, Chat, Outlook, and so on. Better data means better decisions! https://practical365.com/inactive-copilot-users/
"There's a temporary problem with the service. Login Error"
Hi, I have encountered a log-in issue with a office.com account. When trying to log in after yesterday (5th April 2025 at 11 a.m. UK time, the following happens: ) I go to office.com 2) I enter my email address 3) I am asked to verifymy identity via multi-factor authentication. This is successful. 4) I am asked to set a new password 5) I enter a password 6) I reeive the error "There's a temporary problem with the service. Please try again. If you continue to get this message, try again later." I believe that suspicious activity was detected (which I triggered accidentally) but cannot find support for rectifying the situation. The issue is not with my multi-factor authentiction, but setting the new password.Update #9 for Automating Microsoft 365 with PowerShell eBook
The Automating Microsoft 365 with PowerShell eBook is now at update #9. The latest update spans 300 pages of content covering how to use PowerShell with the Microsoft 365 workloads, including Exchange Online, SharePoint Online, OneDrive for Business, Teams, Planner, and Entra ID. There’s no other book that includes so many worked-out examples of how to get things done with PowerShell and Microsoft 365. https://office365itpros.com/2025/02/18/automating-microsoft-365-with-powershell9/
Endnote/Footnote Jumping Broken
Operating Word 16.96.1 on Mac Sequoia 15.4.1 There was an update for Word a week or two ago, and since then, my endnote functionality has been broken. When you double-click on endnote numbers, it should jump to the corresponding note. This is not working. (For footnotes either.) If the cursor is located at the endnote number, I can click "Show Notes" in the header, and it will jump, but that takes too much time. I have restarted the computer, uninstalled and reinstalled Word, opened Word in safe mode, moved files from the library to the desktop (per support instructions)... nothing has fixed it. I have updated the computer software since this happened, and completed another Word update, but otherwise, I haven't installed any other apps or programs. I am a professional editor, so I spend all day in Word checking endnotes, and I lose so much time not having the jump functionality. Any help would be appreciated!SOLVED - Hyperlink function works as Flash Fill but not as Dynamic/spill array
Hello all, I have tracker logs that I'm comparing to my bank and have solved all the issues so far, except for how to spill my Find & Go To hyperlink function. I have a complex formula in range $I$3 that will list all mismatches (bank transactions that are not in tracker and tracker logs that are not in bank) via spill and a data validation list in range $J$3:$J$1000 to select an action from. In range $K$3, I need a spill formula that will search the import sheets for the amount shown on the mismatch list, for each mismatch, then go to it upon click. In range $K$3:$K$1000, I currently have individual formulas that will check if the mismatch contains "Bank" and if so will display a hyperlink that extracts all text then reads the amount as a value, then matches it to the Tracker Import sheet. Otherwise, it will display a hyperlink that does the same thing but matches it to the Bank Import sheet. Logical Test (checks if it contains "Bank"): Value if True (creates hyperlink to the mismatch amount on the Tracker Import sheet): Value if False (creates hyperlink to the mismatch amount on the Bank Import sheet): This works perfectly fine but when I do it as a spill formula, the links either don't work or take me to the incorrect sheet/cell. Formula (Flash Filled range $K$3:$K$1000 - working): =IFERROR(IF(ISNUMBER(SEARCH("*Bank*",I3)),HYPERLINK("#'Tracker Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3,FIND(" ",I3)-1)),'Tracker Import'!$D$2:$D$1000,0)+1,4),"View Log"),HYPERLINK("#'Bank Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3,FIND(" ",I3)-1)),'Bank Import'!$F$2:$F$1000,0)+1,6),"View Transaction")),"") Formula (spill from range $K$3 - not working): =IFERROR(IF(ISNUMBER(SEARCH("*Bank*",I3:I1000)),HYPERLINK("#'Tracker Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3:I1000,FIND(" ",I3:I1000)-1)),'Tracker Import'!$D$2:$D$1000,0)+1,4),"View Log"),HYPERLINK("#'Bank Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3:I1000,FIND(" ",I3:I1000)-1)),'Bank Import'!$F$2:$F$1000,0)+1,6),"View Transaction")),"") Notes: • Range 'Tracker Import'!$D$2:$D$1000 is an Amount column that lists logs from my Spending Tracker. This range is just values. • Range 'Bank Import'!$F$2:$F$1000 is an Amount column that lists transactions from my bank statement. This range is just values. • If the Mismatch List says an amount is not found in the Bank, that means it's an existing amount in the Tracker that shouldn't be there, and vis versa. I would highly appreciate any solution to make my current formula a spill formula. Thank you!Obtain Deleted Stats (SharePoint) by Retention Policy
I've scoured: Identify the available PowerShell cmdlets for retention | Microsoft Learn and the Unified Audit Log (Using Search-UnifiedAuditLog in Powershell: All You Need To Know, How to Query Microsoft 365 Audit Logs using PowerShell – TheITBros) to see if I can come up with a method to obtain some statistics regarding how many files and space (storage) has been freed up with the use of retention policies being enabled. I'm drawing a blank. In an ideal world, I'd like know how many files have been deleted by the system (the system enforcing a 5 Year from last modified Date and Delete Policy) for the last year or 6 month intervals. If possible the corresponding volume of storage space recovered from these deletions. Any ideas?Utilizing Excel's turing capabilities to create Conway's 'Game of Life'
The Background It's been said with Lambda (and LET and a wealth of functions in 365) Excel has become 'turing-complete'. To quote the article linked below: "You can now, in principle, write any computation in the Excel formula language." https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/ The Challenge I thought it would be fun to create Conway's 'Game of Life' in Excel 365 to see how far I could push things. Conway's Game of Life - Wikipedia The rules are simple: A 'cell' has up to 8 adjacent cells (less if the cell is on the edge of the board). A 'neighbor' is a cell with a 1 while a 'dead' cell is empty. An 18x18 board Multiple iterations Bigger boards! more (it's relaxing to create new shapes and designs) The Approach My first thought was to use MAKEARRAY because I could use 'r' and 'c' coordinates and there would be no stacking. I devised a recursive function that worked for 1 iteration but failed on subsequent iterations because the use of TAKE/DROP was slowly shrinking the board! The revised approach is essentialy a recursive MAP that uses 3 arrays: the input matrix, the 'r' array (row numbers) and the 'c' array (column numbers). It's my way of using r/c without using MAKEARRAY. For Discussion I welcome any improvements to the existing function and any different approaches someone may have to creating Conway's Game of Life. Conway Lambda follows: Conway =LAMBDA(matrix, iterations, IF( iterations = 0, matrix, Conway( LET( height, ROWS(matrix), width, COLUMNS(matrix), r_arr, SEQUENCE(height) * SEQUENCE(, width, 1, 0), c_arr, SEQUENCE(height, , 1, 0) * SEQUENCE(, width), CheckNeighbors, LAMBDA(lattice, r, c, LET( RCx, LAMBDA(row, col, IFERROR(CHOOSECOLS(CHOOSEROWS(matrix, row), col), 0) ), N, RCx(r - 1, c), NE, RCx(r - 1, c + 1), E, RCx(r, c + 1), SE, RCx(r + 1, c + 1), S, RCx(r + 1, c), SW, RCx(r + 1, c - 1), W, RCx(r, c - 1), NW, RCx(r - 1, c - 1), compass, VSTACK(N, NE, E, SE, S, SW, W, NW), neighbors, SUM(compass), IF( AND(lattice = 0, neighbors = 3), 1, IF( AND(lattice = 1, OR(neighbors = 2, neighbors = 3)), 1, 0 ) ) ) ), MAP(matrix, r_arr, c_arr, CheckNeighbors) ), iterations - 1 ) ) )
Access changing lettercase - redux
This has probably been beaten to death, but I'm fuming, so here goes. I want Microsoft to DO SOMETHING to help. Access has the highly annoying habit of changing lettercase on identifiers, seemingly at random. This is a real pain when using add-ins like MSAccessVCS, which makes it possible to use git for source code control. Here' what I've deduced is happening: There exists a dictionary/symbol-table containing every identifier used in a project. The symbol table contains ALL identifiers, including those defined by MSAccess, referenced libraries, and your code. Searching that table is case-insensitive, but it stores the canonical version of the identifier with case preserved. Normally, when you type an identifier that already exists in the table, the VBA editor "corrects" the lettercase of your entry to match the table's entry. The extremely annoying part is that sometimes that last step works in reverse. You type an existing identifier, but with a different lettercase. Instead of correcting what you typed to match the table, it instead updates the table with the version you typed, and decides that's the way future and existing identifiers should be spelled. Here's a recent example. I tried to create a class module constructor (Class_Initialize) but typed it "class_initialize" implicitly expecting that the VBA editor would "fix" it. Instead, it updated the symbol table and set the canonical lettercase to the all-lowercase version... and then proceeded to change the lettercase on the Class_Initialize() method in all my class modules. It has now decided the canonical lettercase is "class_initialize" and that's what it "corrects" to from now on. Of course, when I did the next MSAccessVCS export, git showed a bunch of unwanted (but cosmetic) changes. This pollutes the changeset and makes source control more difficult. THIS. IS. A. BUG. My workaround is, when starting a checkin, I go through the list of changes and group all such changes into one commit called "VBA Artifacts". But come on, this shouldn't be necessary. At least let us know WHY this happens, and give us some control of the process. A way to specify/correct the canonical casing would help.
Likert Quick Import
Hi folks i have a written list of Statements I would like to arrange in a likert for forms. It seems I can only copy and paste one statement at a time and the quick import functionality of forms does not pick up likerts. Can anyone give me any advice on how to get all my statements into a likert without copying each one at a time? Thanks.
How to know the original path of an inserted image ?
Hi all, I have a problem with an Excel file. In this file I used the feature "Insert image above cell" and I chose an image from a local path (on my hard drive). So far so good. However, if I copy the file to another computer, the image is gone. That's the concept. Now the problem is that this an auto-generated Excel file from an external program. I know where the images are but I generated multiple reports, modified some of the images and changed the images in some excel files to point to other folders. It works fine on the original computer but I copied the Excel files and the images (or so I thought) to the other computer and some images are missing. The question is : how do I know the original path of the image so that I can also copy it and re-link it. The additionnal question to that is : I modified the local image for some reason (didn't change the path) and I want to update the shown image to include my modification without closing and opening again the Excel file. Is that possible and how ? But the first question is really more important because basically I have a new laptop and I need to recover the whole file and images to export it if needed later in time when I won't have the old laptop anymore.Pulling data from different sheets into a summary sheet
How can I pull employee data from different sheets into a live-updating summary sheet? I have 5 groups of employees — A, B, C, D, and E — with their names listed across 5 separate sheets (one for each group). I’d like to create a summary sheet that displays all employees and the groups they belong to. Sometimes employees change groups. Is there a way for the summary sheet to update automatically when those changes happens?Force update to targeted release for Teams?
We have a number of people on the M365 targeted release option and they've been getting the updates including Teams for a while ⬇️ We also added some more people over 72 hours (3 days) ago to the targeted release option in the M365 admin center but they still don't have the Teams update They've signed out of the Teams desktop app and then signed in again to try and move things along but no change How long should this take? Is there a way of making this happen quicker? ThanksSharepoint Sync-On-Demand & Application.Path
A few years back I traced some weird VBA Macro code behavior in Excel back to a SharePoint feature called "Sync-On-Demand". When Sync-On-Demand is turned on, the VBA code "Application.Path" no longer returns the actual file location, and starts returning a SharePoint site. It does these without any warning and as far as I can tell there is no documentation about this. This broke my companies primary management software until I traced it down and turned off Sync-On-Demand for my remote users. As of this week, the Sync-On-Demand option is not only gone from the Settings, but it seems to be secretly turned back on in the background as well. If we weren't inspecting values after the code crashed, we would not even know this is happening. This means that right now anyone who is using SharePoint and thinks they are opening a local file, is actually opening a web location without even being told about it in any visible way. And it's being done through some sort of background hackery. It's shady as F. We have opened a support ticket but so far they don't even seem to know they have done this and are pointing to new settings locations, but those do not yet exist for us. Is there anyone here with knowledge of this issue and some way for us to turn the sneaky Sync-On-Demand off as of today? The new help docs provided to us show a Conext Menu option available from an Explorer window, but that option does not exist for us. Does anyone know a way to get the file location of whatever Excel file is running a VBA Macros? Application.Path is supposed to be that, but I need a new way that actually works to return the actual harddrive path location, not the SharePoint location.
Events
You’re passionate about what you do, and we’re passionate about empowering your potential. The Microsoft 365 Community Conference is your chance to keep up with AI, build game-changing skills, and ta...
In-Person
Recent Blogs
- 3 MIN READSay hello to new agentic features for Copilot in Forms—your intelligent assistant that helps you work smarter to collect data and make decisions. Copilot can now help you get your form ready to send...Apr 25, 2025
- 4 MIN READAt Microsoft, building community and diversity and inclusion is at the heart of our mission to empower every person and organization on the planet to achieve more. The Microsoft 365 Community Confere...Apr 22, 2025
