Covid Test Results Tool - How to filter results table by the selected employee

[VickyW]

Hi All,

I am hoping someone can help figure out a solution for this - I have a workbook that will be used for approx 9000 staff for recording weekly covid test outcomes

When the [Employees] table in sheet1 is filtered down to a single row the remaining [Employee ID] will appear in cell C9

Is there any way to use the value in cell C9 to automatically filter the 2 results tables on inactive sheets - so that the user can switch to them and immediately see all the results for the selected employee on sheet1

I have tried to filter the 2 results tables with VBA, but the code only works when I'm actually on those sheets.
If I'm still on sheet1 (the employees tables) as soon as a single employee is selected it produces a runtime error 1004 - AutoFilter method of range class failed

Perhaps this way cannot be fixed. Perhaps I am approaching the problem wrong and there is a better way?

Cross post from another forum here that contains an example workbook:

Any and all ideas welcome
Many thanks in advance
Vicky

 

[MARK858]

Try changing [B3] to Sheet1.Range("B3")

 

[VickyW]

Hi Mark
I have tried the above change but still getting runtime error1004 - AutoFilter method of Range class failed

 

[MARK858]

You're criteria isn't in B3 on the Employees sheet in your dummy data sheet.

 

[VickyW]

You're criteria isn't in B3 on the Employees sheet in your dummy data sheet.

The employee ID is in C9 on sheet1
And it's in B3 and in sheet3 & sheet4

There's a formula in all 3 of those cells that says 'when employees table on sheet1 is filtered down to a single row, display the employee ID'
And they're all covered by a textbox that's linked which displays the value

This is the formula on sheet3 & sheet4 in cell B3
=IF(Employees!$L$1=1,Employees!$C$9,"")