Filter Data?

[Rob_010101]

Hello,

I track the whole company's absence on a single excel tracker (excerpt below). At the end of each week, I am required to provide an individual absence report to managers in each location. There are currently 62 in the business across 8 locations and this is hours of work on a Friday.

We use Microsoft Teams, so I was thinking of creating a Teams group for each of the 8 locations and dropping a separate spreadsheet in each group which automatically updates from my main tracker. Absence data is confidential, so location 1 managers can't see who is absent in location 2 for example.

Below is my main tracker (anonymised).

Test Data.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Employee ID (8 Digit)	Employee ID	Status	Forename	Surname	Role	Location	Region	Date of Birth	Age Decimal	Age Y/M	Hire Date	LOS Decimal	LOS Y/M	Last Absence Start	Total Occasions in Current 6 Months
2	00000001	1	Active	Bob	Smith	Manager	Wheathampstead	1	31/12/1993	29.47945205	29 Years, 5 Months	01/01/2022	0.780821918	0 Years, 9 Months	19/12/2022	1
3	00000002	2	Active	Alan	Carr	Shoesmith	Harpenden	2	12/01/1963	60.46849315	60 Years, 5 Months	01/01/2022	23.71780822	23 Years, 8 Months	20/12/2022	1
4	00000003	3	Active	Jacob	Mogg	Manager	Amwell	3	18/11/2000	22.59178082	22 Years, 6 Months	01/01/2022	0.953424658	0 Years, 11 Months	20/12/2022	1
5	00000004	4	Active	Boris	Yeltzen	Shoesmith	Wheathampstead	1	21/11/1988	34.59178082	34 Years, 6 Months	01/01/2022	5.978082192	5 Years, 11 Months	20/12/2022	1
6	00000005	5	Active	Bob	Mortimer	Manager	Gustard Wood	4	19/12/1959	63.5369863	63 Years, 5 Months	01/01/2022	32.13424658	32 Years, 1 Months	10/01/2023	2
7	00000006	6	Active	Daniel	Smith	Manager	Harpenden	2	01/02/1992	31.39452055	31 Years, 4 Months	01/01/2022	0.624657534	0 Years, 7 Months	19/12/2022	1
8	00000007	7	Active	Trevor	Melikklegundrilla	Shoesmith	Amwell	3	26/02/1985	38.32876712	38 Years, 3 Months	01/01/2022	21.30958904	21 Years, 3 Months	20/12/2022	1
9	00000008	8	Active	Sean	Lock	Shoesmith	Wheathampstead	1	11/01/1986	37.45479452	37 Years, 5 Months	01/01/2022	1.567123288	1 Years, 6 Months	22/12/2022	1
10	00000009	9	Active	John	Richardson	Shoesmith	Wheathampstead	1	14/05/1977	46.12328767	46 Years, 1 Months	01/01/2022	3.293150685	3 Years, 3 Months	22/12/2022	1
11	00000010	10	Active	Roisin	Connerty	Shoesmith	Amwell	3	30/07/1998	24.89863014	24 Years, 10 Months	01/01/2022	0.734246575	0 Years, 8 Months	22/12/2022	1
12	00000011	11	Active	Kevin	Kevinson	Shoesmith	Amwell	3	12/05/1978	45.12876712	45 Years, 1 Months	01/01/2022	3.024657534	3 Years, 0 Months	22/12/2022	1
13	00000012	12	Active	Peter	Andre	Shoesmith	Wheathampstead	1	06/09/1992	30.79726027	30 Years, 9 Months	01/01/2022	1.279452055	1 Years, 3 Months	30/03/2023	3
14	00000013	13	Active	Ian	McKellen	Shoesmith	Amwell	3	22/12/1976	46.51506849	46 Years, 5 Months	01/01/2022	1.41369863	1 Years, 5 Months	19/12/2022	1
15	00000014	14	Active	Paul	Gascoine	Shoesmith	Wheathampstead	1	24/09/1985	37.75342466	37 Years, 8 Months	01/01/2022	1.375342466	1 Years, 4 Months	15/03/2023	2
16	00000015	15	Active	Russel	Brand	Shoesmith	Harpenden	2	02/07/1986	36.98356164	36 Years, 11 Months	01/01/2022	15.96712329	15 Years, 11 Months	22/12/2022	1
17	00000016	16	Active	Sarah	Millican	Shoesmith	Wheathampstead	1	14/02/1989	34.35890411	34 Years, 4 Months	01/01/2022	1.356164384	1 Years, 4 Months	03/01/2023	2
18	00000017	17	Active	Kirsty	Thirsty	Shoesmith	Amwell	3	28/12/1990	32.49041096	32 Years, 5 Months	01/01/2022	1.509589041	1 Years, 6 Months	16/03/2023	2
19	00000018	18	Active	Stuart	Edwards	Shoesmith	Wheathampstead	1	24/06/1990	33.00273973	32 Years, 11 Months	01/01/2022	1.449315068	1 Years, 5 Months	03/04/2023	2
20	00000019	19	Active	Andrew	Marr	Shoesmith	Amwell	3	18/12/1960	62.5369863	62 Years, 5 Months	01/01/2022	17.32328767	17 Years, 3 Months	11/04/2023	2
21	00000020	20	Active	Patrick	Swazey	Shoesmith	Harpenden	2	21/03/1973	50.2739726	50 Years, 2 Months	01/01/2022	31.29041096	31 Years, 3 Months	28/04/2023	2
22	00000021	21	Active	Jeremy	Paxman	Shoesmith	Wheathampstead	1	15/08/1975	47.87123288	47 Years, 10 Months	01/01/2022	2.753424658	2 Years, 9 Months	25/05/2023	3
23	00000022	22	Active	Rod	Liddle	Shoesmith	Amwell	3	28/11/1961	61.59178082	61 Years, 6 Months	01/01/2022	2.846575342	2 Years, 10 Months	19/12/2022	1
24	00000023	23	Active	Rob	Brydon	Shoesmith	Wheathampstead	1	27/03/1960	63.26575342	63 Years, 2 Months	01/01/2022	2.564383562	2 Years, 6 Months	23/12/2022	1
25	00000024	24	Active	Joe	Wilkinson	Shoesmith	Amwell	3	07/02/1963	60.39726027	60 Years, 4 Months	01/01/2022	5.364383562	5 Years, 4 Months	22/03/2023	2
26	00000025	25	Active	Rob	Beckett	Shoesmith	Wheathampstead	1	18/04/1962	61.20547945	61 Years, 1 Months	01/01/2022	12.40273973	12 Years, 4 Months	22/12/2022	1
27	00000026	26	Active	Teresa	Green	Shoesmith	Harpenden	2	23/10/1955	67.69589041	67 Years, 7 Months	01/01/2022	4.731506849	4 Years, 8 Months	30/05/2023	3
Sheet1

