Referring to a Specific Range of Data in a Table

[FrenchCelt]

Hello,

I have a table on one sheet and I would like to refer to a subset of that data on another sheet. Specifically, I have performance data broken down into categories (80%+, 60-79.99%, and Under 60%) and I want to list all the data that fall under the 60-79.99% category.

This is what the table looks like:

I have been manually copying and pasting just the 60-79.99% data in the Performance column at the far right after filtering for it in another sheet so I end up with this:

I'd like to automate this by inserting formulas that will directly refer to the table and only display the data subset that is labeled 60-79.99% so I don't have to keep copying and pasting every week. Does anyone know if this is possible? I could just whip up a macro that will run the steps of filtering the table and copying/pasting where I want it to go for me, but I'm becoming a fan of using permanently set formulas to simply update cells for me.

 

[noveske]

I would just make a sheet for 60-79.99%.
Assuming columns are A - F.
Then just hide G and on.
Use this as a module.

Screenshotted working. The first names won't match the first letter of username. But you get the idea.

Sub stuffs()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim lastRow As Long
Dim i As Long
Dim targetRow As Long
    
Set wsSource = ThisWorkbook.Worksheets("Sheet1")
Set wsTarget = ThisWorkbook.Worksheets("Sheet2")

wsTarget.UsedRange.Offset(1).ClearContents    

lastRow = wsSource.Cells(wsSource.Rows.Count, "F").End(xlUp).Row
    

targetRow = 1
    

wsSource.Rows(1).Copy wsTarget.Rows(targetRow)
targetRow = targetRow + 1
    
For i = 2 To lastRow
If wsSource.Range("F" & i).Value >= 0.6 And wsSource.Range("F" & i).Value <= 0.7999 Then
            wsSource.Rows(i).Copy wsTarget.Rows(targetRow)
targetRow = targetRow + 1
End If
 Next i
   
    
End Sub

When I made mine, I made it run on save. So everytime you hit save, it will update.


Added this to ThisWorkbook:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   
stuffs
    
End Sub

 

[noveske]

Updated.

This will create Sheet2 if it does not exist.
When creating or overwriting the output sheet, it will also copy over the formatting.
Results are then auto-sorted from high to low.

You could just make a sheet that will just keep a list of this range of performers.
Just change all the Sheet2's to something like 607999 or Mid. Every time you save, it will update that sheet. Same can be done for all the other performance ranges.

