Pinned Posts
Forum Widgets
Latest Discussions
How to retrieve the latest unit cost
Hello, I am sharing a condensed version of a pricing file that lists from Cols A to C Item Codes and pricing by date. The goal is to retrieve the latest unit cost for a particular Item Code. Here is the link: https://docs.google.com/spreadsheets/d/1wm9Ry7PGqM0qirY6GeKu1F9ZHzR3kFrj/edit?usp=drive_link&ouid=103354753371375324640&rtpof=true&sd=true Note: the formula in Cells F5:G48 may return N/A because of the link that automatically opens up as Google Sheets. In Cells F5:G48, I have attempted to retrieve the latest Unit Cost by first bringing in Unique Item Code from the repetitive Item Code in Column B. Unfortunately, the unit cost coming in for most of the items is incorrect. For example, Item Code: ABR00054 should have a latest unit cost from 12/6/2023 of $42.98. However, the xlookup(maxifs) function that I am using is bringing in $4.53. I thought that the formula shown in Cells G5:G48 would work but something is scrambling the formula logic. Can you please point out if the formula is incompatible with the source data layout as presented in Cols A to C? Please consider highlighting another formula that will be able to provide me with the data that I am looking for. Thank you. Regards, Shams.
Reference sheet numbers in hyperlink
I want to be able to link a specific shape to a sheet by the sheet number, not its name. Using the standard method, by selecting 'hyperlink', only a list of sheet names appear. Inserting a onclick() macro into the initial sheet would work but I cannot figure out how to reference a sheet by its number. All help appreciated.Excel formula to determine missing numbers
In column S of Sheet 1 I have a list of case numbers. They all start with the year that the incident occurred &, followed by the incident number. On Sheet 2, I want to be able to quickly determine if any numbers in the sequence have been skipped. For instance, 2024-033, 2025-001, 2025-003, etc. There are never a set number of cases per year, so it wouldn't matter whether there should've been a 2024-034 after #33, but I would need to be able to readily see that 2025-002 was never input. If it matters, each year begins with '001' and not '000' and I'm working with Excel 2016.
Inconsistent “Wrap Text” Behavior Between Excel Desktop and Excel Web
We’ve observed inconsistent behavior with the “Wrap Text” format across different Excel environments (Windows Desktop vs. Web). Steps to Reproduce: Create or open an Excel file. Enter multi-line text in several cells and enable Wrap Text. Save the file locally. Upload it to OneDrive or open it in Excel for Web. Observe the displayed cell content without resizing the columns or reapplying formatting. Observed Behavior: On Excel for Windows (Desktop) → Wrapped text displays correctly. On Excel for Mac (Desktop) → Wrapped text displays correctly. On Excel for Web → Text may not wrap properly by default, requiring manual reapplication of “Wrap Text.” Expected Behavior: Cell content formatted with Wrap Text should display consistently across all Excel platforms (Windows, Mac, and Web) without requiring manual adjustment or reapplication. Excel Mac Desktop: OneDrive:
Opening CSV file with UT8-encoding (65001) as default for csv files.
Hello, I am trying to open a csv file, that contains Arabic characters. In most cases, it will render the Arabic Character as unreadable characters (as shown in the image below). Those scenarios, include opening the file by double clicking, saving it again as CSV with UTF-8, or converting it to XLSX, all of which result in the same thing as the first image. The only I found to actually make it render them correctly by following this: https://support.microsoft.com/en-gb/office/opening-csv-utf-8-files-correctly-in-excel-8a935af5-3416-4edd-ba7e-3dfd2bc4a032 Which does allow me to select utf-8 encoding. Other CSV viewers don't have that issue. It would be convenient to have away to make this behavior the default. In my organization we have thousands of employees, and the fact they have to open Excel, go to the Data section, select import CSV, select the encoding, then click importing for every single CSV file we have is very annoying, we deal with thousands of files per month. The second Image is for another file: Here is the file in case anyone want to try to open it. Please note we need to support as old as Excel 2016 for any solutions. The CSV files are generated from Power Automate Flows, and we would really like to avoid any sort of conversion between the formats online (Though I don't mind if I can embed let's say, a character or so at the end of the file to make Excel open it correctly) as our experience has been very slow for anything Excel related in Power Automate (if for example we use Excel to generate the files instead of CSV, it would take around 3 hours for each file at least, compared to 5 minutes in CSV). Best, Ali A.Rack of Lambda
There’s been a lot of content shared recently to commemorate the upcoming 40th anniversary of Microsoft Excel. Personally, I’ve only been using Excel for around half that time, but October also marks my 2-year anniversary since "joining the conversation" on this forum. As a gift from me to you (anyone interested), I’ve spent some time over the past few weeks revisiting old posts, updating methods I shared previously and packaging them into a collection of generalized Lambda functions to assist with a variety of common array manipulation and transformation scenarios. The attached file contains some 35+ Lambda functions, ranging from very simple concepts to much more advanced techniques. You can also import them directly from my gist, if desired. While they were all compiled and composed of my own accord, I would be remiss if I failed to credit the community and its members as a major resource in my own development. The amount of knowledge, tips and tricks gained through community collaboration is simply invaluable. You may notice some recurring themes in the way I’ve written many of the functions. For example, I like to keep the optional arguments as simple as possible, using either Boolean values passed to IF, or numeric options from 0 to 3 passed to CHOOSE. Also, many of the array transformation functions use TOCOL-IF-SEQUENCE in one way or another, with MOD-QUOTIENT-SEQUENCE used only a few times in the more complex algorithms (e.g. HWRAP and VWRAP). The collection also includes a few examples of Lambda recursion, the most notable being PF (Prime Factorization). CROSSJOINM was written as a "how-to" demonstration for filtering multiple optional arguments using LAMBDA and NOT-ISOMITTED. There’re also some powerful scanning functions like SCAN3, which can handle multiple input arrays, as well as EVALS with VALS2 to VALS7, which can store and recall multiple variables at each iteration (useful for corkscrew calculations). What you won’t find, however, are methods that use INDEX in an iterative manner with functions like MAKEARRAY, SCAN, etc. as these are only efficient when iterating over a range reference (they will bog down considerably and become practically unusable after just a few thousand iterations when looping over an array object). As such, I don’t recommend them as "generalized" solutions, although they can be very effective on a case-by-case basis. Similarly, you will only find 2 examples in this collection that use REDUCE-STACK in a limited capacity, with fewer than 10 iterations, as I also consider this to be a method of last resort due to its problems with efficiency when the number of iterations cannot be controlled. Hopefully one or two of them proves useful. If not, no big deal. Many of the examples in the attached file are interactive, so you can see how the different options affect the output. For those brave enough, please feel free to share your own custom functions too. I’d love to see what you got. Cheers! REV_2025-10-16: updated function definitions for SCANBYROW and SCANBYCOL to be able to handle TYPE 16 and TYPE 128 values.VBA Data Import
Looking for VBA code to parse a list. The list is an extract of a music playlist which retains the Line 1 "name of song", skips the second line which is a repeat of Line 1. Line 3: Retains Artist name Line 4: Retains Album Name Line 5: Time of song - Will probably have to manually move or delete these manually The list is one of my Playlist from Amazon Music Prime. Thank you. Song Artist Album Time Whisper Whisper Marion Meadows Whisper 4:16 Just Doing Me Just Doing Me Marion Meadows Just Doing Me 2:54 Midnight Bolero Midnight Bolero Armik Amor De Guitarra 4:52 Every Kinda People Every Kinda People Architects of Sound
