Use Form to update data
When the command button New Record is clicked the user enters a value into a Form which will then be entered into F7 (or the next available empty cell in F) When the user clicks OK E7 is then updated with Todays date Followed by Filldown formulas in I6 and J6 to the next row Followed by copy cell J2 and past in to K7 End I have included a sample sheet to help explain what I am wanting to do. All and any help will be greatly appreciated. Thanks Summary Form.xlsx
File for work. please help!
Hi all, i have a file that i use for work. Right now there are 2 different pages. a list of the items with their code and an info about their situation. For Example if they are out, lost, etc. a list of numbers that i send next to the date. if they come back they are yellow, if not they are red. i do all of this manually every day, but when i am not in the office no one does that because it's long, boring and probably very confusing. Ideally i need something that keeps track of what goes out and what comes back and the dates. i attached the two pages. any help, advice, would be highly appreciated!! Many thanks!
Need to return col A if another col contains the text in col B (array) anywhere
So, here's my array in columns A and B: Column L has a journal description that COULD contain one of col B descriptions. So, column L will have things like "3M LO(a)N 2024.12" OR "3M LO(a)N 2024.11", etc. But they could also have "TRU UP COLORADO INTEREST #3126" - basically, the description in col B could be anywhere (together or separated) in Col L. What I want to do is, in Column C write a formula that will look at column L and compare it to the array in columns A & B, and then return column A if the value in col B is found. So, for "3M LO(a)N 2024.12", col C would return "1". For "TRU UP COLORADO INTEREST #3126", return "5". The difference being "3M LO(a)N" is found in the same sequence as it is in the array, but "COLORADO #3126", though contained in col L, the two parts are separated. I'd like to leave these descriptions as they are to help in identifying if/when I need to change the array, but if it's too difficult, I could just use "#3126" as the description in col B and add a vehicle description in another column. The array is named "CATEGORIES" to ease formula creation. Thanks for any help in advance. *** apparently the full word LO AN is not allowed in the text. So, LO(a)N it is.
Spreadsheet Compare Highlight Function
Hello I would like to know if rows can be highlighted in the compared files using the Spreadsheet Compare program. I want the rows changed, rows deleted and the rows with entered values changed highlighted in different colors respectively on the compared sheets by the Spreadsheet Compare program itself. Is it possible? Basically, the Spreadsheet Compare program takes in two spreadsheets - Old Data and New Data. I want the added rows detected in the new data sheet to be highlighted green, the changed rows should be highlighted yellow in the new data sheet and the deleted rows should be highlighted red in the old data sheet. I would like to know if there's any way to accomplish this using Spreadsheet Compare or any external method. I'd greatly appreciate any help! Thanks!Sorting Recurring Expenses?
Hello, I don't use Excel much and I'm having trouble trying to do something that seems so simple. I've searched and searched and can't find an answer. I've prepared a very simple worksheet for monthly recurring expenses. These expenses are typically due the same date each month (1st, 10th, 15th, etc.). How should I be entering those dates so I can sort the list on that? Ultimately, I'd like to sort the expenses bi-weekly to coincide with my pay periods. Thanks! LyleWhat do you think of thunks?
OK, so the most likely response by far is going to be "I don't". However, I tried one of Omid Motamedisedeh's regular challenges and found it a suitable problem for exploring some of the lesser known byways of modern Excel. The challenge is to pick out locally maximum values from a rolling range. What I did was to write a function that used MAP to select one cell at a time, using DROP to remove the initial cells and TAKE to return a range of 5 cells with the active cell in the middle. The direct route to solving the stated problem would be to calculate the maximum value within each range immediately, but I was interested in the more general problem of "could I return the array of ranges in a form that would support further analysis?" As shown, the following formula ROLLINGRANGEλ = LAMBDA(values, n, LET( rows, SEQUENCE(ROWS(values)), MAP(rows, LAMBDA(k, LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), rng ) ) ) ) ); gives and array of ranges error, but simply by enclosing the 'rng' variable within a further LAMBDA ... LET( m, QUOTIENT(n, 2), rng, TAKE(DROP(values, k - m - 1), MIN(k + m, n)), LAMBDA(rng) ) will cause Excel to return an array of functions, each one of which would return a range if evaluated. In the attached workbook, a number of formulae are based upon this array of functions = ROLLINGRANGEλ(dataValues, 5) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, ROWS(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, ISREF(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, AVERAGE(ϑ()))) = MAP(ROLLINGRANGEλ(dataValues, 5), dataValues, LAMBDA(ϑ,v, MAX(ϑ()))) = LET( rollingMax, MAP(ROLLINGRANGEλ(dataValues, 5), LAMBDA(ϑ, MAX(ϑ()))), FILTER(Data, rollingMax=dataValues) ) The first simply returns #CALC! errors on the worksheet which is the normal response to a Lambda function defined on the grid. The second formulas uses ROWS to show that the ranges are not all the same size, the third shows the returned objects to be range references and not simply arrays of numbers, the forth is a rolling average while the fifth is a rolling MAX. The final formula returns the solution to the problem, being a filtered list. The purpose of this post is to demonstrate that Excel, which started out as a 'simple' spreadsheet program, now contains a very different programming environment that shares a function library and uses the grid for input/output but, other than that, has very little in common with 'normal' spreadsheet practice! A related survey can be found at https://www.linkedin.com/feed/update/urn:li:activity:7285432559902068736/
Adding COUNTIF formula to cell by VBA code
Morning all, need some assistance. Attempting to insert a new formula by VBA with an already working Macro. All the other references unhighlighted below work perfectly, but I am missing something with the syntax around a countif function. The countif currently works on the excel sheet itself, however adding it into the VBA is generating the typical "Compile error: expected: end of statement" error indicating a syntax problem. Any advice?Is there a formula / function for displaying the "Name" of a reference cell?
I am trying to see if there is an Excel function to display the "Name" of another cell. I looked through all of the options using the =CELL() function (there are many) but none seem to return the "Name" that I have given the cell. I have used reference names in several places within my spreadsheet to simplify formulas and make them more readable, but I would like to have a dictionary of these referenced inputs including what each one is named in a separate tab. Any pointers are appreciated.IFS or anyother function
Dear Experts, I have a data like below:- So, Column "B" - sfn can go from 0 ~ 1023, and Column-"C", can go from 0~19, Column "G" has 3 values(rnti's) - Now, we have only 2 situations like below in Column"E", where I need the formula:- Either all these 3 rnti's can be Multiplexed in the same sfn.slot So, in below snip all 3 rnti's are FDMed in same sfn.slot - 394.6 and should be continuous, so Column "E" should have fdm-3 But in below instance, in 395.2 we have only 2 rnti's multiplexed(so fdm-2 should be populated) Attached is the spreadsheet. Thanks in Advance, Br, Anupam
