User Profile
Haytham Amairah
Silver Contributor
Joined 9 years ago
User Widgets
Recent Discussions
Re: Formula: Count if the ticket is in particular time, date and channel
little2fern Hi Fern, You can go with the solution suggested by Riny_van_Eekelen. But I would expand my suggested formula the handle the additional criteria you mentioned. To get this analysis done, you need to extract all channels you need from Tags columns in a separate column. You can do that with this formula below and also with the formula suggested by Riny_van_Eekelen. =IF(SUMPRODUCT(IFERROR(SEARCH($H$5:$H$9,D2),0)),INDEX($H$5:$H$9,MATCH(TRUE,ISNUMBER(SEARCH($H$5:$H$9,D2)),0)),"") After that, you can expand the COUNTIFS function to take this additional column into account as follows: =COUNTIFS('2020 All Tickets'!$B$2:$B$16199,">="&$B$3, '2020 All Tickets'!$B$2:$B$16199,"<="&$B$4, '2020 All Tickets'!$C$2:$C$16199,">="&$K3, '2020 All Tickets'!$C$2:$C$16199,"<="&$L3, '2020 All Tickets'!$E$2:$E$16199,E$2) Hope that helpsRe: Formula: Count if the ticket is in particular time, date and channel
little2fern Hi, Please try this formula: =COUNTIFS('2020 All Tickets'!$B$2:$B$16199,">="&$B$3, '2020 All Tickets'!$B$2:$B$16199,"<="&$B$4, '2020 All Tickets'!$C$2:$C$16199,">="&K3, '2020 All Tickets'!$C$2:$C$16199,"<="&L3) To simplify the process, I've created two columns (From/To) next to the table. Hope that helpsRe: Excel spreadsheet
Derek1940 Hi Derek, There are several reasons to have this triangle. You will see this triangle when your data violate the error checking rules in Excel. You can see these rules in Excel Options as follows: To know which rule of them your formula violates, click the dropdown list next to the cell: Hope that helps RegardsRe: Use VBA to Autofill a Row until the end of the number of data in another row
Thomas2170 Hi, Please try this code: Sub AutoFillToRight() Dim lastCellFromRight As String ActiveCell.Select lastCellFromRight = Selection.End(xlDown).End(xlToRight).Offset(-1, 0).Address If lastCellFromRight = "$XFD$1048575" Or lastCellFromRight = "$IV$65535" Then Exit Sub Range(ActiveCell.Address, lastCellFromRight).FillRight Range(lastCellFromRight).Select End Sub But you need to select the starting cell before you run it which in the example below cell (E3): Hope that helpsRe: Microsoft Excel keeping all cells uppercase
richbraithwaite Hi, Please try the code on column C. The code is somehow applied to all columns not only column A! To limit it to column A & C, try this one instead: Private Sub Worksheet_Activate() On Error Resume Next Application.ScreenUpdating = False Union(Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row), _ Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)).Select Dim cell As Range For Each cell In Selection cell.Value = UCase(cell.Value) Next cell Range("A1").Select On Error GoTo 0 Application.ScreenUpdating = True End SubRe: VBA HELP NEEDED
Wit_Emperor Hi, Please try this one: Sub FillFromStartingToEnding() Dim starting As Integer Dim ending As Integer starting = Right(Range("B1").Value, 3) ending = Right(Range("B2").Value, 3) Range(Range("D2"), Range("D2").Offset(ending - 1, 0)).Select Selection.Value = "MDN" For Each cell In Selection cell.Value = cell.Value & Application.WorksheetFunction.Text(starting, "000") starting = starting + 1 Next End Sub Hope that helpsRe: Macro for printing from dropdown menu
marc47555 Hi, Please try this code: Sub PrintOutAllDepots() Dim r As Range Set r = Sheets("Orders").Range("D4:P4") Dim i As Integer i = 1 Sheets("Pallet Card").Activate For Each c In r Range("R1").Value = r(1, i).Value ActiveWindow.SelectedSheets.PrintOut Copies:=1 i = i + 1 Next c End Sub Hope that helpsRe: Click on a cell to open worksheet
warrennel001 If you want to do this natively in Excel, the only way is to create a worksheet containing all calculations you need and the result you want to show in that cell. Then link the resultant cell to the current sheet. But the new sheet cannot pop up on top of the current sheet, you can only move to it using hyperlinks. In the attached workbook, I've tried to approach that in sheet 1 and sheet 2. In sheet 1 / cell A1, I used a hidden shape and linked it to cell C3 of sheet 2. Where cell C3 holds the total result. So, when you click that shape, the link moves you to the next sheet to do any adjustments. Behind the shape in cell A1 of sheet 1, you will see the linked result, it's done using this formula: =Sheet2!C1 Hope that makes senseRe: Click on a cell to open worksheet
warrennel001 Hi, Could you explain this part of your question? The answer to that complex calculation on that new or embedded sheet appears in the cell when closed However, to open another worksheet from a cell, you can use hyperlinks, to learn more please check out these resources: https://www.youtube.com/watch?v=5O_bAkKZ5D4 https://www.ablebits.com/office-addins-blog/2014/05/15/excel-insert-hyperlink/ Hope that helps
