Adding data ONLY when two cells match across sheets...
Hi all, Apologies: this is complicated... I'm working across x2 sheets in an Excel book. On sheet 2, I have my export page, where I paste the uploaded data for it to fill in sheet 1. On sheet 1, I have the historical data which I need to add to weekly. Sheet 2: Export Page including current formula (based on a working formula on another sheet we use. I need both forename and surname to match on the next page, and automatically paste columns C and D if/when/where they do, then tell me where any names have been missed (new students, etc.) so I can add them to the doc. So if A2 and B2 were on A6 and B6 on sheet 1, that is where the data (C2 and D2) would be pasted, to ensure that the student retains their own points balances. Sheet 1: Overview Page - I intend on the above columns C and D then being put into columns J and K on this page. I'll then just copy and paste the data across into the correct week following analysis, ready for the formula to re-populate those cells with the coming week's data. I switched forename and surname around for readability but can return if that makes it easier (for GDPR purposes, I have removed all names). The formula there had green "Yes"s through the page when I used the original data. When I have gone to update today, everything is a red "No", as you can see, so I clearly don't have the right formulas!! Appreciate your help, as I've been working on this for a couple of weeks now and just can't figure it out!! The formula on the other sheet that I was trying to work from was: =IF(COUNTIF(Table1[@[Name and tutor]],A2)=1,"Y","N")
Visual and dax level optimization that causes report slowdown
Hi, I have this visual as attached, that has lot of visual level filters applied it has a dax measure called data completeness as below Data Completeness = var _total = COUNT('Calendar'[Date])*COUNT(Points[DBName-Point_Id]) var _result = [Count of Exisitng Days]/_total return _result It references a dax called Count of Exisitng Days as below Count of Exisitng Days = Var dates = SUMMARIZE(Data, Data[DBName-Point_Id], Data[Date]) Var Ext_dates = COUNTROWS(dates) return Ext_dates Now, the problem I have here is the performance of this visual is causing report to slow down. Please let me know how to optimize the dax and further steps to increase the performance PFA file here PR-419 - Data Coverage - Copy.pbix Thanks in advance! SergeiBaklan
KB5002653 issue with the Kernel Function GetCommandLineW
Hello, Just to warn about this specific issue, since the installation of the KB5002653 , From an Excel 2016 VBA the function GetCommandLineW give a truncated/different result. Sample Code: Declare Function GetCommandLine Lib "kernel32" Alias "GetCommandLineW" () As Long Declare Function lstrlenW Lib "kernel32" (ByVal lpString As Long) As Long Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (MyDest As Any, MySource As Any, ByVal MySize As Long) Function ReadCmdLine() As String Dim pCmdLine As Long ' Pointer to the string Dim Buffer() As Byte Dim StrLen As Long ' Get the pointer to the command line string pCmdLine = GetCommandLine StrLen = lstrlenW(pCmdLine) * 2 If StrLen Then ReDim Buffer(0 To (StrLen - 1)) As Byte CopyMemory Buffer(0), ByVal pCmdLine, StrLen ReadCmdLine = Buffer End If End Function Before the KB I had : "C:\PROGRA~2\MICROS~2\Office16\EXCEL.EXE excelfile.xlsm /parameter" After the KB I have : ""C:\PROGRA~2\MICROS~2\Office16\EXCEL.EXE" excelfile.xlsm"
Feature Suggestion: Ability to Pin or Freeze Non-Contiguous Rows in Excel
Dear Microsoft Excel Team, I’d like to suggest a powerful productivity feature for future versions of Excel: the ability to pin or freeze non-contiguous rows so they remain visible while scrolling. Currently, “Freeze Panes” only works for rows at the top of the sheet. However, in many real-world use cases — such as tracking project milestones, parent rows in grouped data, or key summary entries — users often need to keep specific non-adjacent rows visible for reference as they scroll through large datasets. Suggested functionality: Let users right-click a row and choose “Pin Row” or “Keep Visible”. Pinned rows could float at the top of the viewport (like sticky headers) even while scrolling down. Ideally, multiple rows could be pinned — not just one — and these could be toggled on or off without disrupting the structure of the worksheet. This feature would dramatically improve usability for complex workbooks and align Excel more closely with modern UI patterns found in web apps and dashboards. Thank you for considering this enhancement! A dedicated Excel user
Very slow and crash For ADODB in version 2503
When I use (Provider=Microsoft.ACE.OLEDB.12.0) for select data by command ( select ,update) in vba macro it take time so long and crash in version 2503 but can work normally in the old verison (2502 or older) and 2016,2019 in my company we have to change from 2016,2019 to 365 all of company and affected for me help me Please
REMOVE A CHECKBOX FROM EXCEL WORKSHEET
I was handed an Excel worksheet that has three checkboxes that I wish to remove but cannot seem to remove them. Excel Help says to right click the Box and then hit Delete. There is no Delete in the ensuing dropdown menu and Delete on the keyboard does not work. Any suggestions?
VBA Coding Help - Multiple Criteria 'If Range'
Hi, I'm relatively new to VBA in Excel and working on some improvements to a form I have created. As part of this, i would like a panel on the bottom of the form to unhide certain rows based on sets of criteria in other cells. I have written out coding that i thought would work but it does not produce the required result so hoping someone here can help me correct it and show where I am going wrong please? AIM: Approval panel rows 47:49 and 51 unhide when any of the following is true - Cell D9 <450001 or cell D10<32000001 or cell H9<75001 or cell H10<50001 (rows 50 and 52:53 remain hidden) Or Approval panel rows 47:51 unhide when any of the following is true - Cell D9 >450000 but <1000001 or cell D10<32000001 or cell H9>75000 but <200001 or cell H10>50000 but <100001 (rows 52:53 remain hidden) Or Approval panel rows 47:53 unhide when any of the following is true - Cell D9 >1000000 but <7900001 or cell D10>32000000 but <39400001 or cell H9>200000 but <3900001 or cell H10>100000 but <236001 The current code i have is: If Range("D9").Value <= 450001 Or Range("D10").Value <= 32000001 Or Range("H9").Value <= 75001 Or Range("H10").Value <= 50001 Then Rows("47:49").EntireRow.Hidden = False Rows("50").EntireRow.Hidden = True Rows("51").EntireRow.Hidden = False Rows("52:53").EntireRow.Hidden = True ElseIf Range("D9").Value >= 450000 And Range("D9").Value <= 1000001 Or Range("D10").Value <= 32000001 Or Range("H9").Value >= 75000 And Range("H9").Value <= 200001 Or Range("H10").Value >= 50001 And Range("H10").Value <= 4100001 Then Rows("47:51").EntireRow.Hidden = False Rows("52:53").EntireRow.Hidden = True ElseIf Range("D9").Value >= 1000000 And Range("D9").Value <= 7900001 Or Range("D10").Value >= 32000000 And Range("D10").Value <= 39400001 Or Range("H9").Value >= 200000 And Range("H9").Value <= 3900001 Or Range("H10").Value >= 100000 And Range("H10").Value <= 236001 Then Rows("47:53").EntireRow.Hidden = False End If If it makes any difference, the cells containing the criteria (D9, D10, H9 & H10) are all formatted to Currency £ with 2 decimal places, and this needs to remain. I'd really appreciate if anyone can help me work this out. Thank you in advance for your time and help.