Sub stuffs()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim targetRow As Long
   

    Set wsSource = ThisWorkbook.Worksheets("Sheet1")
   

    On Error Resume Next
    Set wsTarget = ThisWorkbook.Worksheets("Sheet2")
    On Error GoTo 0
   
    If wsTarget Is Nothing Then

        Set wsTarget = ThisWorkbook.Worksheets.Add
        wsTarget.Name = "Sheet2"
    End If
   

    lastRow = wsSource.Cells(wsSource.Rows.Count, "F").End(xlUp).Row
   

    targetRow = 1
   

    If wsTarget.UsedRange.Rows.Count = 1 Then
        wsSource.Rows(1).Copy
        wsTarget.Rows(targetRow).PasteSpecial Paste:=xlPasteFormats
        wsTarget.Rows(targetRow).PasteSpecial Paste:=xlPasteColumnWidths
        wsTarget.Rows(targetRow).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        targetRow = targetRow + 1
    End If
   

    For i = 2 To lastRow
 
        If wsSource.Range("F" & i).Value >= 0.6 And wsSource.Range("F" & i).Value <= 0.7999 Then

            wsSource.Rows(i).Copy
            wsTarget.Rows(targetRow).PasteSpecial Paste:=xlPasteFormats
            wsTarget.Rows(targetRow).PasteSpecial Paste:=xlPasteColumnWidths
            wsTarget.Rows(targetRow).PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            targetRow = targetRow + 1
        End If
    Next i
   

    With wsTarget.Sort
        .SortFields.Clear
        .SortFields.Add Key:=wsTarget.Range("F:F"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange wsTarget.UsedRange
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   

    wsTarget.Activate

End Sub

 

[kevin9999]

I'd like to automate this by inserting formulas that will directly refer to the table

I note you have 365 on your account details, therefore the Filter() function should be available to you. If your second sheet is a range (not a table) but your original data is a table, then try the following (just change the table name from Table1 to whatever you called the table).
Sheet1

Book1
	A	B	C	D	E	F	G	H	I
1	TM Name	Dept	Shift	DOH	USERID	Perf%	On Std%	Eff%	Perfomance
2	data	data	data	data	data	79.70%	data	data	60-79.99%
3	data	data	data	data	data	65.90%	data	data	Below 60%
4	data	data	data	data	data	59.00%	data	data	Below 60%
5	data	data	data	data	data	65.90%	data	data	60-79.99%
6	data	data	data	data	data	65.90%	data	data	60-79.99%
7	data	data	data	data	data	65.90%	data	data	60-79.99%
8	data	data	data	data	data	65.90%	data	data	60-79.99%
9	data	data	data	data	data	65.90%	data	data	60-79.99%
10	data	data	data	data	data	45.00%	data	data	Below 60%
11	data	data	data	data	data	95.00%	data	data	80%+
12	data	data	data	data	data	95.00%	data	data	80%+
13	data	data	data	data	data	95.00%	data	data	80%+
14	data	data	data	data	data	95.00%	data	data	80%+
15	data	data	data	data	data	62.50%	data	data	60-79.99%
16	data	data	data	data	data	62.50%	data	data	60-79.99%
17	data	data	data	data	data	62.50%	data	data	60-79.99%
18	data	data	data	data	data	62.50%	data	data	60-79.99%
Sheet1

Sheet2

Book1
	A	B	C	D	E	F
1	60-79.99%
2	TM Name	Dept	Shift	DOH	USERID	Perf%
3	data	data	data	data	data	79.70%
4	data	data	data	data	data	65.90%
5	data	data	data	data	data	65.90%
6	data	data	data	data	data	65.90%
7	data	data	data	data	data	65.90%
8	data	data	data	data	data	65.90%
9	data	data	data	data	data	62.50%
10	data	data	data	data	data	62.50%
11	data	data	data	data	data	62.50%
12	data	data	data	data	data	62.50%
Sheet2

Cell Formulas
Range	Formula
A3:F12	A3	=FILTER(FILTER(Table1,Table1[Perfomance]="60-79.99%"),{1,1,1,1,1,1,0,0,0})
Dynamic array formulas.

 

[kevin9999]

Another way...

Book1
	A	B	C	D	E	F
1	60-79.99%
2	TM Name	Dept	Shift	DOH	USERID	Perf%
3	data	data	data	data	data	79.70%
4	data	data	data	data	data	65.90%
5	data	data	data	data	data	65.90%
6	data	data	data	data	data	65.90%
7	data	data	data	data	data	65.90%
8	data	data	data	data	data	65.90%
9	data	data	data	data	data	65.90%
10	data	data	data	data	data	62.50%
11	data	data	data	data	data	62.50%
12	data	data	data	data	data	62.50%
13	data	data	data	data	data	62.50%
Sheet2

Cell Formulas
Range	Formula
A3:F13	A3	=FILTER(Table1[[#All],[TM Name]:[Perf%]],(Table1[[#All],[Perf%]]>=0.6)*(Table1[[#All],[Perf%]]<=0.799),"")
Dynamic array formulas.

 

[FrenchCelt]

Thanks kevin9999, this was exactly what I was looking for. My results came out perfectly!

Also thanks to noveske for taking time to offer a solution. I did try out your macro based solution and it worked well enough, but I wanted to learn a formula based method.

 

[kevin9999]

Thanks kevin9999, this was exactly what I was looking for. My results came out perfectly!

Also thanks to noveske for taking time to offer a solution. I did try out your macro based solution and it worked well enough, but I wanted to learn a formula based method.

Happy to help, and thanks for the feedback