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!!

 

[Yongle]

Unless I am missing something, the obvious way to get the detail would be to Filter the data based on criteria that Create Date (Week) = Week 3 which would leave only the 2 correct rows visible

 

[Yongle]

Following up on my previous post, with everything in the one sheet which makes it easy to illustrate here

Book1
	A	B	C	D	E	F	G	H	I
1		Week 4	Week 3	Week 2	Week 1
2	New Escalations		2
3	Resolved Escalations
4	Active Escalations
5
6
7
8	Order #	Customer Name	Reason Code	Status	Comments	Create Date	Create Date (Week)	Resolution Date	Resolution Date (Week)
9	1		First Code	Complete	Misc.	01/01/2020	Week 1	01/02/2020	Week 5
10	2	Company 2	Second Code	Complete	Misc.	15/01/2020	Week 3	12/02/2020	Week 7
11	3	Company 3	First Code	Complete	Misc.	17/01/2020	Week 3	02/02/2020	Week 5
12	4	Company 4	Third Code	Active	Misc.	01/02/2020	Week 5
13	5	Company 5	Second Code	Active	Misc.	10/02/2020	Week 7
14
15
Sheet1

and this code in the sheet code window (not in a module - will not work there)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge > 1 Then Exit Sub
    Dim rng As Range, myCriteria As String, myField As Long
    Set rng = Cells(2, 2).Resize(3, Columns.Count - 1)
    If Not Intersect(Target, rng) Is Nothing Then
        
        Select Case Target.Row
            Case 2: myField = 7:    myCriteria = Cells(1, Target.Column)
            Case 3: myField = 9:    myCriteria = Cells(1, Target.Column)
            Case 4: myField = 4:    myCriteria = "Active"
        End Select
        
        Range("A8").AutoFilter
        Range("A8").CurrentRegion.AutoFilter Field:=myField, Criteria1:=myCriteria
    End If
End Sub

Clicking on the cells in rows 2 and 3 should filter the data the way you require
eg clicking on cell C2 returns the table with rows 10 and 11 visible

Clicking in row 4 returns the currently active cases rather than the ones current at the time
- if you want the ones active in a specific week then filter accordingly
- ie row 1 value is > = create week and row 1 value <= resolution week
- you may need to use dates to make that work

Hopefully this has given you a workable starting point

 

[NaumanJ]

Yongle, you are the absolute best!!! I did not even know this was possible, you have significantly helped me with this!

This might be asking for too much, so it's perfectly fine if you say No! For the Active Escalations, as you mentioned, it is currently filtering the "Status" column for "Active", which is fine for the current week but not past weeks. I'm trying to adjust the VBA code to change the criteria but have been unsuccessful I was wondering if you could please provide me the format I can enter the code? This is what the criteria should be:
- If the Create Week is less than or equal to Target Column week, and Resolution Week is greater than Target Column week, then it is an Active Escalation

Can you please tell me how I would go about having that criteria?

If you need dates, this is how the Weeks are classified:

Week	Start Date	End Date
Week 1	1/1/2020 12:00:00 AM	1/5/2020 11:59:59 PM
Week 2	1/6/2020 12:00:00 AM	1/12/2020 11:59:59 PM
Week 3	1/13/2020 12:00:00 AM	1/19/2020 11:59:59 PM
Week 4	1/20/2020 12:00:00 AM	1/26/2020 11:59:59 PM
Week 5	1/27/2020 12:00:00 AM	2/2/2020 11:59:59 PM

Thanks a million - I can't express how much I appreciate this!

 

[Yongle]

