SUMIF and Linking Cells

[NaumanJ]

Hi there,

I am using the COUNTIF formula to count the different cells to get a final amount. While this is counting everything correctly and giving me the correct final amount, I wish to have all the cells that have been counted to be linked to the final value cell (when it is double clicked, to provide detailed data on the values). Let me explain further to make this make sense.

This is what the final inventory count table is going to look like:

	Week 4	Week 3	Week 2	Week 1
New Escalations		2
Resolved Escalations
Active Escalations

The data from my source looks like this:

Order #	Customer Name	Reason Code	Status	Comments	Create Date	Create Date (Week)	Resolution Date	Resolution Date (Week)
1​		First Code	Complete	Misc.	1-Jan-20​	Week 1	1-Feb-20​	Week 5
2​	Company 2	Second Code	Complete	Misc.	15-Jan-20​	Week 3	12-Feb-20​	Week 7
3​	Company 3	First Code	Complete	Misc.	17-Jan-20​	Week 3	2-Feb-20​	Week 5
4​	Company 4	Third Code	Active	Misc.	1-Feb-20​	Week 5
5​	Company 5	Second Code	Active	Misc.	10-Feb-20​	Week 7

What I'm doing is:
- Counting the amount of orders that will meet the criteria from the source data to populate the inventory count table. For example, in the source data, if "Create Date (Week)" is equal to "Week 3", it will tell me how many times "Week 3" comes up in the entire column.

Here is what I want to build on:
- As we can see, "Week 3" comes up twice in the source data under the "Create Date (Week)" column. This will populate the cells merging "New Escalations" and "Week 3" in the inventory count table with "2" (ie. cell C2). What I wish to have is the intelligence / functionality to have the 2 rows that had "Week 3" linked to the cell that says "2". Thus, if somebody clicks the 2, it will open up a new worksheet / same worksheet with the two data points (so it will provide the detailed data pertaining to the value).

I am really desperate for help with this problem. Can you please let me know if this is possible? How can I go about it? Is there another approach I should be using? PLEASE help me - anything will be appreciated!!

 

[NaumanJ]

Hey Yongle, I've been trying to get this to work for the past few hours and have been unsuccessful.

I just wanted to know if you were planning on assisting? Only asking so I can scratch this option off and begin looking at other resources.

I am really sorry if I'm coming across as selfish, I promise that isn't my intent. Please let me know if you get a chance.

Stay safe and thank you

 

[Yongle]

yes I am more than happy to help you but it will not be until tomorrow

 

[NaumanJ]

No rush at all, thank you SO much truly!!

 

[Yongle]

Please post the code for the recorded macro that provides the combination of filtering that you require

 

[NaumanJ]

Here is the code when I record it:

For "New Escalations" filtering:

Sub Macro6()

    Rows("17:17").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$17:$L$22").AutoFilter Field:=7, Criteria1:="Week 3"
    ActiveSheet.Range("$A$17:$L$22").AutoFilter Field:=3, Criteria1:= _
        "Second Code"
End Sub

Here is how my sheet looks:

		Week 4	Week 3	Week 2	Week 1
First Code	New Escalations
First Code	Resolved Escalations
First Code	Active Escalations
Second Code	New Escalations
Second Code	Resolved Escalations
Second Code	Active Escalations
Third Code	New Escalations
Third Code	Resolved Escalations
Third Code	Active Escalations
Order #	Customer Name	Reason Code	Status	Comments	Create Date	Create Date (Week)	Resolution Date	Resolution Date (Week)
1​		First Code	Complete	Misc.	1-Jan-20​	Week 1	1-Feb-20​	Week 5
2​	Company 2	Second Code	Complete	Misc.	15-Jan-20​	Week 3	12-Feb-20​	Week 7
3​	Company 3	First Code	Complete	Misc.	17-Jan-20​	Week 3	2-Feb-20​	Week 5
4​	Company 4	Third Code	Active	Misc.	1-Feb-20​	Week 5		-
5​	Company 5	Second Code	Active	Misc.	10-Feb-20​	Week 7		-

Here is what I'm trying to do:
- Beforehand, we had three rows because we only had 2 criteria (the week, which is the column) and the 1st row (New, Resolved or Active)
- Now, we have 3 criteria - 1) the week, which is the column; 2) the 2nd row (New, Resolved or Active); and 3) 1st row (Reason Code)

So essentially, I'm trying to get it to do the following (examples):
- If I select the cells connecting "First Code", "Week 1" and "New Escalations", filter the table's Reason Code heading = "First Code" and Create Date (Week) = "Week 1"
- If I select the cells connecting "First Code", "Week 1" and "Resolved Escalations", filter the table's Reason Code heading = "First Code" and Resolution Date (Week) = "Week 1"
- If I select the cells connecting "First Code", "Week 1" and "Active Escalations", filter the table's Reason Code heading = "First Code", Create Date (Week) <= "Week 1", Resolution Date (Week) > "Week 1"

Please let me know if this makes sense

 

[Yongle]

ok will look at it later

 

[NaumanJ]

Awesome - many thanks !

 

[NaumanJ]

Hey Yongle,

No rush or pressure - was just wondering if you had a chance to look at this yet?

 

[Yongle]

You realy cannot resist can you
- a reminder is pressure
- the more you do this the less likely you are to get a solution
- there are much more important things going on here and a lot of the world than your problem right now

yes I have looked at your problem

 

[Yongle]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge > 1 Then Exit Sub
    
    Dim xWeek As String, xScal As String, xCode As String
    Dim crit7 As String, crit9a As String, crit9b As String
    Dim critRng As Range, dataRng As Range
    
    Set critRng = Range("C2").Resize(9, Columns.Count - 2)
    Set dataRng = Range("A14").CurrentRegion
    xWeek = Cells(1, Target.Column)
    xScal = Cells(Target.Row, 2)
    xCode = Cells(Target.Row, 1)
    crit9b = "ZZZZZ"
    
    If Not Intersect(Target, critRng) Is Nothing Then
        Select Case xScal
            Case "New Escalations"
                crit7 = xWeek
                crit9a = "*"
            Case "Resolved Escalations"
                crit7 = "*"
                crit9a = xWeek
            Case "Active Escalations"
                crit7 = "<=" & xWeek
                crit9a = ">=" & xWeek
                crit9b = "="
        End Select
        With dataRng
            .AutoFilter
            .AutoFilter Field:=3, Criteria1:=xCode, Operator:=xlAnd
            .AutoFilter Field:=7, Criteria1:=crit7, Operator:=xlAnd
            .AutoFilter Field:=9, Criteria1:=crit9a, Operator:=xlOr, Criteria2:=crit9b
        End With
    End If
End Sub

Amend to match your data
The code is based on the layout in picture below