Forum Widgets
Latest Discussions
Pulling data from different sheets into a summary sheet
How can I pull employee data from different sheets into a live-updating summary sheet? I have 5 groups of employees — A, B, C, D, and E — with their names listed across 5 separate sheets (one for each group). I’d like to create a summary sheet that displays all employees and the groups they belong to. Sometimes employees change groups. Is there a way for the summary sheet to update automatically when those changes happens?
Find and replace mystery
I am attempting to replace all the occurrences of "$23" within a formula with "$43" [I'm not using the quote marks in the box]. Every single time, it replaces it with "$34" instead of "$43". For troubleshooting, I tried: Find "23" replace with "43" - it worked correctly. Find "$23" replace with "$6300" just to see if a different 'replace with' value would have a different outcome - it still replaces it with "$34" I have another find and replace in the same cell and same formula where I am replacing "$18" with "$38" - it works just fine! I do not have the SUBSTITUTE formula anywhere in this cell. I read somewhere that SUBSTITUTE could affect find and replace. I am stumped. If it matters, the "$23" I am trying to replace is within a SUMPRODUCT formula. I am using 365.SOLVED - Hyperlink function works as Flash Fill but not as Dynamic/spill array
Hello all, I have tracker logs that I'm comparing to my bank and have solved all the issues so far, except for how to spill my Find & Go To hyperlink function. I have a complex formula in range $I$3 that will list all mismatches (bank transactions that are not in tracker and tracker logs that are not in bank) via spill and a data validation list in range $J$3:$J$1000 to select an action from. In range $K$3, I need a spill formula that will search the import sheets for the amount shown on the mismatch list, for each mismatch, then go to it upon click. In range $K$3:$K$1000, I currently have individual formulas that will check if the mismatch contains "Bank" and if so will display a hyperlink that extracts all text then reads the amount as a value, then matches it to the Tracker Import sheet. Otherwise, it will display a hyperlink that does the same thing but matches it to the Bank Import sheet. Logical Test (checks if it contains "Bank"): Value if True (creates hyperlink to the mismatch amount on the Tracker Import sheet): Value if False (creates hyperlink to the mismatch amount on the Bank Import sheet): This works perfectly fine but when I do it as a spill formula, the links either don't work or take me to the incorrect sheet/cell. Formula (Flash Filled range $K$3:$K$1000 - working): =IFERROR(IF(ISNUMBER(SEARCH("*Bank*",I3)),HYPERLINK("#'Tracker Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3,FIND(" ",I3)-1)),'Tracker Import'!$D$2:$D$1000,0)+1,4),"View Log"),HYPERLINK("#'Bank Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3,FIND(" ",I3)-1)),'Bank Import'!$F$2:$F$1000,0)+1,6),"View Transaction")),"") Formula (spill from range $K$3 - not working): =IFERROR(IF(ISNUMBER(SEARCH("*Bank*",I3:I1000)),HYPERLINK("#'Tracker Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3:I1000,FIND(" ",I3:I1000)-1)),'Tracker Import'!$D$2:$D$1000,0)+1,4),"View Log"),HYPERLINK("#'Bank Import'!"&ADDRESS(MATCH(VALUE(LEFT(I3:I1000,FIND(" ",I3:I1000)-1)),'Bank Import'!$F$2:$F$1000,0)+1,6),"View Transaction")),"") Notes: • Range 'Tracker Import'!$D$2:$D$1000 is an Amount column that lists logs from my Spending Tracker. This range is just values. • Range 'Bank Import'!$F$2:$F$1000 is an Amount column that lists transactions from my bank statement. This range is just values. • If the Mismatch List says an amount is not found in the Bank, that means it's an existing amount in the Tracker that shouldn't be there, and vis versa. I would highly appreciate any solution to make my current formula a spill formula. Thank you!
Utilizing Excel's turing capabilities to create Conway's 'Game of Life'
The Background It's been said with Lambda (and LET and a wealth of functions in 365) Excel has become 'turing-complete'. To quote the article linked below: "You can now, in principle, write any computation in the Excel formula language." https://www.microsoft.com/en-us/research/blog/lambda-the-ultimatae-excel-worksheet-function/ The Challenge I thought it would be fun to create Conway's 'Game of Life' in Excel 365 to see how far I could push things. Conway's Game of Life - Wikipedia The rules are simple: A 'cell' has up to 8 adjacent cells (less if the cell is on the edge of the board). A 'neighbor' is a cell with a 1 while a 'dead' cell is empty. An 18x18 board Multiple iterations Bigger boards! more (it's relaxing to create new shapes and designs) The Approach My first thought was to use MAKEARRAY because I could use 'r' and 'c' coordinates and there would be no stacking. I devised a recursive function that worked for 1 iteration but failed on subsequent iterations because the use of TAKE/DROP was slowly shrinking the board! The revised approach is essentialy a recursive MAP that uses 3 arrays: the input matrix, the 'r' array (row numbers) and the 'c' array (column numbers). It's my way of using r/c without using MAKEARRAY. For Discussion I welcome any improvements to the existing function and any different approaches someone may have to creating Conway's Game of Life. Conway Lambda follows: Conway =LAMBDA(matrix, iterations, IF( iterations = 0, matrix, Conway( LET( height, ROWS(matrix), width, COLUMNS(matrix), r_arr, SEQUENCE(height) * SEQUENCE(, width, 1, 0), c_arr, SEQUENCE(height, , 1, 0) * SEQUENCE(, width), CheckNeighbors, LAMBDA(lattice, r, c, LET( RCx, LAMBDA(row, col, IFERROR(CHOOSECOLS(CHOOSEROWS(matrix, row), col), 0) ), N, RCx(r - 1, c), NE, RCx(r - 1, c + 1), E, RCx(r, c + 1), SE, RCx(r + 1, c + 1), S, RCx(r + 1, c), SW, RCx(r + 1, c - 1), W, RCx(r, c - 1), NW, RCx(r - 1, c - 1), compass, VSTACK(N, NE, E, SE, S, SW, W, NW), neighbors, SUM(compass), IF( AND(lattice = 0, neighbors = 3), 1, IF( AND(lattice = 1, OR(neighbors = 2, neighbors = 3)), 1, 0 ) ) ) ), MAP(matrix, r_arr, c_arr, CheckNeighbors) ), iterations - 1 ) ) )
Excel Leader Board
I'm trying to use excel to maintain a leaderboard in an upcoming golf tournament. The tournament will go on all day and the registration desk will enter golfers and scores. I'd like to be able to use that data to maintain a leaderboard so that the golfers can see where their group stands. I need to use the continuously updated data from the registration desk, sort it and have that screen available. How can I do this with data that is continuously being updated and without changing or rearranging the data the registration desk is using--they need to keep their data in tee-time sequential order?
