Forum Widgets
Latest Discussions
Copying Data from Word to Excel
Hi, I am trying to copy data from Word to Excel. The data in Word seems to be in text format. When I paste it in excel, it gets perfectly copied except the columns. What happens to the columns is that, if there is an empty cell, then it is replaced by the data that is to the right of it. Thus the values in the columns shifts left if there are empty cells. Can anyone please help me sort it out? The volume of data is large, thus manually correcting these is not feasible. I have provided a screenshot of the data that is in word. Here is also a link of the word file - Report.docx Thank you.
VBA Coding to hide/unhide a row based on a cells colour
Hi, I'm fairly new to VBA and just getting to grips with some basic functions but am struggling with a slightly more complex one that i would like to implement. I have searched around but can't find an answer to my specific problem so hoping someone here will be able to point me in the right direction please. I have an excel workbook with multiple sheets. Lets call them Sheet1 and Sheet2. I currently have a conditional format set so that if cellA1 in Sheet2 is Red, then cellA2 in Sheet1 auto formats to red and any text in cellB1 in Sheet2 is brought forward to cellB2 in Sheet1 (via in cell formula, not VBA) with row2 of sheet1 in standard position of hidden. I would like to add a string into VBA so that when cell A2 of sheet1 auto formats to Red, row2 sheet1 unhides, but any other value/colour present would keep row2 hidden. A few questions around this: Is this even possible? Do i need to move the colour formatting into VBA instead of an in cell formula and conditional formatting, and if yes, how do i do that? So far my VBA experience is mostly just straightforward 'IF' functions and my knowledge is very basic so i would be very grateful of any advice and suggestions you have with clear descriptions so i can understand and learn. Thank you in advance :)
Filter list based on another cell
Creating a project timesheet where employees input their hours each week for each project so we can appropriately charge the client. I need it to function so when the employee selects their name in the validation drop down list it automatically filters out everyone else's project hours for that week. E.g. They would select their initials in cell C2 and it would automatically hide all the rows and only show the corresponding rows for the selected employee. So for employee "FC" it would go from looking like this... To this... Alternatively, if there is a far easier way of doing this would love the advice. Thank youUtilizing 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 ) ) )
Sorting in Excel using a Mac and using custom lists
I have created a custom sort list in Excel by going to Preferences > Sort and created a list. There was an old list there that I didn't need and deleted. My question is: when I go to sort the data and I click on Sort > Custom Sort and under the heading "Order", I click on the options and the drop down menu includes a selection that no longer need. How do I get rid of this? Does anyone have any idea please? TIAAutomatically record the value of one cell into a different cell each day
Each day I record the current value of my shares into cell B2 on Sheet1. Below, in B5, C5 etc I have all future dates for the next few years in 1 row. In cell C2 I have todays' date (=TODAY()). How can I automate excel to record the last value of cell B2 every day into a different cell under the dates. So far I've got =IF(B5=$C$2,$B$2,""). So, if false, I want to keep the last value of B2. Essentially a basic database that I can use to create an ongoing graph.
Y Axis position and data labels
Hi All, I would like to know if it is possible to position a datalabel between the last data point and the Y Axis. Example with datalabels on the left: Same example with datalabels on the right: As you can see the datalabels override the Y Axis label. I would like to move the Y axis at a custom position. More to the right in order to have the datalabel after the last datapoint but before the Y Axis and thus no override. But it does not seem possible. I tried to play with the margin of the Y axis to cheat a little but it is disable. Any idea how to do it ? Thanks, Concaho
Microsoft AutoUpdate error installing Excel 16.97
Hi, I am getting an update error installing Microsoft Excel 16.97 on Mac OS Sequoia. I am currently on 16.96. The install stops near the end of the progress bar for a long time and then I get an update error. I've had similar issues before and resolved by installing from the pkg file but I can't find the 16.97 install file.
Filter via checkboxes with multiple criteria
I want to make a feature/application matrix where you can choose features by clicking on a checkbox and the filter needs to show only the application where all the chosen (checked) are available. So instead of the inbuild header filter I want to choose by checking / unchecking the boxes . Example: Can some one tell me how to achieve this? (I prefer to have the features vertically and the application/platform horizontally but I think that's harder to achieve or I'm I wrong?)
