# Search results

1. ### 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. ### 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. ### 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. ### Multiple Combo Lists

Anon1mous Please attach a sample file
5. ### 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. ### 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. ### 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. ### 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. ### How do I return a Yes or No response?

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

.
11. ### 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...
12. ### 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)
13. ### How to replace the zeros of an array?

=if( {0,0,6,7}<>0,{0,0,6,7}) Ctrl+Shift+Enter
14. ### 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...
15. ### 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...
16. ### 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
17. ### 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...
18. ### 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
19. ### 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
20. ### 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...
21. ### change background color of new row

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