Cell Formulas
Range	Formula
A2:A27	A2	=REPT(0,8-LEN(B2))&B2

Say I wanted extract all Wheathampstead rows from the above onto a separate spreadsheet which automatically updates when the above is updated, how could this be done?

Kind Regards,
Chris

 

[alansidman]

Simple case of filtering on the Location Column and then copy and paste to a new workbook that can be sent on to Manager. Rinse and Repeat.

 

[Rob_010101]

Simple case of filtering on the Location Column and then copy and paste to a new workbook that can be sent on to Manager. Rinse and Repeat.

I'm aware it needs to be the filter formula but asking how this is done as I've never used it.

Thanks

 

[kweaver]

Click on the G1 cell, click on Data on the Ribbon, then Filter (or use the proposed shortcut you see)

Then, click on the square w/the triangle and deselect (Select All) then select your region.

 

[kweaver]

Alternatively, on a new sheet with the headers in place, cell A2 use a formula for FILTER

021623 Misc.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Employee ID (8 Digit)	Employee ID	Status	Forename	Surname	Role	Location	Region	Date of Birth	Age Decimal	Age Y/M	Hire Date	LOS Decimal	LOS Y/M	Last Absence Start	Total Occasions in Current 6 Months
2	3	3	Active	Jacob	Mogg	Manager	Amwell	3	18/11/2000	22.59178	22 Years, 6 Months	44562	0.953425	0 Years, 11 Months	20/12/2022	1
3	7	7	Active	Trevor	Melikklegundrilla	Shoesmith	Amwell	3	26/02/1985	38.32877	38 Years, 3 Months	44562	21.30959	21 Years, 3 Months	20/12/2022	1
4	10	10	Active	Roisin	Connerty	Shoesmith	Amwell	3	30/07/1998	24.89863	24 Years, 10 Months	44562	0.734247	0 Years, 8 Months	22/12/2022	1
5	11	11	Active	Kevin	Kevinson	Shoesmith	Amwell	3	28829	45.12877	45 Years, 1 Months	44562	3.024658	3 Years, 0 Months	22/12/2022	1
6	13	13	Active	Ian	McKellen	Shoesmith	Amwell	3	22/12/1976	46.51507	46 Years, 5 Months	44562	1.413699	1 Years, 5 Months	19/12/2022	1
7	17	17	Active	Kirsty	Thirsty	Shoesmith	Amwell	3	28/12/1990	32.49041	32 Years, 5 Months	44562	1.509589	1 Years, 6 Months	16/03/2023	2
8	19	19	Active	Andrew	Marr	Shoesmith	Amwell	3	18/12/1960	62.53699	62 Years, 5 Months	44562	17.32329	17 Years, 3 Months	45234	2
9	22	22	Active	Rod	Liddle	Shoesmith	Amwell	3	28/11/1961	61.59178	61 Years, 6 Months	44562	2.846575	2 Years, 10 Months	19/12/2022	1
10	24	24	Active	Joe	Wilkinson	Shoesmith	Amwell	3	23194	60.39726	60 Years, 4 Months	44562	5.364384	5 Years, 4 Months	22/03/2023	2
Report

Cell Formulas
Range	Formula
A2:P10	A2	=FILTER(Absent!A2:P27,Absent!G2:G27="Amwell")
Dynamic array formulas.

 

[Rob_010101]

Thanks All,

After a think I've managed to sort it, using the filter formula similarly to @kweaver 's second post above.

Kind Regards

 

[Fluff]

The problem with using the filter function, is that a manager can simply change the criteria & see data for another site.

 

[Rob_010101]

The problem with using the filter function, is that a manager can simply change the criteria & see data for another site.

Correct. To prevent this, I'm protecting each worksheet with a password:

and using the following VBA Code to prevent Save As:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
    MsgBox "Save As has been disabled for security reasons. Please speak to Chris in HR if you would like a copy", vbInformation
    Cancel = True
End If
End Sub

Even though I've tested Save As and a workbook is saved with the same workbook protections, i'm adding this for an extra layer even though they can just delete the code.

Any holes in this?

Kind Regards,
Chris

 

[Fluff]

It's very easy to bypass sheet protection.

 

[Rob_010101]

Hasn't that been stopped in recent versions of office? I'm sure I read somewhere they use SHA-256 now?