Forum Widgets
Latest Discussions
Excel Data Model File Path Change
My boss created an Excel data model before I joined the company. He created the data model and queried the data from files within his personal drive. Now that I am here, he wants me to have access to these files and has moved them into a shared folder. The thing is, all of the queries now have the wrong file path. Is the only way to fix this to manually update the file path within the Source step of the power query editor? If there is an easier way, please let me know!CalebF22Apr 21, 2025Copper Contributor499Views0likes5CommentsConditional Formatting Partial
Good morning, In need of a little bit of help. My current situation is I can't figure out a formula to highlight partial matches. In my current excel I gather information from a scan then I filter it out to give me the text I want. Now from there I want that filtered info to match another sheet that same text followed with text after. Due to privacy reason I cant share my excel sheet but will provided a similar example. So lets say column D is the filter info from my scan and my list is from column A (in real word list and filtered info are in 2 separate sheets but same book). on the top you can see a formula I have been trying but doesn't always work. I want both sides (or sheets to highlight when it happens).SolvedFatManFluffApr 20, 2025Brass Contributor111Views0likes9CommentsUtilizing 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 ) ) )Patrick2788Apr 20, 2025Silver Contributor2KViews3likes16CommentsFind 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.KyleHeckApr 20, 2025Occasional Reader1View0likes0CommentsComands not visible under some tabs unless I select arrow
The Alignment tab does not display the various commands like wrap text unless I select the arrow under alignment. I found that I am supposed to have a “Ribbon Display Options” arrow in the upper right corner of Excel, but I do not have this arrow. How do I restore this arrow? This is also true of the Number tab.8104108795Apr 20, 2025Occasional Reader5Views0likes0CommentsPower Query Dropping Decimals with Accounting Format
I'm encountering what seems like a bug in Power Query. I have a table with data that contains numbers with more than 4 decimal places. When this data is in the Accounting format, Power Query is only picking up 4 decimal places, even if the Query is formatting the data is Decimal Number (Changed Type). When the data is formatted as Number, Power Query is able to pick up all decimal places (Some numbers have 10+ decimals) Is this normal behavior? I would like to use the Accounting format because it looks cleaner, but obviously I cannot sacrifice data accuracy. It is also very illogical to have a format labeled "Accounting" only hold 4 decimal places when passed through Power Query I did try restarting Excel multiple times, refreshing queries, the only thing that resolved it was changing the local format to Number.bradbeliveauApr 19, 2025Copper Contributor132Views0likes11CommentsShift Work Calendar Year at Glance Template
I would like to use the Shift Work Calendar year at glance Template in Excel, but it is only built for 3 jobs. I need to edit for 7 or more jobs, but can't figure out how to do it. I'm sure it's a simple solution, but I'm new to this.Joha842Apr 19, 2025Copper Contributor66Views0likes2CommentsPivot by function and graphs.
Hi, I am trying to make my graph based on a pivot ny function to keep updated. As soon as I Add a new line in my table, the graph gets ruined. See attached document for example. Any clever solutions for using Pivot by and graphs? Best regards - GeirHogstad_RaadgivningApr 19, 2025Steel Contributor47Views0likes1Commentcount some data
Hello My friend, Can anyone please inform me what is the Excel equation for the below details: I have 2 columns first one the state and it have two cells only with C or T....and the second column is for agent name which has many cells more than 3k...so i need to know like the cells on the right every agent how many total it have of c and t and how many separately. Thanks in advance.hitman_semsem2007Apr 19, 2025Copper Contributor10Views0likes0CommentsExcel search for files with asterisk or percentage is not working
Hi there Can you please add on the roadmap to have a common way to search files by adding asterisk or percentage? Don't make sense to not be possible such simple query with the standard syntax. Thanks Ricardoricardok1Apr 19, 2025Copper Contributor91Views0likes8Comments
Resources
Tags
- excel42,586 Topics
- Formulas and Functions24,701 Topics
- Macros and VBA6,402 Topics
- office 3656,021 Topics
- Excel on Mac2,652 Topics
- BI & Data Analysis2,372 Topics
- Excel for web1,917 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,634 Topics