Forum Widgets
Latest Discussions
#unknown! displayed in excel where an inserted picture should be located
A supplier is sending me an excel template which contains pictures in a cell The picture can be viewed in web based outlook and web based excel but when I download the file the cell states #unknown! I believe the supplier is using a personal 365 account and I am using 365 Apps for enterprise I think they are adding the picture by completing the below (taken from MS site) this is what I see when I open it up on web based outlook But this is what I see when I download an open in excel desktop Any help is greatly appreciatedAButler1910May 20, 2025Copper Contributor51KViews3likes18CommentsCopying range of cells referencing named cells with a sequential change
I have a large dashboard with many named cells. I want a summary table of 36 rows and 15 columns to reference the named cell values without having to manually insert each reference. Example: I enter =A1_A1 into cell D6 and want the adjacent cell D7 to dynamically be =A1_A2 and D8 to be =A1_A3, etc. Example: I enter =A1_A1 and the next cell down to become =A2_A1. then =A3_A1, etc. I know there are functions that can address it, I just have not been able find what works. I appreciate any assistance with this. It would save me a tremendous amount of time.dansweetwoodMay 20, 2025Occasional Reader1View0likes0CommentsHighlight Duplicates but only from one column
Hello, I have two columns of values in my screenshot. Column D and Column E. Column D has some duplicates and Column E does not. How do look at both columns but only highlight the duplicates in column D, only IF they appear in column E? Thanks!Solved1162646322May 20, 2025Occasional Reader36Views0likes2CommentsSolving an 0 1 Knapsack Problem with LAMBDA
A Knapsack Problem is defined as: Given a set of items, each with a weight and a value, determine which items to include in the collection so that the total weight is less than or equal to a given limit and the total value is as large as possible. -Wikipedia After studying 0 1 Knapsack Problems (A given item may only be selected once) and the techniques used to solve them, I sought to build formulas to solve a standard problem consisting of 5 items with various weights and benefits. I considered generating a value matrix (array of arrays limitation - MAKEARRAY could only go so far) and creating a recursive Lambda (I couldn't conceptualize one and I didn't think it would be efficient memory-wise). The solution I went back to a previous project's technique: LAMBDA Halloween Challenge - Microsoft Community Hub That challenge was all about generating the COMBINA results. The way I see an 0 1 Knapsack is essentially generating COMBIN results and then determining which combination is the greatest benefit without going over the capacity. First, I created the 'Shuffle' function which takes a value produced from SEQUENCE and excludes any numbers >5 (total number of items) and removes values where a number repeats (e.g. 122 and 123 vs 321). Shuffle =LAMBDA(scalar,LET( arr, 1 * MID(scalar, SEQUENCE(, LEN(scalar)), 1), filtered, FILTER(arr, arr <= 5, 0), sorted, SORT(UNIQUE(filtered, 1), , , 1), IF(TAKE(sorted, , 1) = 0, 0, sorted) )) Then I created a function to be called within REDUCE: GenerateCombin =LAMBDA(a,v,VSTACK(a, EXPAND(Shuffle(v), , 5, 0))) This is the most resource intensive part of the project because if SEQUENCE goes into 5 digits, REDUCE will struggle to stack. I added a check to determine if picking all 5 items exceeds the limit so there's no need to go into 5 digits with SEQUENCE! =LET( r, IF(SUM(weight) > cap, 2345, 12345), distinct, UNIQUE(DROP(REDUCE(0, SEQUENCE(r), GenerateCombin), 1)), DROP(SORT(distinct), 1) ) 'Solve' to be called within BYROW. This function checks the CombinMatrix and totals up the Benefit and Weight for each combination. It strings together the results in each cell. =LAMBDA(row,LET( w, SUM(XLOOKUP(row, item, weight, 0)), b, SUM(XLOOKUP(row, item, benefit, 0)), IF( w <= cap, "Items: " & TEXTJOIN(", ", , TEXT(row, "0;;;")) & CHAR(10) & "Benefit: " & b & CHAR(10) & "Weight: " & w, NA() ) )) At the sheet level: =LET(results,TOCOL(BYROW(CombinMatrix,Solve),2),WRAPROWS(results,4,"")) The results: The question for the community: Can you conceptualize an easier way to do this? Formula-based solutions only, please.SolvedPatrick2788May 20, 2025Silver Contributor7.5KViews1like14CommentsMatch/Index referencing incorrect row
I have a spreadsheet that I am working on in MS365. On sheet "A" I am drawing the following information: AGE (=G63) into cell S65 Weight Lifted (=F68) into cell S66 From sheet "B" (Reference data) Row 2 are the age categories (17 - 21, 22 - 26, 27 - 31, 32 - 36, 37 - 41, 42 - 46, 47 - 51, 52 - 56, 57 - 61, 62 Over. These are grouped as MDLAGE. Column A are the scores - 100 - 0 descending order. these are grouped as MDLPOINTS In cells B3:K49 are the weights lifted. The values vary by age and there are cells that are blank or have a "---" in them. These are grouped as MDL. The formula I am using is: Cell S67 - =MATCH(S65,--LEFT(MDLAGE,2),1) This formula is returning the correct column. Cell S68 - =MATCH(S66,INDEX(MDL,0,S67),1) This formula is referencing the INCORRECT row. It is returning row 40 instead of row 31 which is where the correct weight is listed. If I change the 1 to a 0 or -1 the return is N/A. I have tried multiple formulas to "skip" the blank or --- cells but nothing has worked. Any help would be greatly appreciated!Scout50May 20, 2025Occasional Reader59Views0likes3CommentsNeed help with a formula
Im building an employee data sheet and one of the columns is HIRE DATE, the next column is 90 DAY REVIEW DATE. Both columns are date formatted. The formula in the 90 DAY REVIEW DATE cells is simply the hire date + 90. The problem is that if there is not a date in the hire date cell, the date that is being displayed in the 90 DAY REVIEW DATE is 3/30/1900, (I assume this is the default beginning date in Excel?) I do not want anything displayed in the 90 DATE REVIEW DATE column is there is not a date in the HIRE DATE column. Can someone help me with this please?SolvedSunnymom1263May 20, 2025Occasional Reader29Views0likes1CommentSum from cell across multiple sheet, depending on hire date
I can not figure this out, I am trying to make a summary sheet that will count the vacation days across multiple sheets from "Start" to "All" (these are timesheets between those named sheets) for employees. But I need the count to reset on their hire anniversary month and day. Employee 1 - Hired on 1/2/2001 (found in cell B5 on the sheet named "Summary") Employee 2 - Hired on 4/9/2020 (found in cell B6 on sheet named "Summary") In this example, any vacation days in cell F5 (employee 1) across multiple sheets between sheets named "Start" to "All" will count until the anniversary month and day arrives, then it will reset and start counting forward again until that date arrives again. Any help will be much appreciated!SolvedLisaB1009May 20, 2025Copper Contributor237Views0likes10CommentsShapes Moving Even When set to Do Not Move
I use Excel 365 Enterprise 4 edition. I created a file that is stored on a Teams site that contains shapes I use as buttons for my macros. Every time I open the file the shapes have moved and/or resized and I can't figure out how to prevent this. I have the shapes set to Don't move or size with cells set on each one of them. Users are not moving them. No one is playing any pranks, I wish they were it be a lot easier to fix. Anyone have any ideas?Solved36Views0likes2Comments
Resources
Tags
- excel42,710 Topics
- Formulas and Functions24,779 Topics
- Macros and VBA6,421 Topics
- office 3656,052 Topics
- Excel on Mac2,658 Topics
- BI & Data Analysis2,382 Topics
- Excel for web1,929 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,647 Topics