User Profile
NikolinoDE
Platinum Contributor
Joined Jul 08, 2020
User Widgets
Recent Discussions
Re: Help with add-ons for excel
YouTube is an excellent source for visual tutorials. The link they shared is a good starting point for seeing how to install and manage add-ons. Video tutorials are indeed a quick way to get your bearings, and I think mathetes suggestion is very good for acquiring basic knowledge. In addition to mathetes excellent video link, here are some more links with information about add-ons. Add or remove add-ins in Excel How to Enable Data Analysis in Excel Power Pivot - Gambaran Umum dan Pembelajaran How to Add a Power BI Add-In in Excel Excel Add-ins Tutorial: Create an Excel task pane add-in (Create Your Own Add-In) My answers are voluntary and without guarantee! Hope this will help you too.2Views0likes0CommentsRe: Formula for adding multiple sheets
m_tarler’s advice is excellent. If you have control over how your workbook is set up, combining all your transactions into one sheet (with an extra column for something like “Sheet origin” or “Month”) is absolutely the cleanest and most reliable approach. Then you can just use a PivotTable or SUMIFS to sum by name of place. However, if you cannot change the multi-sheet structure (for example, if each sheet is generated separately and you must keep them apart), here’s an alternative step-by-step formula that builds on m_tarler’s VSTACK idea but is broken out for clarity: =LET( AllData, VSTACK(Sheet1:Sheet5!A2:H1000), Names, CHOOSECOLS(AllData, 3), Withdrawals, CHOOSECOLS(AllData, 5), GROUPBY(Names, Withdrawals, SUM, 0, 0)) Replace Sheet1:Sheet5 with your actual sheet names (e.g., Sheet1:Sheet10). Adjust A2:H1000 to cover all your data rows on each sheet. The GROUPBY function (available in Excel for Microsoft 365) will automatically list each unique name in column C and sum the withdrawal amounts from column E. If you don’t have GROUPBY or VSTACK (older Excel versions), you can use a traditional approach: Create a list of all unique names from all sheets using UNIQUE(VSTACK(...)). Then use SUMIF across sheets, like: =SUMIF(Sheet1!C:C, A2, Sheet1!E:E) + SUMIF(Sheet2!C:C, A2, Sheet2!E:E) + ... (This becomes tedious with many sheets, so the VSTACK method is far better.) Finally, when adding text in formulas, make sure names match exactly (e.g., “Joe Doe” vs “Joe Doe” with extra spaces). Using TRIM around names can help avoid mismatches. My answers are voluntary and without guarantee! Hope this will help you too.0Views0likes0CommentsRe: MO 365 Excel - Difficulty Changing a Date format
m_tarler has made a very good point. If standard date formatting isn't working, the values are likely stored as text, not as actual dates. m_tarler’s suggested method — Text to Columns — is typically the most efficient fix: Select the date column. Go to Data > Text to Columns. Choose Delimited > Next > Uncheck all delimiters > Next. Under Column data format, select Date: MDY > Finish. This converts the text to real date values. After that, you can format the cells as needed: Right-click > Format Cells > Custom > Type: yyyy/m/d That will give you 2025/1/31. If for some reason Text to Columns doesn't work, you can also use a formula in a helper column: =DATE(RIGHT(A1,4), LEFT(A1,FIND("/",A1)-1), MID(A1,FIND("/",A1)+1, FIND("/",A1, FIND("/",A1)+1)-FIND("/",A1)-1)) But m_tarler’s approach is simpler and should resolve the issue in most cases. My answers are voluntary and without guarantee! Hope this will help you too.2Views0likes0CommentsRe: How can I overcome the HYPERLINK Functions 255 character limit?
You can use Alternatively, a small VBA macro to insert a "real" Excel hyperlink, which has a much higher character limit (up to the cell's 32,767 character limit) . This is ideal for your JotForm links because it will automatically update based on your spreadsheet data whenever you run it. Here's a sample VBA code that creates a clickable link in the selected cell: Sub CreateLongHyperlink() ' This macro creates a hyperlink in the currently selected cell ' It bypasses the 255-character limit of the HYPERLINK formula Dim targetCell As Range Dim longURL As String Dim friendlyName As String ' Set the cell where you want the hyperlink Set targetCell = ActiveSheet.Range("A1") ' <-- CHANGE THIS TO YOUR CELL ' --- IMPORTANT: Build your long URL here by combining strings --- ' This example assumes your URL parts are in cells B1, B2, etc. ' Replace this with your own logic to build the JotForm URL. longURL = Range("B1").Value & Range("B2").Value & Range("B3").Value ' Set the text you want to see in the cell (e.g., "Fill out Form") friendlyName = "Open Pre-populated Form" ' Delete any existing hyperlink in the cell On Error Resume Next targetCell.Hyperlinks.Delete On Error GoTo 0 ' Add the new, long hyperlink to the cell targetCell.Parent.Hyperlinks.Add Anchor:=targetCell, _ Address:=longURL, _ TextToDisplay:=friendlyName ' Optional: Confirm it worked MsgBox "Hyperlink added successfully!", vbInformation End Sub Hope that helps.0Views0likes0CommentsRe: Pull data from other tab
You're trying to pull the current PTO balance based on today's date, not the last entry of the year. Here’s how to do it, assuming your data layout: Example layout on your PTO tab Let’s say: Column A = Date (each row is a PTO accrual date or month-end balance date) Column B = PTO Available Hours Example: A (Date) B (PTO Available) 1/31/2026 10.5 2/28/2026 15.0 3/31/2026 18.0 ... ... 12/31/2026 120.0 Goal on Calendar tab Pull the PTO available for the most recent date on or before today. Solution formula (on Calendar tab) =XLOOKUP(TODAY(), 'PTO tab'!A:A, 'PTO tab'!B:B, , -1) How it works: TODAY() = today’s date 'PTO tab'!A:A = date column on PTO tab 'PTO tab'!B:B = PTO hours column -1 = next smaller item (exact match or next smaller date if exact not found) This returns the PTO balance for the latest date ≤ today. If your dates are not sorted ascending Use this instead (works even if unsorted): =LOOKUP(2, 1/('PTO tab'!A:A <= TODAY()), 'PTO tab'!B:B) Or with MAXIFS (if you have Office 365 or Excel 2019+): =MAXIFS('PTO tab'!B:B, 'PTO tab'!A:A, "<="&TODAY()) But MAXIFS gives the max balance on/before today, not necessarily the latest date's balance — use only if PTO always increases. If PTO resets yearly and you have multiple years Add a year check: =XLOOKUP(1, ('PTO tab'!A:A <= TODAY()) * (YEAR('PTO tab'!A:A) = YEAR(TODAY())), 'PTO tab'!B:B, , -1) Hope that helps. Explaining with help from AI🙂0Views0likes0CommentsRe: Onedrive Web search : "Location" column always shows "My Files" for folders path
When two folders share the same name but exist in different locations (e.g., Documents > Projects > Archive vs. Pictures > 2024 > Archive), the search results make them indistinguishable. Users cannot tell which folder is which without clicking into each one. Until Microsoft fixes this: Look at the file results — If a file inside the folder appears in search, its location column will show the correct path, indirectly revealing the folder's location. Use the breadcrumb — Click on a folder result, then look at the navigation bar at the top of OneDrive to see its actual path. Use OneDrive sync + File Explorer — Search locally through File Explorer (if you sync folders) where the path column works correctly for folders. Hope that helps clarify things a bit🙂8Views0likes0CommentsRe: OneDrive changing dates
It’s a very valid question, especially given how long this issue has been around. Just to clarify upfront, I’m not a Microsoft employee — just a fellow OneDrive user like you. So I can’t provide any official statements or timelines on if or when this will be fully addressed. From what’s been discussed in Microsoft’s own Q&A forums, this doesn’t appear to be a simple bug but rather part of how OneDrive currently works. It often treats uploaded files as new files, meaning the “Date Created” reflects the upload time, even though the EXIF “Date Taken” may still exist in the file. At the same time, multiple users report that OneDrive ignores or inconsistently uses the “Date Taken” metadata for sorting, especially after uploads or certain actions. So while newer features (like the Photos experience) may display EXIF data better, the underlying behavior hasn’t really changed in a consistent way. The most reliable workarounds are still: Avoid using the mobile app for large archival uploads Transfer files via PC into a OneDrive-synced folder (this tends to preserve metadata better) Or use third-party tools/apps if you need more control over dates Unfortunately, there’s still no clear indication that a full fix (like a “preserve EXIF dates” option) is coming anytime soon. If you want to push for change, the best route is to submit feedback through OneDrive or vote on existing threads in Microsoft’s feedback portal. Hope that helps clarify things a bit🙂7Views0likes0CommentsRe: Spellcheck Not Working for Some Words?
You’re right—words like Manila, Asia, and Santiago do seem obvious, so it’s frustrating when spellcheck doesn’t flag them. What you’re experiencing is actually a known behavior in many spellcheckers (including Excel and Word): they often ignore fully capitalized words or words that start with a capital letter, assuming they are proper nouns (like names of people, places, or brands). Since your misspellings are in a column labeled Region and Sub-Region and start with capital letters (e.g., Aisa, Manilla, Santigo), the spellchecker likely treated them as intentional names and skipped them. This can be helpful in some contexts (avoiding false positives on legitimate names) but clearly causes issues when proper names are actually misspelled. How to prevent or catch this in the future: 1. Adjust spellcheck settings (in Excel/Word): Go to File > Options > Proofing Uncheck “Ignore words in UPPERCASE” and/or “Ignore words that contain numbers” Note: This may increase false positives, but it will flag those capitalized misspellings. 2. Use Data Validation (recommended for your workbook) - As mathetes thoughtfully suggested, this is often the most reliable approach for terms you use frequently. You can create a master list of correct region/sub-region names (e.g., Asia, Manila, Santiago, Tangier) and then apply Data Validation as a dropdown list in your worksheet. This prevents typos from being entered in the first place and is much more dependable than spellcheck for proper nouns. 3. Run a manual check on proper nouns as a final step: Sort the column alphabetically and scan visually, or use a formula like =A1<>B1 if you have a correction reference list. You’re absolutely right to want accurate place names, and your approach is thoughtful. My answers are voluntary and without guarantee! Hope this will help you.4Views0likes0CommentsRe: Files are gone, folderstructure is there
I fully understand your concern about data integrity — this is very worrying. The fact that storage is still used but files are invisible is a key clue. Since standard restore and recycle bin checks didn't help, I recommend you ask Microsoft support specifically for an engineering lead to repair the file index from server-side backups. Local data recovery software on your PC may also recover traces of the missing files. You are not alone with this issue. I hope a real solution comes soon.11Views0likes0CommentsRe: Files are gone, folderstructure is there
Thank you for your detailed testing and for sharing your contact with Microsoft support (Mr. Jalaj G). Your suggestion about a server-side index restore is exactly right. Since your quota still shows used space, the physical data exists. When you speak with support again, you can politely say: "I request that an engineering lead performs a manual restoration of my file index from server-side backups, because standard restore tools show blank entries and cannot select items." Local recovery software (option 4) is currently your best independent path. Please keep us updated, and thank you for helping the community.8Views0likes0CommentsRe: Add secure additional workbook and worksheet protection Use MS account
You're right — Microsoft has definitely heard this request. There are old UserVoice threads and TechCommunity posts asking for exactly what you described: protection tied to a Microsoft Account, not a password stored in the file. For reference, you can look back at some of those discussions: TechCommunity Discussion: "Protecting an Excel File" – a thread where users discuss the limitations of current workbook protection. Hashcat Forum (Technical): This shows how easily worksheet protection passwords can be cracked by removing the XML node — proof that current "protection" is not real security. Microsoft Q&A on Password Security: Official discussion confirming that worksheet/workbook protection "isn't intended as a security feature" and that encryption is the only real protection. Newsoftwares Security Analysis: A detailed breakdown showing the difference between encryption (secure) and worksheet protection (just an XML flag, trivially bypassed). The fact that Microsoft hasn't implemented account‑based protection in standard Excel suggests it's either a deliberate product differentiation (keeping IRM for paid enterprise plans) or a technical challenge around key management without forcing everyone into the cloud. Your original answer was correct — no such feature exists today outside of Purview / Azure RMS. If I were replying to the user, I'd acknowledge their frustration, agree that the password method is broken, and then offer the closest practical workarounds: Store the file in OneDrive for Business or SharePoint, share only with specific Microsoft Account‑authenticated users, and combine that with "Encrypt with Password" as a second layer. If they have Microsoft 365 Business Premium or E3/E5, show them how to use "Restrict Access" (IRM) without full Purview governance. Suggest they submit or upvote a feature request in Microsoft Feedback (inside Excel → File → Feedback). What you're asking for is completely reasonable from a security standpoint. It's just not something Excel can do natively yet without an enterprise license. If Microsoft ever builds that into personal Office, it would genuinely fix a long‑standing vulnerability. My answers are voluntary and without guarantee! Hope this will help you.1View0likes0CommentsRe: Not understanding XLOOKUP arguments as they apply here
Yes — in practice, FALSE behaves the same as 0 for match_mode in XLOOKUP, meaning exact match only. When you write: =XLOOKUP(A2, B2:B10, C2:C10, , FALSE) Excel interprets FALSE as 0, which means: Exact match (default behavior). Even though it works, FALSE is not officially documented for match_mode in XLOOKUP. In older functions like VLOOKUP, FALSE explicitly meant exact match. In XLOOKUP, Microsoft defines: 0 → exact match (default) -1, 1, 2 → other match types Use 0 instead of FALSE for clarity and consistency: =XLOOKUP(A2, B2:B10, C2:C10, , 0). If someone accidentally wrote match_mode = TRUE, that would be 1, which changes behavior entirely (exact or next larger). So FALSE is “safe” because it equals 0, but TRUE would be dangerous. That’s another reason to stick with 0. My answers are voluntary and without guarantee! Hope this will help you.2Views0likes0CommentsRe: Not understanding XLOOKUP arguments as they apply here
Golden rule: lookup_value = what you know (typed in a cell) lookup_array = where that value exists (single column/row in your data) return_array = where the answer is (same size as lookup_array, different column/row) XLOOKUP syntax =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) Argument What it means lookup_value What you are searching for (e.g., a product ID, name, or number) lookup_array The column/row where Excel should look for that value return_array The column/row with the value you want to return (same size as lookup_array) [if_not_found] Optional: What to show if no match is found (e.g., "Not found") [match_mode] Optional: 0 = exact match (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard match [search_mode] Optional: 1 = search first to last (default), -1 = search last to first, etc. Often mix up lookup_array and return_array. lookup_array is where the lookup_value lives. return_array is where the answer lives. Both must be the same shape (same number of rows for vertical lookup, same number of columns for horizontal lookup). Example misunderstanding: People think “lookup_array” means the whole table, but it’s just the column/row containing the value you’re matching. My answers are voluntary and without guarantee! Hope this will help you.2Views1like2CommentsRe: I need help with the baseball file.
If this code also doesn't work, a little more information would be helpful in order to assist further. Basic information, such as how it can be found here, and information on how this code interacts with the file or other VBA code would be beneficial. Sub BaseballDataTest() Dim ws As Worksheet Dim lastRow As Long Dim i As Long Set ws = ActiveSheet ' Find last row with data in Column A lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow = 1 And ws.Cells(1, "A").Value = "" Then MsgBox "No data found in Column A", vbExclamation Exit Sub End If Application.ScreenUpdating = False For i = 1 To lastRow ' 1. TRIM all cells in columns A, B, C ws.Cells(i, 1).Value = Trim(ws.Cells(i, 1).Value) ws.Cells(i, 2).Value = Trim(ws.Cells(i, 2).Value) ws.Cells(i, 3).Value = Trim(ws.Cells(i, 3).Value) ' 2. COLUMN B: Only change if team is NOT Blue Jays, White Sox, or Red Sox Dim teamName As String teamName = ws.Cells(i, 1).Value If teamName <> "Blue Jays" And teamName <> "White Sox" And teamName <> "Red Sox" Then Dim colBValue As String colBValue = ws.Cells(i, 2).Value ' Exact matches (case-sensitive as shown) If colBValue = "At Astros" Then ws.Cells(i, 2).Value = "@Astros" ElseIf colBValue = "Mariners At" Then ws.Cells(i, 2).Value = "Vs Mariners" End If End If ' 3. COLUMN C: Subtract 1 hour from any time value If IsDate(ws.Cells(i, 3).Value) Then ws.Cells(i, 3).Value = ws.Cells(i, 3).Value - TimeSerial(1, 0, 0) End If Next i Application.ScreenUpdating = True MsgBox "Complete! Processed rows 1 through " & lastRow, vbInformation End Sub What This Code Does Action Result Trims Column A, B, C Removes extra spaces Blue Jays / White Sox / Red Sox in Column A Column B is NOT changed Any other team in Column A + At Astros in Column B Changes to @Astros Any other team in Column A + Mariners At in Column B Changes to Vs Mariners Valid time in Column C Subtracts 1 hour This code will work if your data matches the assumptions above.0Views0likes0CommentsRe: Excel collage spécial formules
Voici une checklist combinée, courte, efficace et professionnelle. Elle commence par les vérifications simples (mode de calcul, liaisons) et remonte jusqu’aux macros si nécessaire. Diagnostic Excel – Collage spécial bloqué + perte de données À faire dans cet ordre. Test après chaque étape. 1. Mode de calcul (1 seconde) Onglet Formules → Options de calcul Coche Manuel Pourquoi : évite qu’Excel ne recalcule tous les fichiers ouverts au moment du collage. 2. Liaisons entre fichiers (30 secondes) Onglet Données → Modifier les liaisons Si des liens apparaissent → Rompre les liens Pourquoi : empêche les autres fichiers de revenir à leur dernier état enregistré. 3. Collage spécial sans formules (test immédiat) Copie (Ctrl+C) Collage spécial (Ctrl+Alt+V) → choisir Valeurs Si ça fonctionne → le problème vient des formules externes ou du recalcul. 4. Curseur bloqué en croix + collage impossible Appuie sur Échap (une fois) Puis : Fichier → Options → Options avancées Décoche Activer la poignée de recopie et le glisser-déplacer OK → puis recoche l’option Réinitialise l’état bloqué d’Excel. 5. Désactiver temporairement les macros (cause majeure) Fichier → Options → Centre de gestion de confidentialité Paramètres du Centre → Paramètres des macros Choisir : Désactiver toutes les macros sans notification OK → rouvre les fichiers Si le problème disparaît → une macro est en cause (Worksheet_Change). 6. Dernier recours – Compléments Fichier → Options → Compléments Gérer : Compléments COM → Atteindre Tout décocher → OK Mes réponses sont volontaires et sans garantie ! J'espère que cela vous aidera. La réponse a-t-elle été utile ? Marquez-la comme meilleure réponse et likez-la ! Cela aidera tous les participants du forum.4Views0likes0CommentsRe: Excel formulas starting to include current sheet name
You’re absolutely right regarding Excel’s behavior — and thank you for pointing that out. I realize my previous reply mixed in content from a different discussion (about Text to Columns), which is unrelated to this question. Apologies for the confusion there. Your explanation about selection order and sheet switching is correct and aligns with how Excel builds formulas: If you start on the current sheet → Excel omits the sheet name If you return to it after selecting another sheet → Excel includes it Thanks again for the clarification.12Views0likes0CommentsRe: Excel formulas starting to include current sheet name
I wasn’t claiming Excel behavior changed over time. The issue isn’t about versions at all — it’s about how Text to Columns works: Text to Columns has always required a consistent delimiter. The data in the Post does not have one: Some rows use a comma → New Orleans, LA Others don’t → Los Angeles CA City names have variable spaces So this isn’t a version problem — it’s a data structure problem. Even in Excel 95, Text to Columns would behave the same way. The core rule Excel can only split cleanly when: There’s a consistent separator, OR a fixed position is defined. The data has neither — but it does have one reliable pattern: The state is always the last word.41Views0likes2Comments
Recent Blog Articles
No content to show