ExcelSujeet (YouTube Blogger)

Array Formula - Find Date & Time Just Greater than a Date & Time Array Formula =SMALL(($B$3:$B$10=$F$3)*($C$3:$C$10>$G$3)*($C$3:$C$10),SUM(IF(($B$3:$B$10=$F$3)*($C$3:$C$10>$G$3)*($C$3:$C$10)=0,1,0))+1) Paste the formula in a cell and press Ctrl + Enter on the keyboard to make it an array formula.

 Control Pivot Table's Page Filters Using VBA Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim str_Location As String, str_Region As String If Application.Intersect(Target, Range("B1:C2")) Is Nothing Then Exit Sub 'MsgBox "Yes, it intersect." If Sheet1.Range("B1").Value <> "Location" Then     Sheet1.Range("B1").Value = "Location" End If If Sheet1.Range("C1").Value <> "Region" Then     Sheet1.Range("C1").Value = "Region" End If str_Location = Sheet1.Range("B2").Value str_Region = Sheet1.Range("C2").Value If str_Location <> "All" Or str_Region <> "All" Then     Sheet2.PivotTables("ThisPivotTable1").PivotFields("Location").ClearAllFilters     Sheet2.PivotTables("ThisPivotTable1").PivotFields("Location").CurrentPage = str_Location          Sheet2.PivotTables(...

Excel Array Formulas Example-1 a) Formula in Col E (Press Ctrl + Shift + Enter to make it an Array Formula)     =IFERROR(INDEX($B$3:$B$13,SMALL(IF(ISNUMBER(SEARCH($E$2,$B$3:$B$13)),ROW($B$3:$B$13)-2),ROWS($D$3:$D3)),1),"") b) Formula in Col G (Press Ctrl + Shift + Enter to make it an Array Formula) =IFERROR(INDEX($B$3:$B$13,MATCH(0,COUNTIF($G$2:$G2,$B$3:$B$13),0),1),"") c) Ignore blanks - Formula in Col G (Press  Ctrl + Shift + Enter  to make it an Array Formula) =IFERROR(INDEX($B$3:$B$12,MATCH(0,COUNTIF($D$2:$D2,$B$3:$B$12)+IF($B$3:$B$12="",1,0),0),1),"") d) Only text - Formula in Col G (Press  Ctrl + Shift + Enter  to make it an Array Formula) =IFERROR(INDEX($B$3:$B$12,MATCH(0,COUNTIF($F$2:$F2,$B$3:$B$12)+IF(ISTEXT($B$3:$B$12),0,1),0),1),"") e) Only numbers - Formula in Col G (Press  Ctrl + Shift + Enter  to make it an Array Formula) =IFERROR(INDEX($B$3:$B$12,MATCH(0,COUNTIF($H$2:$H2,$B$3:$B$12)+IF($B$3:$B$12="",1,0)+IF(IS...