User Profile
OliverScheurich
Gold Contributor
Joined 5 years ago
User Widgets
Recent Discussions
Re: Taking text from a multi-line cell and making each line its own row.
With legacy Excel such as Excel 2013 you can use Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well. =DROP(REDUCE("",L2:L6,LAMBDA(u,v,VSTACK(u,TEXTSPLIT(v,,CHAR(10))))),1) With Microsoft 365 or Excel online you can apply this formula.Re: A button to transpose data into a destination table
An alternative could be Power Query. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.Re: Formule avec condition
=8+0.8*SUM(COUNTIFS(INDIRECT("'"&A1:A4&"'!C12"),">5")) =8+0,8*SOMME(NB.SI.ENS(INDIRECT("'"&A1:A4&"'!C12");">5")) Does this formula work if you enter the sheetnames in range A1:A4? The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024. I've added the english formula because i'm not sure if the translation into french is correct.Re: Countif/Countifs
Sorry for the late reply. It seems that either i didn't receive a notification of your reply on Oct 06, 2025 or i was missing something. Please use the formula provided by djclements that is perfect for your task. I wasn't aware that this is possible. Attached is my sample file with both formulas however mine is redundant.Re: Conditional Formatting
Sorry for the late reply. It seems that either i didn't receive a notification of your reply on Oct 11, 2025 or i was missing something. Retaining the previous highlights can be done with VBA. The VBA worksheet change event code posted below returns the intended result in my sample file. All entries in range B2:AA157 that match the value in cell A3 are highlighted and the highlight is retained if the A3 value is cleared or changed. For some reason i can't attach my sample file currently. Without VBA this isn't possible as far as i know. Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cell As Range Set rng = Range("B2:AA157") If Not Intersect(Target, Range("A3")) Is Nothing Then For Each cell In rng If cell.Value = "" Then Else If cell.Interior.ColorIndex = 7 Then Else If cell.Value = Target.Value Then cell.Interior.ColorIndex = 7 Else End If End If End If Next Else End If End SubRe: Excel formula to determine missing numbers
I've already tried several times to reply but for an unknown reason my replies in the community weren't posted. In the screenshots i don't see the error message that's returned. However i see that there's a table named "Tabelle1" with headline "CASE NUMBER" and with a cell containing "----" (exactly 4 times "-") in sheet DATA ENTRY_ALL. Can you copy the M code from my attached file and then load your table into the Power Query Editor and paste the code into the Advanced Editor of your file. After Start -> Close and load the result should look like in my sheet.Re: Excel formula to determine missing numbers
An alternative could be Power Query which works in Excel 2013 and in newer versions. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. The data layout in the screenshot and in the attached file is for illustration. You can place the green result table in another worksheet as well.Re: Data Validation Conditional Formating
This is the rule for conditional formatting in the attached sample file. =(B3=$A$17)*ISNA(MATCH(B3,$H$3:$H$17,0)) This is the applies to range for conditional formatting. =$B$3:$B$14;$D$3:$D$14;$F$3:$F$14 In cell A17 i've added Data Validation without a dropdown. In the screenshot all cells containing "July" in ranges $B$3:$B$14;$D$3:$D$14;$F$3:$F$14 are highlighted in red after "July" was removed from range H3:H17.Re: SUM WITH INDEX MATCH ERROR
=SUM(MMULT(TRANSPOSE((A5:A9=C15)*(B5:B9=C16)),TRANSPOSE(MMULT((C1:N1=C13)*(C2:N2=C14),TRANSPOSE(C5:N9))))) This formula works in my sample file and in modern and legacy Excel. The formula must be entered as an arrayformula with ctrl+shift+enter if someone doesn't work with Office 365 or Excel for the web or Excel 2024.Re: How to summarize by date range and grouping customer items
=PIVOTBY(A2:B31,LET(_daterange,HSTACK( BYROW(C2:C31,LAMBDA(x,DATE(YEAR(x),MONTH(x)-1,DAY(I1)))), BYROW(C2:C31,LAMBDA(r,LET(y,EOMONTH(r,0),DATE(YEAR(y),MONTH(y),DAY(J1)))))), DROP(REDUCE("",C2:C31,LAMBDA(u,v, VSTACK(u, XLOOKUP(1, (v>=INDEX(_daterange,,1))*(v<=INDEX(_daterange,,2)), _daterange, HSTACK( DATE(YEAR(v),MONTH(v),DAY(I1)), LET(y,EOMONTH(v,1),DATE(YEAR(y),MONTH(y),DAY(J1)))))))),1)), D2:D31,SUM, ,0) This formula returns the intended result in my Excel online sample sheet.Re: Countif/Countifs
=SUM(COUNTIF(INDEX(C:C,@CHOOSECOLS(REGEXEXTRACT(TEXTSPLIT(C3,":"),"[0-9]+"),1)): INDEX(C:C,@CHOOSECOLS(REGEXEXTRACT(TEXTSPLIT(C3,":"),"[0-9]+"),2)),{6.12.23.60})) The above formula returns the expected result in Excel 365, Excel for the web and Excel 2024. We have to make sure that there isn't a circular reference. The formula is in cell C5 and dragged to the right in row 5. That's why we can only count the occurrences of {6.12.23.60} starting from row 6 in order to avoid a circular reference. This means that all ranges between C6:C1048576 are allowed. In your formula i see one occurrance of COUNTIF*COUNTIF but i assume you always want COUNTIF+COUNTIF.Re: Excel - Matching outgoing to incoming inventory based a specific conditions
Hello Christine, thank you for your detailed explanation. I understand the task and the formula in cell K7 returns the results. The formula must be filled down. In the screenshot i've hidden some rows and highlighted 3 unique combinations of "Supplier PO" and "SKU" for illustration. I've attached the sample file with 2 sheets with hidden and unhidden rows of the sample database. Regards, Oliver =IF( OR( AND(SUMIFS($V$7:$V$1000,$D$7:$D$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7) +SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)=0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0), AND(SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)+V7=0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0)), "closed", IF( OR(SUMIFS($V$7:$V$1000,$D$7:$D$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7) +SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,F7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<>0, AND(SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)+V7<>0, SUMIFS($AB$7:$AB$1000,$F$7:$F$1000,D7,$G$7:$G$1000,G7,$J$7:$J$1000,J7)<0)), "in progress", "") )