Try this

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge > 1 Then Exit Sub
    Dim rng As Range, myCriteria As String, myField As Long, myCriteria2 As String, myField2 As Long
    Set rng = Cells(2, 2).Resize(3, Columns.Count - 1)
    If Not Intersect(Target, rng) Is Nothing Then
        Select Case Target.Row
            Case 2: myField = 7:    myCriteria = Cells(1, Target.Column)
            Case 3: myField = 9:    myCriteria = Cells(1, Target.Column)
            Case 4: myField = 7:    myCriteria = "<=" & Cells(1, Target.Column)
                    myField2 = 9:   myCriteria2 = ">=" & Cells(1, Target.Column)
        End Select
        
        Range("A8").AutoFilter
        Range("A8").CurrentRegion.AutoFilter Field:=myField, Criteria1:=myCriteria, Operator:=xlAnd
        If myField2 > 0 Then Range("A8").CurrentRegion.AutoFilter Field:=myField2, Criteria1:=myCriteria2, Operator:=xlAnd
    End If
End Sub

 

[NaumanJ]

Wow - you are the absolute best!! You're an Excel guru, thank you so so much for this!

I have one FINAL question, and while I know it's asking for way too much for your support on this, you're the only one who seems to be able to help me. I tried to do this ask myself, consulted numerous friends and searched online, but I was unable to You definitely don't have to help me because you've already done so much, but here goes:

Previously, I was using the Week (the columns) as the only filters. If I wish to use both the Column and Row as a filter, would that be possible? It would be the same table, but take into account "Reason Code". It would look like this:

The reason code (from the Detailed Data) would be in column 3: "Reason Code"

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

Once again, thank you SO much for all your help so far. If possible, please assist me with one final time, though I understand if you can't because I'm asking for too much. Many thanks in advance

 

[Yongle]

Additional criteria are required to mimic what happens when filtering is performed manually
Record Macro is useful for something like this
Click Record Macro and filter manually using all criteria that you need
Cick on Stop
Examine what has been recorded by Excel and amend the code provided in post#5

 

[NaumanJ]

I've tried to use the "Record Macro" function, and also the code you used earlier to account for the additional criteria, but it is giving me an error

Here's how the sheet looks like:

However, with the way I've adjusted the code, it gives me this error:

This is the adjusted code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.CountLarge > 1 Then Exit Sub
    Dim rng As Range, myCriteria As String, myField As Long, myCriteria2 As String, myField2 As Long, myCriteria3 As String, myField3 As Long
    Set rng = Cells(2, 2).Resize(9, Columns.Count - 1)
    If Not Intersect(Target, rng) Is Nothing Then
        Select Case Target.Row
            Case 2: myField = 7:    myCriteria = Cells(1, Target.Column)
                    myField2 = 3:   myCriteria2 = Cells(Target.Row, 1)
            Case 3: myField = 9:    myCriteria = Cells(1, Target.Column)
                    myField2 = 3:   myCriteria2 = Cells(Target.Row, 1)
            Case 4: myField = 7:    myCriteria = "<=" & Cells(1, Target.Column)
                    myField2 = 9:   myCriteria2 = ">=" & Cells(1, Target.Column)
                    myField3 = 3:   myCriteria3 = Cells(Target.Row, 1)
        End Select
        
        Range("A17").AutoFilter
        Range("A17").CurrentRegion.AutoFilter Field:=myField, Criteria1:=myCriteria, Operator:=xlAnd
        If myField2 > 0 Then Range("A17").CurrentRegion.AutoFilter Field:=myField2, Criteria1:=myCriteria2, Operator:=xlAnd
        If myField3 > 0 Then Range("A17").CurrentRegion.AutoFilter Field:=myField3, Criteria1:=myCriteria3, Operator:=xlAnd
    End If
End Sub

Do you have any guidance? Sorry for all this trouble, I'm just in a tight deadline and freaking out

 

[Yongle]

I'm just in a tight deadline and freaking out

I am a volunteer here and will not take any pressure from anyone however nicely worded
You could have explained your ultimate goal in the first post
Expecting ongoing help as you make the problem ever more complex is not fair on the person trying to help you

 

[NaumanJ]

Definitely, Yongle. I truly appreciate all your support with this - it helped me significantly.

I didn't mean to pressure you at all - my apologies if it seemed like so.

Please, if you're able to help (on your own time, no rush at all), please let me know.

Sorry for showing bad character, I didn't intend to