• 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.

Search results

  1. Hui

    Need formula or VBA to weed out unwanted numbers

    If you separate the criteria into 2 cells like Then in BL5 you can use something like: =COUNTIFS(B5:BI5,BJ5,B5:BI5,BK5) That returns 10 So I am not sure how the other checks work with 57 and 4 ?
  2. Hui

    How to Pull Data from a Date Range Based on Criteria

    E8: =SUMPRODUCT(('Daywise''2019'!E2:NE2<=$G$3)*('Daywise''2019'!E2:NE2>=$F$3)*('Daywise''2019'!$C$4:$C$55=$C8),'Daywise''2019'!$E$4:$NE$55) then Copy down Note that the ranges are of equal length/height now
  3. Hui

    Add 'n' numbers from a selected cell in user form pop-up window

    Try these and modify to suit Sub Sum_Range() Dim No_Cells As Long No_Cells = InputBox("Please enter the number of cells to Sum", "Sum Range") Dim sum_rng As Range Set sum_rng = Range("A1:A" & CStr(No_Cells)) Dim my_sum As Double my_sum =...
  4. Hui

    Multiple Combo Lists

    Anon1mous Please attach a sample file
  5. Hui

    How do i group dates automatically

    Assuming each group is 5 days long starting on 1 April 2018 In cell B2: =INT((A2-DATE(2018,4,1))/5) copy down
  6. Hui

    12 month rolling data - How do I produce a single arrow Icon to represent the total trend.

    Pivot Tables in Excel 2019 have the option somewhere to lock the formatting
  7. Hui

    How to create/generate a licence code for my excel file.

    There are a few systems around that will replace variables with words like ababababbba and abababababa Which makes reading and decythering very difficult, but not impossible
  8. Hui

    Dynamic formula again.

    First, I'd go away fr5om Week Numbers to dates. Why what happens to the dates like 30 August? Second, E4: =IF(AND($C4<=F$3,$D4>E$3),$B4,"") Third: Fix up the Month Tiles, to align with the months better See attached
  9. Hui

    How do I return a Yes or No response?

    =IF(COUNTIF(A1:T1,"<>")>0,"Yes","No") That is a <> with " around them, no spaces
  10. Hui

    Happy 10th Anniversary Chandoo.org Forums

    Today on July 26, 2009 the Chandoo.org Forums were turned on. So after 10 years, 39,144 threads, 246,519 messages and with 51,201 members a big Happy Anniversary is in order Happy 10th Anniversary Chandoo.org Forums I look forward to the next 10 Hui...
  11. Hui

    Future date

    Thomas You are trying to make an Array based on Today() and a number of months ahead of that, and then lookup the value of A2 in that array Unless they conicide it will not match Shouldn't C2 simply be : =EDATE(A2,B2)
  12. Hui

    How to replace the zeros of an array?

    =if( {0,0,6,7}<>0,{0,0,6,7}) Ctrl+Shift+Enter
  13. 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...
  14. 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...
  15. 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
  16. 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...
  17. 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
  18. 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
  19. 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...
  20. Hui

    change background color of new row

    CF, override cells colors and so need to be compatible with your requirements
  21. 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
  22. 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...
  23. 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
  24. 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