Recent Discussions
Excel formula troubleshooting
Hi everyone. I am using excel online to try and become a data analyst. now I have troubles with the formulas. I am doing what is supposed to work. " =sum or subtotal (I2:I38) and press enter but the end result is overflow. what is going wrong here? Help would be very welcome.
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.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
Date format to LookUP two table
I have 2 spreadsheets each containing table with ID (unique identifier Key) and service dates, Table A is ID & service date, Table B has ID, service date and lot of other transaction details which i want to look up. I want to join on ID and service date (I used to convert date to excel serial no. and concatenate with ID to bring other columns on matching records). with these files I also need to match the time - how do i do that? both files date/time format are quite different. Attached file for reference. https://www.dropbox.com/scl/fi/nd2fnni2nm2890145nrl1/Test.xlsx?rlkey=9svhktjrgphmgdbwzjg90tzg0&st=qeyl0ewr&dl=0
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
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!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.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?
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 youReceiving 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.
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.
Power Query Get Data Problem
When I first retrieve data from the Fabric data warehouse in Power Query, the data loads successfully. However, when I later try to load the data as a connection only, I can't retrieve the data a second time. As a result, I couldn't complete my project. It's unacceptable for such a critical connection to have this many issues during the second load when connecting with Excel.Excel Does Not Sync Until Opened
I created a form linked to an Excel spreadsheet in SharePoint as well as a Power Automate approval flow triggered on submission. In addition to sending out confirmation/approval emails, the flow also updates the associated Excel spreadsheet with the approval/denial. The flow was working fine until last week but since then has failed 2 consecutive times.The failures appear to be related to the flow unable to find the automatically generated submission ID in the Excel spreadsheet. When I open the linked spreadsheet, the submission is not immediately present at first but the file does sync at that point and the entry appears. When re-triggered after the sync, the flow completes successfully. Not sure if it is related, but MS form/linked spreadsheets have been updated by Microsoft recently and I was prompted to go through an update process for synced Excel spreadsheets. I completed the update with this specific form last week after the flow failed for the first time. TLDR - my form only syncs with the spreadsheet once opened. Any ideas how to get MS Forms to update the spreadsheet in real-time as opposed to when it is opened?The Confusing Renaming of Microsoft 365 Copilot
Microsoft loves branding exercises. At least, that can be the only reason why the Microsoft 365 Copilot rename is happening. I can think of no other reason why Microsoft would seek to confuse its customers by applying the Microsoft 365 moniker to an app that can’t access Microsoft 365 data, unless of course people pay to use Copilot agents. It’s all very confusing. https://office365itpros.com/2024/12/20/microsoft-365-copilot-rename/Practical365 Review of Microsoft 365 2024
In our Microsoft 365 review of 2024, we consider the major technical advances in Microsoft's cloud ecosystem together with some changes that might affect operations. Our view of what's important might not match yours, so feel free to comment to voice your opinion. https://practical365.com/microsoft-365-review-2024/Free GitHub Copilot for Visual Studio Code
A free version of GitHub Copilot is available for Visual Studio Code with 2,000 code completions/month. That's a great deal. See https://code.visualstudio.com/blogs/2024/12/18/free-github-copilot for details. I recommend using GitHub Copilot to create PowerShell scripts for Microsoft 365. For more details, seehttps://practical365.com/github-copilot-powershell/Practical 365 Review of 2024
In our Microsoft 365 Review 2024, we consider some of the major technical advances that occurred in Microsoft's cloud ecosystem in 2024 together with some upcoming changes that have the potential to affect tenant operations. As always, our choice of what's important and what's not might not match yours, so feel free to comment and voice your opinion. https://practical365.com/microsoft-365-review-2024/Blocking Microsoft 365 Copilot Making Inferences in Teams Meetings
The Copilot inference and evaluation policy controls if users can ask Copilot in Teams to evaluate the emotions of other meeting participants. It sounds creepy that meeting participants can ask Copilot how someone is feeling based on their contributions to a meeting, but AI is happy to answer unless blocked by policy. Maybe blocking big brother monitoring should be the norm rather than an exception? https://office365itpros.com/2024/12/19/copilot-inference-and-evaluation/
Recent Blogs
- Learn more about changes to contacts we're bringing to Outlook Web App (OWA)!Dec 26, 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 26, 2024
