• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Recent content by Hui

  1. Hui

    How to replace the zeros of an array?

    =if( {0,0,6,7}<>0,{0,0,6,7}) Ctrl+Shift+Enter
  2. Hui

    Create report from the table by the format of the cells

    I would look at Peter's example and work through the data flow that you need, see where/how it is handled in Peters model There are times where Names don't appear as simple as maybe you think it should be But the logic of what peter Has setup is flexible and extensible in that it allows you to...
  3. Hui

    Create report from the table by the format of the cells

    Daniel taught that Names are actually more correctly Named Formulae and that all Named Formulae are Array Formula ie: A100 is actually a Named Formula referencing some memory that has a value or formula that you associate to a logical grid position of A100 Daniel went into great lengths in the...
  4. Hui

    Display cell content in another cell by selecting the cell.

    Dermot Firstly, Welcome to the Chandoo.org Forums You can't do what you want without some VBA Now that I have said that, somebody will show us how
  5. Hui

    Monthly totals

    Derek Firstly, Welcome to the Chandoo.org Forums Have a try of these O7: =SUMPRODUCT(($E$7:$E$69>=DATEVALUE("1"&N$7&$N$6))*($E$7:$E$69<=EOMONTH(DATEVALUE(1&N$7&$N$6),0))*$K$7:$K$69) O8...
  6. Hui

    Fill the shape with color if the text inside it is similar to text in a clicked cell.

    Kenducot Firstly, Welcome to the Chandoo.org Forums Your example is unclear? What is the link between Rectangle 20, and MMM and cell A3 and the color? What do you want to happen and when ? Please attach an actual Excel file, not a picture
  7. Hui

    Need Help With Random Population

    You need to do a search for "Dynamic Data Validation" from the main Chandoo.org page https://chandoo.org/wp/?s=Dynamic+data+validation It has been discussed here several times
  8. Hui

    Need Help With Random Population

    Ham123 Firstly, Welcome to the Chandoo.org Forums Do you want to choose 495 of the 610 items from the first list ? I'll assume items can';t repeat I would add a helper column to the original data and simply have the formula =rand() in that column Then in the destination use a formula to...
  9. Hui

    change background color of new row

    CF, override cells colors and so need to be compatible with your requirements
  10. Hui

    change background color of new row

    Private Sub CommandButton1_Click() Dim r As Integer r = ActiveCell.Row + 1 Rows(r).Insert shift:=xlShiftDown ActiveSheet.Cells(r, 1).Value = vbCrLf & "New " & vbCrLf & "Row" & vbCrLf Range(Cells(r, 2), Cells(r, 15)).Interior.Color = vbYellow Unload Me UserForm8.Show End Sub
  11. Hui

    Conditional formatting - Only format days of the month, not previous or next

    Using June as an Example June is J16: P21 Select that edit the Orange CF Use this modified formula =AND(MONTH($J$14)=MONTH(J16),OR(B7=$R$37,B7=$R$39,B7=$R$40,B7=$R$41,B7=$R$42,B7=$R$43,B7=$R$44,B7=$R$45,B7=$R$46)) etc ie: Use the Month of the Month and Month of the Date as an extra check You...
  12. Hui

    Help needed with SUMPRODUCT/SUMIF across multiple columns with criteria

    Oahmed Can you please start a new post and attach a sample file to simplify the solution
  13. Hui

    How to manage an error

    AS debaser described, the On error is still active once triggered If another error occurs it will go back to 10 so you need to a reset statement as described into the end of the error handling to stop that behaviour
  14. Hui

    How to manage an error

    The below is what you want I'm not sure where you want to jump back to, so adjust accordingly Do Until surplus = 0 adr = "AF" & lmin & ":AF" & lmax & "" If lmax - lmin = 0 Then min = Application.WorksheetFunction.Subtotal(5...
  15. Hui

    Excel Doughnut Chart With Round Edges

    You can read more about how this was made here: https://chandoo.org/wp/learn-how-to-make-a-circular-arc-chart-in-excel/