Forum Widgets
Latest Discussions
Need help with my TEXTJOIN Formula
Hi all, I'm new to this forum and i have a question regarding this formula =TEXTVERKETTEN(", "; WAHR; FILTER( 'Training Matrix'!$I$4:$AT$4; // training names ('Internal Tracking'!$A$4:$A$85 = $A5) * // Match employee name ISTZAHL('Internal Tracking'!$E$4:$CX$85) * // Check for completion date (INDEX('Training Matrix'!$I$5:$AT$92; VERGLEICH('Internal Tracking'!$E$3; 'Training Matrix'!$B$5:$B$92; 0); VERGLEICH(TEXT('Internal Tracking'!$B$4:$B$85; "0") & "-" & TEXT('Internal Tracking'!$D$4:$D$85; "0"); 'Training Matrix'!$I$4:$AT$4; 0)) = "M"); "" )) which is in english =TEXTJOIN(", ", TRUE, FILTER( 'Training Matrix'!$I$4:$AT$4, // training names ('Internal Tracking'!$A$4:$A$85 = $A5) // Match employee name ISNUMBER('Internal Tracking'!$E$4:$CX$85) // Check for completion date (INDEX('Training Matrix'!$I$5:$AT$92, MATCH('Internal Tracking'!$E$3, 'Training Matrix'!$B$5:$B$92, 0), MATCH(TEXT('Internal Tracking'!$B$4:$B$85, "0") & "-" & TEXT('Internal Tracking'!$D$4:$D$85, "0"), 'Training Matrix'!$I$4:$AT$4, 0)) = "M"), "" )) I'm using a german excel so I'm working with the first one , regardless it gives an #WERT error which I haven't been able to fix, the components of the formula such as ISTZAHL(ISnumber) and Vergleich(Compare) works and I can't see where the mistake is . To give a context on the formula and what I'm trying to achieve : I have 3 sheets , the first one is the 'Training Matrix' .The Training Matrix is where you can see if a training according to role-section (in the header row) is mandatory (marked with a "M" ) or extra (marked with a "E").The trainings also differ according to section. As an example Engineer-SectionA has different mandatory/extra trainings than Engineer-SectionB, they are both engineers but because they are in different sections they have different trainings assigned as mandatory . In the screen shot the sections are blurred , but in the row 4 which begins from I column the format is as such "role-section". Training Name= B5:B92 , Role-Section=I4:AY4 , M/E cells=I5:AY92 In the Internal Tracking sheet you can see if an employee has completed a training.If he/she completes it , there is a date in the cell , so the cells with no date indicate that the training is NOT completed and with conditional formating you can see if the completed training is mandatory or not but this information is not relevant for the formula. Employee Name is in Column A4 , Employee Role in B4 , Section in D4, Training Names range : E3:E88 , date cells range : E4:CN85 What I'm trying to accomplish is in this sheet : Using the formula , I want to retrive the name of the completed trainings for each employee.Employee Name is in A5:A86 , role in B5:B86 and section in D5:D86 in this sheet. So for each person the completed mandatory trainings needs to be listed using the formula =TEXTVERKETTEN(", "; WAHR; FILTER( 'Training Matrix'!$I$4:$AT$4; // training names ('Internal Tracking'!$A$4:$A$85 = $A5) * // Match employee name ISTZAHL('Internal Tracking'!$E$4:$CX$85) * // Check for completion date (INDEX('Training Matrix'!$I$5:$AT$92; VERGLEICH('Internal Tracking'!$E$3; 'Training Matrix'!$B$5:$B$92; 0); VERGLEICH(TEXT('Internal Tracking'!$B$4:$B$85; "0") & "-" & TEXT('Internal Tracking'!$D$4:$D$85; "0"); 'Training Matrix'!$I$4:$AT$4; 0)) = "M"); "" )) but it doesn't work , can someone help me out ? Thanks in advance !
Drag and drop values from a Workbook to another
When copying a value (as a result of a calculation) from one workbook to another and then drag and drop a series of results is not functioning, only the first value is copied to all cells. This worked before but now it only works if you do it from another worksheet in the same workbook. This worked before so how to solve?Conditional Formatting for Quarters & Years
Hi all, I had a spreadsheet that had months along the top and used conditional formatting to colour in the cells between the start and finish date using the formula:=AND($C5<=E$2,$D5>=E$2) and it worked perfectly. I've now been asked to replace the months with Quarters and so based on this, I've tried to add in 2 further columns with the Start & finish year, and then the Quarter Start and Finish Year in the hope that I could just change the AND formula to include all 4 conditions =AND($C$13<=G$3,$D$13>=G$3,$E$13<=G$2,$F$13>=G$2) however I can't seem to get this to work? Is there any way to get the conditional formatting to colour in all cells that are within both the start and finish years as well as the start Quarter start & Quarter finish? Thanks in advance!
Formula Help Required
Good Morning, I have started creating a sheet which works out transport weight and packaging required based on orders from our customer. I am after a formula to look at the Packaging Type and the quantities required and compile a packaging order list. The sheet would then tell me how many H Lids etc and then how many R lids etc. Sheet attached. I am looking to put the formula in boxes B27 to E30. Cheers
Need help reversing a formula
I'm trying to create a formula that will take a price from one cell and convert it to a letter code. I found a formula that does the opposite, it takes a letter code and converts it to a price. The pricing code is PATHFINDER (whereas P=1, A=2, T=3, H=4, F=5, I=6, N=7, D=8, E=9, and R=0) So, $12.45 would be PAHF, $5.60 would be FIR, etc. Here is the formula I used to convert a letter code to prices. =LET(l, LEN(C3), letters, MID(C3, SEQUENCE(l), 1), numbers, CONCAT(SWITCH(letters, "P", 1, "A", 2, "T", 3, "H", 4, "F", 5, "I", 6, "N", 7, "D", 8, "E", 9, "R", 0)), numbers / 100) In this formula, C3 had the text "PAHF" and the formula produced $12.45. Please help me find a way to take a price and convert it to the letter code instead (the opposite of what I did, essentially).Need some help with pulling data from a table
So, I'm having a bit of trouble pulling a list of names from one table, onto another table. I have a table on a worksheet named 'I', where all my data and info is, and the table is named 'Shops'. In that table, are all the shops names, and below each shop is all the employee's from that shop. I have another table on another worksheet named 'Store'. I haven't named that table yet (do I need to?). Above that table, in cell D1, is a dropdown list, which is used to select the shop from the Shops table, and I'm trying to figure out how to populate cells A5 to A24 on the Store sheet, with the employee names from the Shops table, dependant on which shop was selected in the dropdown list. I have included a couple of screenshots, in the hopes that they will help. Any help on this would be greatly appreciated! Ignore the sizing of some of these cells, as it's due to data below that needed resizing.
