Forum Widgets
Latest Discussions
How to distribute the value of one cell evenly in a sequence for the value of another cell?
Hi there, I'm trying to evenly distribute numbers (not a set value) to help with crocheting - taking the math out for testers/creators right now I have the blue row working as a sequence, however I can't work out how to divide it equally So for the example above; there are 20 rows, there needs to be 8 increases (1 every 2.5 rows). How do I get that information to show in the column next to my sequence (Rows)? If it's even possible - haha. I would like it to automatically work out like the below (manually entered) Thank youSUMIF function error
I have a table where Column 7 contains the "Due Date" of the value in Column 9. Some of the "Due Dates are in the future (outside the reporting date) so I created the following formula: =SUMIF((R5C9:R15C9),(R5C7:R15C7<=Max_Due_Date),R18C12) to obtain the total amount within the reporting period -- i.e. less than or equal to the last date in the reporting period (a named item containing the calculated date of the first of the current month, plus the number of days in the month, minus 1), and want the result in Row 18 Column 12. I get the error code "SPILL#", with a "hatched lines" box immediately below the formula cell. I am not trying to get a Pivot Table, as suggested by the unhelpful "Help"! I have used "SUMIF" many times in the past without problems. H E L P , please
Filter Pivot Table
Ok, i have a filtered pivot table and wonder if there is a way to have an option to select no items and have value show 0 In this case, there are none that have been improperly modified, therefore i have none to select, as all the ones listed or correct. I need a way to make it show 0. Here is show 36, which is all of them, since i'm not sure how to do this so i can select none and show 0?
Excel VBA Updates not functioning as intended
Afternoon all, Attaching a deidentified copy of a metrics file. I built a macro which does two main things once a user clicks the "Update Sheet" icon in the ribbon. asks for a date in the format "MM/DD/YYYY" Compares the date entered to the format requirement, & if appropriately formatted, proceeds to make updates to the two tabs of the sheet. As a safeguard, the macro also checks if the entered date is found in Data tab, row 2. If yes, a msgbox should notify the user of the existence of the date, and exit sub. However with a user entered date which already exists, my macro continues on to make updates of inserting a new column C on Data tab, as well as inserting a new row 2 on Slide Prep tab. I've checked all the formatting and can't find why it doesn't find a match. Also, on the Slide Prep tab it when inserting new formulas, the formulas added to I2:M2 all present as text, and do not auto calculate - again, nothing i can locate as the root source of the surprise. I've attached a copy with the incorrect duplication of 10/01/2025 as two columns on Data tab, and as two rows on Slide Prep tab. VBA Code below: Option Explicit Sub CMLUpdateV2() Dim Wb As Workbook Dim Ws As Worksheet Dim ICount As Integer Dim iRow As Integer Dim Row As Object Dim RowAddress As String Dim RowRange As String Dim NM As String Dim DR As Range 'Data Date Tab Date Row(2) Dim DC As Range 'Date Found Range Dim strDateInput As String Dim blnValidDate As Boolean Application.ScreenUpdating = False For Each Wb In Application.Workbooks If Not (Wb Is Application.ThisWorkbook) Then Wb.Close End If Next blnValidDate = False 'Determine if input value is valid format, re-enter if not Do strDateInput = InputBox("Please Enter File Submission Month as MM/DD/YYYY:", "Date Entry") 'If strDateInput = "" Then ' MsgBox "No Date Entered, update cancelled.", vbInformation ' Exit Sub ' End If If IsDate(strDateInput) Then If Format(CDate(strDateInput), "MM/DD/YYYY") = strDateInput Then blnValidDate = True Else MsgBox "Invalid Date Format or No Date Entered. Please Re-Enter in MM/DD/YYYY format." blnValidDate = False End If Else MsgBox "Invalid Date Format or No Date Entered. Please Re-Enter in MM/DD/YYYY format." blnValidDate = False End If Loop Until blnValidDate NM = strDateInput 'Determine if valid input is already present on sheet Set DR = ThisWorkbook.Worksheets("Data").Range("2:2") Set DC = DR.Find(What:=NM, LookIn:=xlValues, LookAt:=xlWhole) If Not DC Is Nothing Then MsgBox "Date entered is already present. Please Review Date.", vbOKCancel Exit Sub Else 'Update Data Tab with new column and formula updates With ThisWorkbook.Worksheets("Data") .Columns("C:C").EntireColumn.Insert .Range("D:D").Copy .Range("C:C").PasteSpecial xlPasteAll .Range("C2").Value = NM .Range("C3:C6,C16:C19,C29:C32,C42:C45,C55:C58,C68:C71").ClearContents .Columns("O:O").Copy .Columns("O:O").PasteSpecial xlPasteValues End With 'Update Slide Prep Tab with New row and formula updates With ThisWorkbook.Worksheets("Slide Prep") .Rows("2:2").EntireRow.Insert .Range("A2").Value = NM .Range("A2").NumberFormat = "m/d/yyyy" .Range("B3:H3").Copy .Range("B2:H2").PasteSpecial xlPasteAll .Range("B2").Formula = "=XLOOKUP(B$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("C2").Formula = "=XLOOKUP(C$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("D2").Formula = "=XLOOKUP(D$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("E2").Formula = "=XLOOKUP(E$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("F2").Formula = "=XLOOKUP(F$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("G2").Formula = "=XLOOKUP(G$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("H2").Formula = "=XLOOKUP(H$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("I2").Formula = "=XLOOKUP(I$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("J2").Formula = "=XLOOKUP(J$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("K2").Formula = "=XLOOKUP(K$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("L2").Formula = "=XLOOKUP(L$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Range("M2").Formula = "=XLOOKUP(M$1,Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" .Rows("14:14").EntireRow.Copy .Rows("14:14").EntireRow.PasteSpecial xlPasteValues End With MsgBox "New File Date " & NM & " added and sheets updated. Proceed to Data Tab to record Enrollment Line Counts." End If End SubExcel + IRM: Allow VBA to edit protected cells / insert rows without granting "Full Control"
We have a corporate Excel workbook used for IT service pricing that contains sensitive data. To protect this information, we enabled IRM (Information Rights Management), restricting access to our corporate domain without granting the “Full Control” permission, and enabled programmatic access to allow VBA automation. Goals Prevent the workbook from being shared or opened outside the company domain; Keep certain sheets, rows, and columns protected (formulas, formatting, etc.); Allow VBA macros to edit protected cells, insert new rows, and update contents without unprotecting the sheet — or, alternatively, to temporarily unprotect and reprotect via VBA. Issue Even with IRM enabled and programmatic access allowed, VBA cannot write to protected cells or insert rows. If the user has “Full Control” permission under IRM, everything works — but then the user can also disable IRM protection, which defeats the security purpose. Question Is there any additional configuration, specific permission, or MIP/DLP policy that allows VBA to perform full programmatic operations within the domain without requiring “Full Control” for users? Environment Microsoft 365 E3 Excel Desktop (64-bit) IRM/MIP enabled via Microsoft Purview Stored on SharePoint Online Suggested tags (add via the forum UI) Excel, VBA, IRM, Microsoft Purview, MIP, DLP, SharePoint Online, Information ProtectionGolf league form
I keep each week's score on a excel spread sheet I developed, and I have to do meltable inputs each week. One is the score. I have 28 weeks of play and form input. All works well except for when I input the current week the form is set up so the last 3 rounds are what your handicap is fingered on. So, week one thru 3 will be your starting handicap. Week 4 input is made, and I have to delete field 1 so as to still only have 3 numbers that the handicap figures from. The form is setup for 28 weeks, but the handicap is only 3 weeks at a time. Is there a formula that will auto drop the last uncounted score automatically. For example, field f1, g1, h1, would be your 1st 3 scores that establish your handicap to start the session the 4th round would be g1, h1, and i1. How can I auto delete f1, so I don't have to manually do it every time.
Data Import issue???
I am working on a drift formula for items being pushed by wind and waves on the water. I imported a text file of weather data, formatted all the data to numbers, and built an "if" formula to convert the wind direction into drift direction. Wind from 360 degrees (north) would cause an item to drift 180 degrees (south). The problem is the formula sees everything as false. Is this a formatting issue with the imported text? =IF(B28<180,B28+180,B28-180)
Solver in Excel - Variable Limitation
Hi All, I built a model in Excel to solve 1d Cutlist optimization, model works great and gives optimal results But when there is complex cutting patterns that results in more than 250 cutting combinations model totally fails fails due to the limitation of solver to handle more than 250 variables with integer constraints. using simplex LP, I created model years ago but unable to use it Hoping Microsoft has this in the list to fix this limitation in coming versions ?Stockhistory returning #BLOCKED! error
I started getting this error a couple of days ago. Summary of the problem and what Excel Tech Support did but didn't fix the problem. 1. I get #BLOCKED! error with my stockhistory formula but stock data type formula is still working because I can still pull the stock price from Microsoft online repository (Bing) 2. In my local copy of Excel, the group data types has gone missing. I can retrieve it via customising ribbon but the icons for stock, currency, etc. is not there. 3. In the web version of Excel, the data types group is there but I still get the same error as (1) 4. Yesterday, the Microsoft support person reinstalled my copy of Office, checked the privacy settings in "Connected Experiences" but did not fix the problem. They said it was going to be escalated but I didn't get a call back at the agreed time. I went back to Tech Support via chat and the guy said that stockhistory function has been removed from Excel. Clearly clueless. 5. Is this a license problem?
VBA trouble with Export as Fixed Format
Hi folks - I'm struggling to get this code for export as fixed format to work. My aim is to use it to churn through 150 names in a dropdown (data validation using a reference to a named range called "Names") and print PDF earning statements for each person. I have the file stored on Sharepoint and when I run it there it seems to churn through the names, but not produce the PDFs (at least not that I can find). When I move the file to my local device (Mac), it throws an error and highlights this part as the issue. Any help is most appreciated. I'd also love to learn how to fire this from the version hosted on Sharepoint, but from this article it seems I'll need some more VBA. Sub PrintAllVariablePayoutPDFs() Dim DropDown As Range 'xRg Dim IndivName As Range Dim NamesList As Range 'List of COMPANY Employees Dim PDFName As String Dim PDFPath As String Dim FullPath As String ' Turn off screen updating Application.ScreenUpdating = False ' Set where dropdown resides Set DropDown = Range("G2") ' Set where list of names resides Set NamesList = Evaluate(DropDown.Validation.Formula1) ' Set PDF path every quarter PDFPath = "/Users/dominiccronshaw/Library/CloudStorage/OneDrive-COMPANY/GTM Operations/Commissions/2025 Q3/Payout PDFs/" 'Set PDFName PDFName = Range("K4").Value ' Construct the full file path ' FullPath = PDFPath & PDFName & ".pdf" 'Steps through options in the dropdown with people's names For Each IndivName In NamesList DropDown = IndivName.Value 'Prints each sheet as PDF with unique file name ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ FileName:=PDFName, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False Next 'Turn on screen updating Application.ScreenUpdating = True End Sub
