Forum Widgets
Latest Discussions
A LAMBDA Word Search Puzzle
I've created a 15x10 Word Search. I've included a word list (dynamic range) containing words which might be found in the word search. The goal is to then extract those words when they appear in the word search. The catch is the words can appear 1 of 4 ways: horizontal left-to-right, horizontal in reverse, vertical, and vertical in reverse (No diagonals!). (The words currently in the puzzle are in blue for illustrative purposes) When I started this exercise, I thought of a solution involving SCAN and some text accumulation. There are a few issues with this method. SCAN would need to go through the puzzle twice (Can't VSTACK then SCAN) - once going through the range as normal then through the range when flipped (w/CHOOSECOLS and TRANSPOSE). The dimensions of each are not the same and it gets messy. Ultimately, the method I used starts by finding the position of words (Regular or in reverse) for a given row, pulling the words, filtering, splitting (Have to account for potentially multiple words in a line), stacking, and producing a list of words found. That's the short of it. You'll have to see the workbook. I'm interested in if SCAN can potentially make the solution a bit simpler.
Spill formula with cell reference from lambda map
Hi, Hopefully someone can give me any advice on the following. I want to combine a spilled formula array with some logic to determine the label of each cell. That determination is done where the formula looks at the value of the cell itself and the adjacent cell. In orange I tried to combine the spilled formula but it returns blanks. Formula is as follows: =LET(tbl;XLOOKUP(A2#&"|"&B1#;tbl_Data[Nummer]&"|"&tbl_Data[PostingDate];tbl_Data[Status];;0); MAP(tbl;LAMBDA(a; LET(b;INDIRECT(ADDRESS(ROW(a);COLUMN(a))); x;AND(OFFSET(b;;0)="Uitgebracht";ISERROR(OFFSET(b;;1))); y;AND(OFFSET(b;;1)="Opdracht";OFFSET(b;;0)="Uitgebracht"); z;AND(ISERROR(OFFSET(b;;0));ISERROR(OFFSET(b;;1))); IFERROR(IFS(x;"Vervallen";y;"Nieuw";z;"");""))))) Anybody any idea what goes wrong? Many thanks, MichielArray formula map lambda indirect address
Hi, The orange area contains a spilled array formula where I am trying to loop through a spilled array and for each cell I want to check the value and the value of last period and on basis of an if formula provide a certain label. Some how my formula give all blanks (orange part of the attached). I am not sure if the indirect address of the map formula works like this: =LET(tbl;XLOOKUP(A2#&"|"&B1#;tbl_Data[Nummer]&"|"&tbl_Data[PostingDate];tbl_Data[Status];;0); MAP(tbl;LAMBDA(a; LET(b;INDIRECT(ADDRESS(ROW(a);COLUMN(a))); x;AND(OFFSET(b;;0)="Uitgebracht";ISERROR(OFFSET(b;;1))); y;AND(OFFSET(b;;1)="Opdracht";OFFSET(b;;0)="Uitgebracht"); z;AND(ISERROR(OFFSET(b;;0));ISERROR(OFFSET(b;;1))); IFERROR(IFS(x;"Vervallen";y;"Nieuw";z;"");""))))) Somebody any suggestion on this? Many thanks,Lambda map formula and spilled range
Hi, In the attached file I try to have one formula that takes a spilled range and based upon the value of a cell in the spilled range (compared to the cell one column to the left) I want to return a specific label (result in the yellow areas, the orange area tries to do the same but then in one spilled formula). Somehow the results remain blanks. Anyone any advice on the map formula that compares the cells? Many thanks, Michiel
Using Filters to Auto-Update Complex Formulas
Hi, This is my first post here. Hoping someone here may be able to assist me. I have the below spreadsheet. There is currently 500 rows of data. At the Top is a Summary Grid with formulas summarizing the data below (Rating, Employee Level, etc. ) I'm also showing an example of one of the formulas in Cell F3. The other cells contain similar formulas. Ask: How do I get the Summary Grid to only display the data based on the rows visible, which is determined by Column T filter selections. For example, if the user selects "Level 4 Manager 2" and this manager has 10 employees, how do I get the Summary Grid to display just the data based on those 10 employees (versus the total 500). I've seen videos/sites that use the Subtotal or Aggregate formula, but it appears those can only be applied to specific functions, not a more complex formula. Is this possible?
Macro Error for Pivot Table
I can create a Pivot Table for the data I download and it works fine. However, when I made a Macro to do the same thing, I get an error. The error is: Run-time error '5': The following is a screenshot of the highlighted error in the Macro. I am now using Excel 2013, however I made an initial Macro using an older version of Excel and am now trying to make a pivot table using 2013 and installing it near the start of the existing Macro I have. How do I correct for this error?
Complicated formula help for a budget spreedsheet
Hello everyone, Normally I can find what I'm looking for and apply it to my project, but this one is a bit complicated, if it can even be done. I'll try to explain it as best as I can. I've got a calendar type layout on one tab(Forcast Template), with bills listed on anoter tab(All Bills). What I'd like to get it to do, is automatically fill in each bill for the corresponding day. The complications are as follows: some days have multiple bills due, and some bills only apply to certain months. I'm familiar with if/then functions as well as xlookup, but combining the two for this application is proving to be a tad more complicated. Again, that's if it can be done. Not sure how to attach the file for viewing, but I'll post screenshots so hopefully you can tell what I'm trying to do. Let me know if I need to clarify anything. Thanks!Vlookup or any better function
Dear Experts, Greetings! I have a data like below:- Sheet 1 & 2, and have to fetch the data from 2 -> 1. If , I use Vlookup with True(approximate match) then it populates many times , but I want to populate the exact time from sheet-2 and put it in sheet-1 when the RRC(Column F) triggered example shown as 1st line from Sheet2 What's the best way to achieve this, we work in terms of ms( milliseconds ) so can't do any Approximations. Thanks in Advance, Br, Anupam
