Recent Discussions
Mail enabled AD accounts
I have a question about email delivery to Mail enabled AD accounts. I am aware that these types of accounts are useful when you need to provide an external person like a contractor a login but don't want to provide them with a mailbox. You can add their external email address to the email field in Entra but they login with your companies UPN. Apparently there is also a license saving here as you dont need to assign them an E5 My questions are: If I put their UPN as the recipient of an email and send it will EXO fail to deliver it since they don't have an EXO mailbox? Or will it deliver to the external mail address set in the mail address field in their user account? How does advanced features like purview etc work if they don't have an E5? What's the process to create one of these mail enabled users?Blog about How to Troubleshoot Common #MicrosoftTeams Issues
Microsoft Teams has become one of the leading platforms for communication and collaboration in modern workspaces, especially as remote work has become more widespread. While Teams offers a plethora of features, users may occasionally encounter issues that can disrupt workflow. This guide outlines how to troubleshoot some of the most common Microsoft Teams problems, ensuring that you can get back to working smoothly in no time. https://dellenny.com/how-to-troubleshoot-common-microsoft-teams-issues/
Stock data type does not report correct Performance metrics
Hello, I've been using the stock data type in Excel 365 and I've noticed that some of my mutual funds are reporting dramatically different Performance metrics as compared to Yahoo and others. For instance, for mutual fund VMCIX, Excel is reporting 1/3/5-yr annual returns as 16.9%, 3.6%, and 10.1%, respectively. But Yahoo is reporting for the same metrics, 32.50%, 6.61%, and 11.96%. Can anyone explain the discrepancy? Are there any solutions to get more accurate (preferably, free) data in Excel? Thanks! David
Forecast Revenue based on historic cost data
We perform approximately 10,000 service orders in a year. Each service order has detailed cost categories (labor hours, labor cost, burden, material cost, equipment cost, per diem cost, subcontractor cost). Since we utilize a percentage of completion method to recognize revenue we have to determine the accrued revenue at the end of each quarter on open and unbilled service orders. My question is if there is a formula that can analyze the historic costs vs actual final billing on completed service orders and forecast unbilled service order revenue based on the composition of the costs. As an example, if a service order has a high material cost in relation to labor cost the final billing and subsequent margin percentage, (final billing - final cost) / final billing, is smaller than a service order has only labor. Currently, we breakdown historic cost and billing on completed service orders by project manager, customer, etc. to determine a historic margin percentage on completed service orders. Then, using that historic margin we forecast estimated final billing on open service orders: current cost / (1-margin). While that has worked in the past, I would like to refine the process with a more detailed forecast method. Thank you in advance for ideas.
All the rows after last "8" in PQ
Dear Experts, Can you please help on how to get a column for all the elements after Last-8 in below Table ( not sure of we can use Table.LastN to do this, but without M-code:- So, in the New Column, I should have, ,6,15,4,..12 Using UI, Thanks in Advance, Br, AnupamReceiving Error After Unpivoting Columns in Power Query
I'm trying to alphabetize a column of email addresses, separated by a semi colon. I found if I added an index column, split by delimiter, unpivoted the columns, sorted the rows, grouped the rows, and then add a custom column combining the data, I was able to achieve this. However, coming back to it today, it no longer works and is throwing a DataFormat.Error: Invalid cell value '#NAME?' error. After messing around with adding different steps to see if I kept getting the error, I noticed that no matter what I added after the Unpivoted Columns step, I will get this error. I did some research and it seemed that it was a type error so I switched all types by using "Detect Data Type". This still did not work. I also tried replacing all the nulls from splitting by delimiter and all blank cells with "Null" (not sure if there was any but wanted to be thorough). I turned on "Column quality" to see where the error was coming from and I'm getting 100% valid on all columns My code: let Source = Excel.CurrentWorkbook(){[Name="ACR"]}[Content], #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]), #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Type] = "Assets" or [Type] = "Links")), #"Replaced Value" = Table.ReplaceValue(#"Filtered Rows"," ","",Replacer.ReplaceText,{"ContentOwner"}), #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 1, 1, Int64.Type), #"Split Column by Delimiter" = Table.SplitColumn(#"Added Index", "ContentOwner", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"ContentOwner.1", "ContentOwner.2", "ContentOwner.3", "ContentOwner.4", "ContentOwner.5"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"SiteCollection", type text}, {"Site", type text}, {"ContentId", Int64.Type}, {"ContentOwner.1", type text}, {"ContentOwner.2", type text}, {"ContentOwner.3", type text}, {"ContentOwner.4", type text}, {"ContentOwner.5", type text}, {"Title", type text}, {"Index", Int64.Type}}), #"Replaced Value1" = Table.ReplaceValue(#"Changed Type",null,"Null",Replacer.ReplaceValue,{"ContentOwner.2", "ContentOwner.3", "ContentOwner.4", "ContentOwner.5"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value1", {"SiteCollection", "Site", "ContentId", "Title", "Index"}, "Attribute", "Value"), #"Sorted Rows" = Table.Buffer(Table.Sort(#"Unpivoted Columns",{{"Index", Order.Descending}, {"Content Owner", Order.Ascending}})) in #"Sorted Rows" Note: I tried to moving the Changed Type step to after the Unpivoted Columns step and I still receive the same error. I used Table.Buffer here because the grouping did not maintain the sort I specified on this step. Once I added Table.Buffer, it did. I removed it in trying to solve this problem and I still get the error. View when on the Unpivoted Columns step: Due to the nature of the data, I cannot share the file so if there is any clarification I can provide, please let me know.Lists Comparison (Stacked & Keeping order)
I've been trying to develop an Excel function which would compare Lists, each of single column arrays and generate a stacked (side by side) comparison aligning the matched (same) elements and keeping the original order of elements of each list intact in the resulting comparison. A Two-Lists comparison function has been developed. However, it would be great if it can be scaled up efficiently to 3 or more lists comparison. There are ideas on adopting the 2-list comparison function to greater number of lists, but the present approach is slow! and it seems that the 2-list comparison itself is not done the best way perhaps. Sharing in attached excel file, the worked-out function till now, and looking for better ideas!
SUBTRACTION IN PIVOT TABLE
Dear Member, I have created pivot table using existing data. Now there is two different type of data I need to comapre. data A vs Data B. In data A , there is two different value in data A called value 1 and Value 2. and Value 3 & Value 4 in data B. Now using pivot table I want to compare sum of Value 1 of data A vs Sum of Value 3 of data B. And same sum of Value 2 in data A vs sum of Value 4 in data b and in last where generally we get sum of VALUE 1 & 3 and Value 2 & 4 , I need to subtract this value so I can find difference. Thank you
Excel 2019 hangs when trying to print any excel file
since last week, no excel files will print. Excel just hangs. mine is MS office 2019. totally frustrated. tried repairing office online several times, reinstalled the printer several times etc. word and outlook prints without a problem. Problem is just with excel 2019!!!Retrieve data from protected spreadsheets
I like to make a spreadsheet which retrieve some data from 10 different password protected spreadsheets. With the normal methode, I need to fillout every time, the different passwords for each of these spreadsheets. The preferable way is that I open my spreadsheet and it will get data from the password protected spreadsheets. How can I make a link to retrive data with the password embedded?Disable Recent Tab Entirely
Hi, Got 2 questions about Excel on Mac. 1- Is it possible to disable the recent workbooks tab entirely without having to turn off "Show Workbook Gallery when opening Excel" on a macbook pro? 2- Is there an easy way to clear out that list? I've found a couple of videos on YouTube but they're too complicated.
Stock Function - Industry Coming Back as #FIELD!
Populating Industry fields for stocks and getting #Field errors. Canadia Stock exchange. REITs not coming back as well as mutual funds. Can i manually change it? Getting spill errors when i do Can i do something else to get the industry to populate Even if i can just get generic ETF/Mutual Fund, REIT/Real Estate would be better than errors Thanks Craig
adding specific cells
I run a small trucking business, where I add my daily runs into excel. I have a few different customers. at the end of the month I need to add specific customers for specific dates. I cant seem to get the formula right. Anyone able to guide me. I can even email a copy and you can play with it.Confused on how to bring the needed data for the report view
So I'm not an expert in Excel, just know few basics and managing. There is a huge data file (Comes on a monthly basis) with thousands and thousands of rows and columns. There is one column with GL codes and one row with entity codes, so basically the cell provides the amount. Many of the cells have zero in it (Meaning no transaction) Client already has a report which pulls the data based GL codes, basically the total of account. The client wants a new report to pull the data based on GL codes(Column) and entity codes (Row), so that he gets detailed, clean view and can be updated monthly. Here is the mock version of the data. I want to bring over the data in a new sheet with the GL Codes and entity codes. I'm not sure what exactly to do since I'm not that well versed with excel. Please help your fellow mate 🥹 Apologies if my English is bad. TY!Embed SharePoint Document in Excel
In a SharePoint library, I have an Excel document and a Word document. The Excel document refers to the Word document quite a bit so instead of opening two windows to view the documents and toggling back and forth, I wanted to embed the Word document into an Excel sheet so it will update whenever the Word document updates. I found Insert > Object (in the Text section of the ribbon) but that only seems to embed a hard copy of a Word document from your computer and not from an online source. I was thinking something like this: I don't want to just add a link to the Word document, so if this isn't possible, oh well.Dynamic Power Query
I use Excel on a Mac and have been looking for a way to have the poser query be dynamic for different environments. There is a lot of discussion in various website about doing that be referencing a cell in your worksheet. That works fine for Windows, but will not work on a Mac. I finally stumbled on to a solution that someone posted on the internet. unfortunately I didn't save the reference. But I have applied this solution and it works very well on both Windows and Mac. The code to do it is : provide your new source path and query name you are changing between the quotes Sub change_source() Dim pqTable As WorkbookQuery 'replace pqTable with any name Dim oldSource As String Dim newSource As String Set pqTable = ThisWorkbook.Queries("Insert query name") oldSource = Split(pqTable.Formula, """")(1) ' oldsource will be the old file name newSource = "insert new path" MsgBox ("Changing From: " & oldSource & vbCr & vbLf & vbCr & vbLf & "To: " & newSource) ' this code then changes the Source formula in the query pqTable.Formula = Replace(pqTable.Formula, oldSource, newSource) End Sub
Recent Blogs
- First published on MSDN on Sep, 17 2010 The new Containers feature in Visio 2010 is great for adding structure and organization to Visio diagrams.Dec 30, 2024
- We are excited to announce that we have a new and improved solution for syncing your form responses to Excel, with better reliability and performance! Previously, some Forms supported an older versio...Dec 30, 2024
