User Profile
NikolinoDE
Platinum Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Re: Drop-down list with data validation rule
You can do this using Data Validation with a custom formula in Excel. Here’s a step-by-step guide: Select B5 (the cell where the amount is entered). Go to the menu: Data → Data Validation → Data Validation… In the Settings tab: Allow: Custom Formula: enter a formula that checks both the drop-down selection and the amount. Enter the formula Assuming your offer types are in K5, the formula is: =IF(K5="offer1", AND(B5>=200, B5<=1000), TRUE) Test it: Choose "offer1" in K5 and try entering 2000 in B5 → You should see your alert. Choose a different offer type → Any amount is allowed. My answers are voluntary and without guarantee! Hope this will help you.7Views0likes0CommentsRe: Excel formula to take time off from a time range
Understand the logic You have time ranges and corresponding break deductions: Hours Worked (C2) Break Deduction 4:00 – 5:59 15 minutes 6:00 – 7:59 30 minutes 8:00 – 8:59 60 minutes 9:00 – 11:59 90 minutes You want D2 = C2 - break. Correct Excel Formula: =IF(AND(C2>=TIME(4,0,0),C2<=TIME(5,59,0)), C2-TIME(0,15,0), IF(AND(C2>=TIME(6,0,0),C2<=TIME(7,59,0)), C2-TIME(0,30,0), IF(AND(C2>=TIME(8,0,0),C2<=TIME(8,59,0)), C2-TIME(1,0,0), IF(AND(C2>=TIME(9,0,0),C2<=TIME(11,59,0)), C2-TIME(1,30,0), C2)))) Make sure C2 is in time format (e.g., [h]:mm if it can exceed 24 hours). TIME(hours, minutes, seconds) works the same on Mac. My answers are voluntary and without guarantee! Hope this will help you.8Views0likes0CommentsRe: Excel + IRM: Allow VBA to edit protected cells / insert rows without granting "Full Control"
You want a fully stand-alone Excel workbook, protected via IRM/MIP, where VBA can modify protected cells or insert rows, without granting users Full Control (so far i understand). Excel + IRM / MIP Limitations There is no native Excel/MIP setting that allows macros to bypass protected cells under IRM without Full Control. Any attempt to do Sheet.Unprotect or InsertRows via VBA will fail unless the user has Full Control permissions. Why UserInterfaceOnly = True fails UserInterfaceOnly allows macros to edit protected sheets without unprotecting them, but this does not override IRM restrictions. IRM protection is applied at a level below the Excel object model — macros cannot bypass it unless the file is opened by a user with Full Control. There is no Excel-native way to allow macros to bypass IRM/MIP cell protections without Full Control. IRM is designed intentionally to enforce governance, including programmatic access restrictions. UserInterfaceOnly = True only works with normal Excel protection, not IRM-protected workbooks. For fully stand-alone usage, you either: Avoid IRM for the editable areas and rely on sheet protection + passwords, or Use a split model where the IRM master file is read-only, and macros write to a separate unprotected workbook. If your goal is stand-alone VBA automation with cell editing under IRM, Microsoft’s current model does not support it without granting Full Control. Any workaround requires either: Relaxing IRM to allow normal sheet protection, or External service identity (Power Automate, service account) to handle edits under IRM. Excel-native alternatives (stand-alone) Given that you cannot bypass IRM programmatically without Full Control, the closest Excel-native options are: Approach Pros Cons Use normal sheet protection (password) instead of IRM for cells/macros UserInterfaceOnly=True works; macros can insert rows/update formulas Does not prevent copying file outside the domain Split workbook into master (IRM) + editable local sheets Sensitive data remains IRM-protected; users edit local sheet macros Adds complexity, requires sync logic VBA temporarily unprotect with a password Stand-alone macros work on protected cells Password must be embedded (security risk) I apologize that I cannot offer a reasonable solution, but the available options do not allow it. My answers are voluntary and without guarantee! Hope this will help you.2Views1like0CommentsRe: Excel VBA Updates not functioning as intended
Thanks for that thoughtful follow-up — really appreciate the careful read. You’re absolutely right: most of the edits were cleanup (naming consistency, structure, better error handling, and avoiding duplicated Exit Sub branches). But yes, the two points you noted — using Application.InputBox with Type:=1 and separating vDate as an actual Date variable — are what primarily affect the matching logic. The root of the original issue was indeed a type mismatch during the .Find operation. The original code was searching for a text string like "10/01/2025", while the worksheet cells contained true date serials formatted as m/d/yyyy. Since .Find with LookIn:=xlValues compares underlying values, not visible formatting, the string "10/01/2025" didn’t match the numeric date value. By explicitly converting both sides to a true date (vDate = CDate(NM)) and searching for that date value, Excel’s internal comparison works correctly regardless of how the cell is formatted (whether 1/4/2025 or 01/04/2025). The other side benefit of the rewrite is that it ensures formulas pasted into the “Slide Prep” sheet are treated as real formulas — not text — by resetting number formats before assigning them. I’ll definitely share if anything else interesting turns up, but yes, it seems to come down to how .Find interprets date strings versus serials depending on locale and cell formatting. Thanks again for the thoughtful comment — this kind of cross-check is exactly why the community is great. … I rewrite it in a slightly more bulletproof version with error handling and dynamic column handling for Slide Prep. That would make it future-proof….i hope 🙂i dont test it. Option Explicit Sub CMLUpdateV4() Dim wb As Workbook Dim wsData As Worksheet, wsSlide As Worksheet Dim rngDateRow As Range, rngFound As Range Dim dtInput As Variant, NM As String, vDate As Date Dim blnValidDate As Boolean Dim col As Long, lastCol As Long ``` On Error GoTo Cleanup '--- Optimize performance Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual '--- Close other workbooks For Each wb In Application.Workbooks If Not (wb Is ThisWorkbook) Then wb.Close SaveChanges:=False Next wb '--- Ask user for a valid date blnValidDate = False Do dtInput = Application.InputBox("Please Enter File Submission Month as MM/DD/YYYY:", _ "Date Entry", , , , , , 1) If dtInput = False Then MsgBox "Update cancelled by user.", vbInformation GoTo Cleanup End If If IsDate(dtInput) Then NM = Format(CDate(dtInput), "MM/DD/YYYY") vDate = CDate(NM) blnValidDate = True Else MsgBox "Invalid Date Format. Please re-enter in MM/DD/YYYY format.", vbExclamation End If Loop Until blnValidDate '--- Reference worksheets Set wsData = ThisWorkbook.Worksheets("Data") Set wsSlide = ThisWorkbook.Worksheets("Slide Prep") '--- Check if date already exists in Data row 2 Set rngDateRow = wsData.Rows(2) Set rngFound = rngDateRow.Find(What:=vDate, LookIn:=xlValues, LookAt:=xlWhole) If Not rngFound Is Nothing Then MsgBox "Date " & NM & " already exists on the Data sheet. Please review.", vbInformation GoTo Cleanup End If '======================= ' Update Data tab '======================= With wsData .Columns("C:C").Insert .Columns("D:D").Copy .Columns("C:C").PasteSpecial xlPasteAll .Range("C2").Value = vDate .Range("C2").NumberFormat = "m/d/yyyy" ' Clear old content blocks .Range("C3:C6,C16:C19,C29:C32,C42:C45,C55:C58,C68:C71").ClearContents ' Ensure column O is static values .Columns("O:O").Copy .Columns("O:O").PasteSpecial xlPasteValues End With '======================= ' Update Slide Prep tab '======================= With wsSlide .Rows("2:2").Insert .Range("A2").Value = vDate .Range("A2").NumberFormat = "m/d/yyyy" ' Copy formulas from previous row (B3:H3) .Range("B3:H3").Copy .Range("B2:H2").PasteSpecial xlPasteAll ' Reset number format for new formula columns lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column .Range(.Cells(2, 9), .Cells(2, lastCol)).NumberFormat = "General" ' Insert dynamic XLOOKUP formulas B2 to last column For col = 2 To lastCol .Cells(2, col).Formula = "=XLOOKUP(" & .Cells(1, col).Address(False, False) & _ ",Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" Next col ' Convert row 14 to values (per original code) .Rows("14:14").Copy .Rows("14:14").PasteSpecial xlPasteValues End With '--- Success message MsgBox "New File Date " & NM & " added and sheets updated." & vbCrLf & _ "Proceed to Data tab to record Enrollment Line Counts.", vbInformation ``` Cleanup: '--- Restore defaults Application.CutCopyMode = False Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic ``` If Err.Number <> 0 Then MsgBox "An error occurred: " & Err.Description, vbCritical End If ``` End Sub0Views1like0CommentsRe: Formula broke, need commas back. (complicated)
If your formula currently uses ; like: =SUM(A1;B1;C1) Replace it with commas: =SUM(A1,B1,C1) You can do a quick Find/Replace (Ctrl+H) in the formula bar for ; → ,. Without the formula and the digital environment (operating system, Country, storage medium, etc.), unfortunately, we can only guess, and I'm not good at guessing. My answers are voluntary and without guarantee! Hope this will help you.13Views0likes0CommentsRe: Custom Date format Excel 365 ddd/mm/yy
Unfortunately, Excel for Web currently does not support creating or editing custom number/date formats (as far as I know). You can only select from the pre-defined list of formats under “Number Format → Date.” This is a known limitation — even though the web app preserves existing custom formats created in the desktop app. So if you try to type ddd - dd mmm - yy (or your desired Mon - 01 Jan - 25) directly in the web UI, it won’t accept it. Workarounds: Use a helper column with a TEXT formula If you want to stay fully within Excel for Web, use a formula instead: =TEXT(A2, "ddd - dd mmm - yy") Then your output will look exactly the same, e.g.: Mon - 01 Jan - 25 You can even hide the raw date column and just display this formatted version in your to-do list. This is dynamic — if you change the date in A2, the formatted result updates automatically. Use a helper column with a TEXT formula If you want to stay fully within Excel for Web, use a formula instead: =TEXT(A2, "ddd - dd mmm - yy") Then your output will look exactly the same, e.g.: Mon - 01 Jan - 25 You can even hide the raw date column and just display this formatted version in your to-do list. This is dynamic — if you change the date in A2, the formatted result updates automatically. This is a suggestion. My answers are voluntary and without guarantee! Hope this will help you.3Views0likes0CommentsRe: VBA trouble with Export as Fixed Format
Classic Excel-on-Mac VBA snag — especially with ExportAsFixedFormat and SharePoint/OneDrive paths. Sub PrintAllVariablePayoutPDFs_MacSafe() Dim DropDown As Range Dim IndivName As Range Dim NamesList As Range Dim PDFName As String Dim PDFPath As String Dim FullPath As String ' Disable screen updates for speed Application.ScreenUpdating = False ' Dropdown location Set DropDown = Range("G2") ' List of all names (from validation) Set NamesList = Evaluate(DropDown.Validation.Formula1) ' Use a valid local path - ensure it exists first PDFPath = "/Users/dominiccronshaw/Library/CloudStorage/OneDrive-COMPANY/GTM Operations/Commissions/2025 Q3/Payout PDFs/" If Right(PDFPath, 1) <> "/" Then PDFPath = PDFPath & "/" If Dir(PDFPath, vbDirectory) = "" Then MsgBox "PDF output folder not found: " & PDFPath, vbCritical Exit Sub End If ' Loop through all names in list For Each IndivName In NamesList DropDown.Value = IndivName.Value DoEvents ' let Excel update dependent cells ' Refresh PDF name each time (in case K4 changes) PDFName = Range("K4").Value FullPath = PDFPath & PDFName & ".pdf" ' Optional debug check Debug.Print "Saving PDF to: " & FullPath ' Export as PDF (Mac-safe) On Error Resume Next ActiveSheet.ExportAsFixedFormat _ Type:=xlTypePDF, _ FileName:=FullPath, _ Quality:=xlQualityStandard, _ IncludeDocProperties:=True, _ IgnorePrintAreas:=False, _ OpenAfterPublish:=False If Err.Number <> 0 Then MsgBox "Error saving PDF for " & IndivName.Value & vbCrLf & _ "Path: " & FullPath & vbCrLf & _ "Error: " & Err.Description, vbCritical Err.Clear End If On Error GoTo 0 Next IndivName Application.ScreenUpdating = True MsgBox "All PDFs exported successfully!", vbInformation End Sub Make sure the output folder exists. Excel won’t create directories. Avoid spaces or punctuation in folder names if possible. The local OneDrive path on Mac must be mounted under /Users/.../Library/CloudStorage/OneDrive-YourOrgName/. If the script appears to “do nothing”, open the VBA Immediate Window (Cmd+Option+I) to check the Debug.Print output — it will show exactly where it’s trying to save. This is a suggestion. My answers are voluntary and without guarantee! Hope this will help you.0Views0likes0CommentsRe: Excel + IRM: Allow VBA to edit protected cells / insert rows without granting "Full Control"
Goal Supported by IRM? Prevent external sharing Fully supported via IRM/MIP Lock structure but allow VBA edits Not possible without Full Control Allow VBA edits under service context Possible with automation identity Sheet protection with macro access Use Excel protection, not IRM Recommended Enterprise Model Keep IRM label on master workbook (read-only for users). Build a macro-enabled “editor” workbook with internal security (password-protected sheets). Use the macro to write updates through: SharePoint REST API, or Excel Online Connector (Power Automate) using a service account with Full Control. That preserves your data sovereignty and corporate controls without granting users Full Control rights. Simple VBA that POSTs to Power Automate HTTP endpoint This is a lightweight client example. It does not impersonate elevated rights — it just sends data. Sub SendToPA() Dim http As Object Dim url As String Dim sJSON As String Dim dateVal As String url = "https://prod-XX.westus.logic.azure...." ' Power Automate HTTP trigger URL (use managed auth in prod) dateVal = Format(Range("A2").Value, "MM/DD/YYYY") sJSON = "{" & Chr(34) & "user" & Chr(34) & ":" & Chr(34) & Environ("USERNAME") & Chr(34) & "," & _ Chr(34) & "date" & Chr(34) & ":" & Chr(34) & dateVal & Chr(34) & "}" Set http = CreateObject("MSXML2.XMLHTTP") http.Open "POST", url, False http.setRequestHeader "Content-Type", "application/json" ' If using a static shared key (not recommended), include header ' http.setRequestHeader "x-api-key", "<secret-from-keyvault>" http.send sJSON If http.Status = 200 Then MsgBox "Update submitted successfully." Else MsgBox "Error: " & http.Status & " - " & http.responseText End If End Sub Do NOT embed long-lived secrets in client-side VBA. Use short lived tokens or require the VBA call to include user identity so Power Automate can validate and prompt for approval. This is a suggestion. My answers are voluntary and without guarantee! Hope this will help you.1View1like2CommentsRe: Excel VBA Updates not functioning as intended
Option Explicit Sub CMLUpdateV3() Dim wb As Workbook Dim wsData As Worksheet, wsSlide As Worksheet Dim rngDateRow As Range, rngFound As Range Dim dtInput As Variant, NM As String, vDate As Date Dim blnValidDate As Boolean '--- Optimize performance Application.ScreenUpdating = False Application.EnableEvents = False Application.Calculation = xlCalculationManual '--- Close all other workbooks to avoid confusion For Each wb In Application.Workbooks If Not (wb Is ThisWorkbook) Then wb.Close SaveChanges:=False Next wb '--- Ask user for a valid date (InputBox Type:=1 ensures date entry) blnValidDate = False Do dtInput = Application.InputBox("Please Enter File Submission Month as MM/DD/YYYY:", _ "Date Entry", , , , , , 1) If dtInput = False Then MsgBox "Update cancelled by user.", vbInformation GoTo Cleanup End If If IsDate(dtInput) Then NM = Format(CDate(dtInput), "MM/DD/YYYY") vDate = CDate(NM) blnValidDate = True Else MsgBox "Invalid Date Format. Please Re-Enter in MM/DD/YYYY format.", vbExclamation End If Loop Until blnValidDate '--- Reference worksheets Set wsData = ThisWorkbook.Worksheets("Data") Set wsSlide = ThisWorkbook.Worksheets("Slide Prep") '--- Check if date already exists in Data row 2 Set rngDateRow = wsData.Rows(2) Set rngFound = rngDateRow.Find(What:=vDate, LookIn:=xlValues, LookAt:=xlWhole) If Not rngFound Is Nothing Then MsgBox "Date " & NM & " already exists on the Data sheet. Please review.", vbInformation GoTo Cleanup End If '======================= ' Update Data tab '======================= With wsData .Columns("C:C").Insert .Columns("D:D").Copy .Columns("C:C").PasteSpecial xlPasteAll .Range("C2").Value = vDate .Range("C2").NumberFormat = "m/d/yyyy" 'Clear old content blocks .Range("C3:C6,C16:C19,C29:C32,C42:C45,C55:C58,C68:C71").ClearContents 'Ensure column O is static values .Columns("O:O").Copy .Columns("O:O").PasteSpecial xlPasteValues End With '======================= ' Update Slide Prep tab '======================= With wsSlide .Rows("2:2").Insert .Range("A2").Value = vDate .Range("A2").NumberFormat = "m/d/yyyy" 'Copy formulas from previous row (B3:H3) .Range("B3:H3").Copy .Range("B2:H2").PasteSpecial xlPasteAll 'Reset cell format to General to ensure formulas calculate .Range("I2:M2").NumberFormat = "General" 'Insert XLOOKUP formulas Dim col As Long For col = 2 To 13 'B to M .Cells(2, col).Formula = "=XLOOKUP(" & .Cells(1, col).Address(False, False) & _ ",Data!$B$3:$B$80,XLOOKUP($A2,Data!$C$2:$Q$2,Data!$C$3:$Q$80))" Next col 'Convert row 14 to values (per original code) .Rows("14:14").Copy .Rows("14:14").PasteSpecial xlPasteValues End With '--- Confirm success MsgBox "New File Date " & NM & " added and sheets updated." & vbCrLf & _ "Proceed to Data tab to record Enrollment Line Counts.", vbInformation Cleanup: '--- Restore defaults Application.CutCopyMode = False Application.ScreenUpdating = True Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Sub This is a suggestion🙂. Hope it helps.1View0likes2CommentsRe: Accidentally deleted ALL of my files
Check your Recycle Bin Even though you said the deletion bypassed it, it’s still worth verifying: Right-click Recycle Bin → Open. If it’s empty, try the OneDrive online Recycle Bin too: Go to onedrive.live.com Click Recycle Bin on the left. Check both primary and second-stage recycle bins (scroll to bottom → “Second-stage Recycle Bin”). Use File Recovery Tools If the files were hard-deleted, you can often still recover them with data recovery software, provided they haven’t been overwritten. Microsoft’s own tool: Windows File Recovery (free from Microsoft Store) Command example (replace drive letter accordingly): winfr C: D: /regular /n *.* Or use reputable third-party tools. Check Backups and Cloud Versions If you’ve ever used: Windows File History (Control Panel → File History) OneDrive Personal Vault / Backup Time Machine / External backup drive Check there — they might contain snapshots of your user folders. Windows 10/11 often “moves” Desktop, Documents, Pictures, etc. into OneDrive automatically when you set up cloud backup. Even though they looked local, they were being synced to (and stored in) your OneDrive directory. Deleting OneDrive removed the entire structure. When you reinstalled or reactivated OneDrive, it showed only files that were already synced to the cloud — not the ones that never finished syncing or were deleted locally before upload.3Views0likes0CommentsRe: Excel/VBA Worksheet_Change function running old version
What you’re experiencing is almost always not Excel showing a ghost old version, but rather some subtle issue with how the Worksheet_Change event is being triggered or where the code actually resides. You can add a simple Debug.Print to confirm exactly which code is running: Private Sub Worksheet_Change(ByVal Target As Range) Debug.Print "Worksheet_Change fired" MsgBox "Error B" End Sub If you see "Worksheet_Change fired", the code is being executed. If you still see “Error: A”, then some other code or add-in is interfering. Otherwise it would be helpful if the VBA code was included.20Views0likes0CommentsRe: Data Import issue???
Here’s a drop-in replacement formula that will work no matter if the imported data is text or numbers, and will always return a valid drift direction (0–360°). =MOD(VALUE(TRIM(B28))+180,360) My answers are voluntary and without guarantee! Hope this will help you.48Views0likes0CommentsRe: Text box lagging when typing inside
If the text box lag still persists even after disabling hardware acceleration, testing at 100% zoom, and simplifying the workbook, then we’re probably dealing with a rendering or memory leak issue that Excel sometimes develops with text boxes in newer builds (especially Office 365 and Excel 2021+). First, I'd like to point out that it's always good to share information about the problem with the digital environment. Digital environments such as Excel version, Windows (OS) version, storage location (OneDrive, SharePoint, hard disk, etc.), how big the workbook is, allow you to arrive at a solution proposal more quickly and accurately. Whatever the case, here’s some more set of steps you can try… Run Excel in Safe Mode This launches Excel without any add-ins, startup templates, or custom graphics hooks. Close Excel completely. Press Windows + R, type: excel /safe and press Enter. Open your workbook and test typing in a text box. If the lag disappears, one of your add-ins (even a Microsoft one like Power Query or PowerPivot) is interfering. You can then re-enable add-ins one by one under File → Options → Add-ins → COM Add-ins → Go…. Disable “Modern Comments” and other experimental UI features Recent Office builds use web-rendered layers for comments, shapes, and text boxes — which can lag badly on some systems. Try: Go to File → Options → General. Under Comments, choose “Use classic comments” (if available). Restart Excel and test. Clear Excel’s Rendering Cache Corrupt graphics cache files can cause lag in shape/textbox editing. Close Excel. Press Windows + R → type: %appdata%\Microsoft\Excel Delete any files named Excel*.xlb (these store toolbar/cache settings). Restart Excel — it’ll recreate them automatically. There are still a few suggested solutions, but you would have to go into the “regedit” and make changes that might not be helpful if you don't know in advance what digital environment you are dealing with.1View0likes2CommentsRe: Text box lagging when typing inside
You’re not the only one who’s noticed performance changes since Microsoft began integrating “Copilot” and other AI features into Office apps. But I think it is very unlikely that the delay is directly caused by the AI or the copilot logic. The AI integrations (like Copilot, formula suggestions, and autocomplete previews) run mostly in the background and are not active inside drawing objects such as text boxes. However, you’re right that lag began showing up around the same time Microsoft started rolling out these AI-related updates. Hypothesis...in my opinion Excel AI or Copilot trying to “interpret” text box input interact with text boxes I Think its very unlikely. AI features don’t interact with text boxes. Rendering engine update related to Copilot rollout Same subsystem, causes lag on some setups, can be likely. GPU/Display scaling issue Hardware acceleration & DPI scaling are top culprits. You could perhaps try to temporarily disable Copilot and all online intelligence functions completely (without losing Excel access) – just to see if the performance improves...just a thought🙂16Views0likes0CommentsRe: Text box lagging when typing inside
When typing or moving the cursor in a text field, you may experience lag or delays in input, especially in large or complex workbooks. There are several possible causes and solutions. Here are some possible causes. First make sure: Your GPU drivers are up to date. Excel and Office are updated via File → Account → Update Options → Update Now. Change in Excel’s bottom-right corner or via View → Zoom — e.g., 80%, 100%, 120%. that text boxes lag more when Excel is not at 100% zoom (like 90%, 110%, or 130%). So it’s worth setting the workbook zoom to exactly 100% to test. Windows scaling: This is your screen scaling in Windows Settings → System → Display → Scale and layout, where you might have: 100% (recommended for native DPI) 125%, 150%, 175%, etc. When Excel runs on a high-DPI display (like a 4K or Retina screen) and the scaling isn’t handled cleanly, it can make text boxes laggy when editing or moving them. That’s why I mentioned: Right-click Excel → Properties → Compatibility → Change high DPI settings → Override high DPI scaling behavior → Application This tells Windows to let Excel handle its own DPI scaling — which often reduces lag in text boxes and other drawing objects. Too Many Shapes or Text Boxes Each text box is treated like a separate drawing object. If you have dozens or hundreds of shapes, charts, or text boxes, Excel slows down. Try reducing the number of text boxes if possible. Use cell comments, cell notes, or merged cells with text wrapping instead, if feasible. Third-Party Add-ins Some add-ins hook into Excel’s rendering engine and cause lag. Go to File → Options → Add-ins At the bottom, select COM Add-ins → Go… Temporarily uncheck all and restart Excel to test.6Views0likes2CommentsRe: why excel always put value of 1 when adding values in series
Maybe a VBA macro can help you...here is an example Sub AddSeriesFast() Dim cht As Chart Set cht = ActiveSheet.ChartObjects("Chart 1").Chart cht.SeriesCollection.NewSeries cht.SeriesCollection(cht.SeriesCollection.Count).Values = Range("B2:B10") cht.SeriesCollection(cht.SeriesCollection.Count).Name = Range("B1") End Sub My answers are voluntary and without guarantee! Hope this will help you.25Views0likes0CommentsRe: Update Excel formulas between a path in local folder and owncloud in another computer
Map the same drive letter on both computers. That’s the cleanest, no-macro, no-maintenance solution: Excel thinks it’s opening the same files. You can work seamlessly both locally and in OwnCloud. No need to rewrite formulas. Example On both computers, ensure this path works: H:\Fold\Data\physics\summary\daily_summary.xlsx On the OwnCloud computer, that’s just a mapped shortcut to: C:\Users\fulanito\ownCloud\Shared\Data\physics\summary\daily_summary.xlsx Then formulas like: ='H:\Fold\Data\physics\2025\[data_2025.xlsx]Horario'!AV1419 will update perfectly from either machine. My answers are voluntary and without guarantee! Hope this will help you.20Views1like1CommentRe: Excel autofill options not appearing after dragging cell down
Hi Sergei, so far I read In Excel for Web, you can still use left-click drag to autofill values or continue a pattern, but the right-click drag menu and Autofill Options button do not appear….as far as I could read in Excel for the web. I don’t use Excel for web daily, but if that now possible I would be happy if I was incorrect🙂. I'll try it in the next few days...anyway, thanks for the correction👍.12Views0likes1CommentRe: Is it possible to restore a folder to a previous state?
You’re absolutely right that individual files in OneDrive for Business have version history, but folders themselves don’t have independent versioning. However, there is a way to restore a whole folder (and your entire OneDrive) to a previous state — without having to delete it manually. Restore your entire OneDrive to a previous point in time This is the recommended and safest method if you want to roll back all changes in a folder (or your whole OneDrive) to how it was, for example, one week ago. Steps Go to your OneDrive for Business online: https://portal.office.com Click the gear icon in the top-right → Restore your OneDrive (If you don’t see this, your admin might need to enable it — it’s available for Microsoft 365 business and enterprise plans.) You’ll see a timeline and activity log. Use the slider or calendar to choose a point in time (for example, “7 days ago”). Review the list of changes that will be undone (file deletions, renames, edits, etc.) Click Restore. OneDrive will roll back all files and folders to how they were at that point. This includes restoring deleted items, undoing renames, and recovering folder contents. You can later undo this restore if needed — OneDrive keeps track of the restore action. To answer your idea: “Maybe the solution is to delete the folder and then look for a version in the recycle bin — will this work?” Not quite. Deleting the folder and restoring it from the recycle bin will only bring back the folder as it existed at the time it was deleted, not as it existed a week earlier (unless that’s when you deleted it). It won’t recover older versions of the files. My answers are voluntary and without guarantee! Hope this will help you.2Views0likes1CommentRe: Support Required: Finding Matching Number Combinations
You have two sets of numbers, and you want to find which combination(s) of numbers (from one or both sets) sum to a specific target value — right? This is a subset sum problem, which Excel can handle in a few ways (depending on how complex your data is). Formula-only (if you need Excel formulas) If your dataset is small (say ≤ 15 items), you can use array formulas. Suppose: Numbers are in A2:A10 Target sum is in C1 You can test for matching subsets using a brute-force enumeration approach, but Excel formulas alone can’t easily “iterate” through all combinations. However, you can highlight matching combinations with formulas. For example: =IF(SUM($A$2:$A$10)=C1,"Exact Match","") …but that only checks if the whole range sums to the target, not combinations. So for pure formulas, the best you can do is use SUMIF/SUMIFS if you have grouping logic (like matching IDs or categories). If you want use Excel’s built-in Solver This is the most reliable and flexible method for finding combinations that sum to a specific value. Example Say your numbers are in A2:A10, and your target sum is in C1. Steps In column B2:B10, create binary selection cells — they’ll indicate whether each number is included: B2: =IF(A2="","",0) (Then copy down to B10.) Create a sum formula to compute the total of selected numbers: C2: =SUMPRODUCT(A2:A10, B2:B10) Go to Data → Solver (you might need to enable it in Add-ins first). Set up Solver: Set Objective: $C$2 To: Value Of: and enter your target value (e.g. $C$1) By Changing Variable Cells: $B$2:$B$10 Add Constraints: $B$2:$B$10 = binary (you can set this under "Add → bin" for binary values) Click Solve. Solver will set 1s and 0s in column B to identify the combination of numbers in column A that add up to your target. My answers are voluntary and without guarantee! Hope this will help you.0Views0likes0Comments
Recent Blog Articles
No content to show