Forum Widgets
Latest Discussions
IF OR statements
I have a cashbook spreadsheet which in Col A shows money in, Col B shows money out, Col C shows the cost code, and Cols D, E, F, etc one for each of the cost codes. So, for example, if there's a cost of £100 in Col B and a "Gardening" cost code in col C then the value in Col B is copied over to, let's say, Col F which is the Gardening costs column. That's easy. In the Gardening costs column is F and there's a gardening cost in row 10, we have: =IF(C10="Gardening",F10=B10,"") But suppose there's money coming in, for example, a refund for gardening overcharged. This income would be shown in Col A. What I want to do is to repeat the above but add something along the lines that if there's an income in Col a, that should also be moved to the gardening column but as a negative amount. I've struggled to write this without success. any help greatly received! Thanks to the community. G.
Need help with a formula
Hi all, I've got a problem with a formula I'm trying to use =XLOOKUP(A4:A540,(OFFSET('XLOOKUP page'!$B$4,1,COLUMN(B4)*2-4)):(OFFSET('XLOOKUP page'!$B$540,1,COLUMN(B540)*2-4)),(OFFSET('XLOOKUP page'!$A$4,1,COLUMN(B4)*2-4)):(OFFSET('XLOOKUP page'!$A$540,1,COLUMN(B540)*2-4))) The formula is supposed to do an XLOOKUP and when I drag the formula across it goes up in increments of 2 but however I try to do it I always get an error or the first column works and the subsequent ones dragged across don't work. Could anyone help please.
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?
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.Wordle with Excel Solver
Amid the plenty of solutions and techniques that are coming for the newly viral word-puzzle game Wordle. I have also tried to make an excel-based solution for the puzzle. Because, being an excel enthusiast, I believe that Excel provides more flexibility and customization power than other analytical and problem-solving tools. The primary aim of sharing the solution with the respected members of this excellent community is to have different logic and alternative solving approaches that would surely provide some learning opportunities. The element of interest for the members is to model an approach that would produce more close and accurate guesses. For this solver, I have got inspiration from Mr. Robert Gascon, who occasionally demonstrates excellent features of traditional excel. Therefore the solver is a non-VBA / non-CSE model and does not require the 365 version of Excel. Thoughtful comments, especially from Mr. Robert Gascon Twifoo and Mr. Sergei Baklan SergeiBaklan about alternate solving approaches will be valuable. The solution of two real games along with the solution strategy is also attached for reference purposes. Thanks Tauqeer
