excel
42796 TopicsOrganisation Linked Data Type not working
I have a feature table published on Power BI Service that was previously accessible in Excel via the linked data type. However, for the past month, this connection has stopped working, and I'm unable to fetch data from the table. It appears the issue is related to authentication. Even the promoted dataset cannot be found or accessed anymore. This functionality was working fine earlier. I suspect this is due to a change in the authentication or security layer, because when I try to connect to a Power BI dataset in Excel using the "From Organization" option for a PivotTable, I am prompted to authenticate with Azure Active Directory every time. This suggests that persistent authentication is no longer being maintained. I believe this same issue is affecting the linked data type feature in Excel. Could you please clarify Has there been a recent change in the authentication model? Is there a workaround or fix to restore access to the linked data types from Power BI in Excel? Is the linked data type feature being deprecated? II tested on both current channel and beta channel If it's still working on your tenant, please do let me know or if its broken on your side as well , please update so that I can progress the issue accordingly. Thanks20Views0likes1CommentFilter overwhelmed? Not showing all data
Hello! I've developed an application for my workplace, logging absences through the year on a backend spreadsheet and presenting the data on a frontend spreadsheet, with a filter available as managers requested the ability to review data based on names, dates, shift times, etc. It is a lot of data, there are 1833 absences logged over the last 2 weeks. The frontend filter continues to work as expected, and there is practically no delay in presenting the data. However, I've noticed that some of the columns are presented as blank. If I open the backend spreadsheet, suddenly the frontend filter shows all columns as populated. I'm not sure how to lessen the load on my filter, as I am assuming there's excess processing afoot. Any ideas worth a try, thank you! Filter code (crunchy, I know. Eek.) The date variables were my only idea to simplify the want to search from, to, and between dates depending on blank cells: " =IFERROR( LET( fromdate,('BACKEND.xlsm]RECORD'!E:E>=G7)*('BACKEND.xlsm]RECORD'!F:F>=G7), todate,('BACKEND.xlsm]RECORD'!E:E<=H7)*('BACKEND.xlsm]RECORD'!F:F<=H7), tweendate,(('BACKEND.xlsm]RECORD'!E:E>=G7)*('BACKEND.xlsm]RECORD'!E:E<=H7))+(('BACKEND.xlsm]RECORD'!F:F>=G7)*('BACKEND.xlsm]RECORD'!F:F<=H7)), blanks,FILTER('BACKEND.xlsm]RECORD'!A:L,('BACKEND.xlsm]RECORD'!B:B<>"")*('BACKEND.xlsm]RECORD'!B:B<>"Co-Ordinator Entering Leave:")*IF(ISBLANK(B3),1,'BACKEND.xlsm]RECORD'!B:B=B3)*IF(ISBLANK(C3),1,'BACKEND.xlsm]RECORD'!C:C=C3)*IF(LEN(E7)>0,'BACKEND.xlsm]RECORD'!D:D=E7,1)*IF(ISBLANK(I3),1,'BACKEND.xlsm]RECORD'!G:G=I3)*IF(ISBLANK(J3),1,'BACKEND.xlsm]RECORD'!J:J=J3)*IF(ISBLANK(K3),1,'BACKEND.xlsm]RECORD'!L:L=K3)*SWITCH(LEN(G7)+LEN(H7),0,1,10,tweendate,5,IF(LEN(G7)=0,todate,fromdate))), IF(blanks=0,"",blanks)),"No Results") " The data / filter results are listed below, of course personal details removed. Columns A, H, I, and K are all presented as blank until the BACKEND spreadsheet is opened (which defeats the purpose of this filter). BACKEND Data RECORDED Co-Ordinator Entering Leave: Employee Name: Date Called: Absence Start Date Absence End Date Shift Time Affected Actioned? Rostered? Reason for Leave Comments Covered by: TRUE John Smith Don Douglas 08/06/2025 09/06/2025 09/06/2025 0900-1730 NO Personal Leave (PL) Sick, recuperating from injury. Note pending. TRUE Jane Smith Deb Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Did not feel well, Victoria moved to cover. Victoria Veck Jane Smith Doug Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Victor Veck FILTER Result ENTERED Co-Ordinator Entering Leave: Employee Name: Date Called: Absence Start Date Absence End Date Shift Time Affected Actioned? Rostered? Reason for Leave Comments Covered by: John Smith Don Douglas 08/06/2025 09/06/2025 09/06/2025 0900-1730 Personal Leave (PL) Jane Smith Deb Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Victoria Veck Jane Smith Doug Douglas 09/06/2025 09/06/2025 09/06/2025 0600-1430 Sick / Injured Victor Veck30Views0likes1CommentShortcut to jump to formula bar in Excel 365
I would like to know if there is a keyboard shortcut to jump to the formula bar in MS Excel 365. The shortcut used to be F2 + Ctrl A in previous versions of Excel, but it is no longer working in Excel 365. I know that I could turn off "Allow editing directly in cells", but that is not what I'm looking for. I want to edit formulas directly in the cell, but be able to switch to the formula bar if needed. Would really appreciate it if someone could help me out with this one. Thanks!47KViews1like8CommentsData table in Excel copy and paste it within the same sheet
Hi all, I'm working with a one-variable data table in Excel and would like to copy and paste it within the same sheet while keeping it fully functional — including the array behavior and input cell reference. Currently, when I copy and paste the data table to a new location: The pasted result shows only static values (numbers). The data table array/formula disappears. I have to go back to Data > What-If Analysis > Data Table and re-enter the input cell reference each time. Is there a way to duplicate a data table in the same worksheet without needing to manually reset the input cell reference every time? Any suggestions or workarounds would be appreciated. Thank you!16Views0likes1CommentProblems with a SUMIFS formula
I'm really having some trouble with Excel in that it's not behaving logically. Essentially, what I'm trying to do is conditional sums for spending categorizations. But when I use the formula, I get an error that I can't figure out, because when I use the Function Wizard, it seems to work nicely. But yet, it comes up with this error, and I can't find any information sufficient enough to solve this. Here is the formula: =SUMIFS(B2+H4:H15,H4:H15,"Netspend Account",C2,"Netspend Account")34Views0likes1CommentText Extract - 2
Dear Experts, I have a txt file as attached in Excel, from where need to extract the meaningful data like below(Logic and what to extract is like below) Each cell Identity has a MeasQuantityresults for rsrp,rsrq and sinr, followed by the rsrp,rsrq and sinr for ssbIndex for the same cellIdentity as below till a new cellIdentity is measured and so on.. :- By the way , if someone can educate also on what format would these(txt) be? in what language do we use these nested kind of pattern.. any easier method to read them ? Any PQ or legacy formula Welcomed ... as I don't have REGRX in my excel , but I do have Python supported in my excel version, so regex in python also more than Welcome to achieve the output. Thanks in Advance!! Br, AnupamSolved219Views0likes10Commentsnested xlookup
Hello My input data is my desired output is how to achieve this result, basically, i need to get the values for whatever date i enter in the column header. it needs to pull the values corresponding to input table. i tried using nested xlookup. please guide me. PS: i need to use in webexcel (excel 365) Thanks Kalyan,Solved1.2KViews0likes9CommentsSUMIFS excluding duplicate values
Hello, The goal is to sum the job value (column D) for each machine (column B). The issue is that a value will be included more than once because a job reference is listed more than once (column C). How do I write a formula to sum the job value with the Machine name as the criteria and filtering through the job reference to only sum the value of that job once? This is what I have so far but it's either returning 0 or the SPILL error: =IF(B2:B350="Durst1",SUMPRODUCT(1/COUNTIF(C2:C350,D2:D350&""),D2:D350),0) Many thanks for your help.Solved98KViews0likes18CommentsHelp with XLOOKUP
=XLOOKUP([@Name],'[May 2025 Metrics.xlsx]Individual Tech Summary'!$A:$A,'[May 2025 Metrics.xlsx]Individual Tech Summary'!$D:$D) Let me say thanks in advance for any help with this. I have to pull numbers for each of my technicians into a spreadsheet from multiple excel files that are sent each month. I muddled through a few web pages on XLOOKUP and was able to get the above to work. The screenshot is just a snip-it of the file I'm working in, and it goes out to column U with all the various categories (nothing crazy). Since I have to do this for monthly reports and the quarterly report, I have the same little snip it copied 4 times on the same worksheet (tab?). It's one thing to have to set this up once copying the above formula and just having to change what destination column is referenced "!$D:$D", but having to change the destination file name twice for each column that is using this formula is killing me as the file names are always changing. Is there a way I can modify this to use a cell reference to somewhat automate this? I would like to be able to type in the file name into say cell D2 and it use that to complete the formula. Example: =XLOOKUP([@Name],'[D2]Individual Tech Summary'!$A:$A,'[D2]Individual Tech Summary'!$D:$D) and on cell D2 it would have either "[May 2025 Metrics.xlsx]" or "May 2025 Metrics.xlsx". I hope this makes sense? That way I can just update the name for June 2025, July 2025, Q3 2025, Q4 2025, March 2029... you get the idea I hope.Solved77Views0likes4Comments