Forum Widgets
Latest Discussions
Conditional Formatting Rules randomly being deleted, not by a person, on shared workbook
I have an Excel workbook with multiple tabs that have different Pivot Tables based off of the same master table on its own sheet. I have been able to successfully create Conditional Formatting rules on a Pivot table using a formula before applying the rule. However, the rules that use formulas (not the ones for example based on a single cell value) are deleted from the sheet at what seems to be random times. I have confirmed that no individual is deleting this (on purpose). This doesn't seem to happen when I have my own sheet no matter how I change or update the data. i have also shared the sheet via OneDrive with another person and neither of us could get this to occur. But the live workbook, which is located in a Document Library in SharePoint (MS 365, several users also sync this library to OneDrive) and we have multiple users editing the master table and refreshing the pivot tables throughout the day either from the online version or in the desktop app. This seems to be specific to the sheets on SharePoint/OneDrive and that are using formulas in the conditional formatting rules. I can't find anything that talks about this in my searches so wanted to see if 1) anyone has also experienced this and 2) Any ideas if this can be solved or how to solve it? Here's an example of how the rules are setup. The formula ones are the ones that get deleted at random. I've tried other variations of the formulas as well and am pretty sure at this point that its the use of a formula, not the structure of the formula.
test
Data was exported from one server and has to be imported into another server. The servers accept the information but it has to be re-formatted to be accepted and not throw errors. The down load server export sheet contains a cell of information that will not be accepted by the upload server. It has the header "ITEM SPECIFICS". It generates two columns of data (left & right) the left is the data title or data term. It is followed by a ":" The right side is the associated tombstone data. It is followed by a ",". The column has over 100 terms. If there is no associated data with the term, the ":" is immediately followed by a comma ",".
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
