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

VickyW

New Member
Joined
Mar 1, 2020
Messages
9
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
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

vba.png


Run time 1004.png


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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try changing [B3] to Sheet1.Range("B3")
 

VickyW

New Member
Joined
Mar 1, 2020
Messages
9
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
Hi Mark
I have tried the above change but still getting runtime error1004 - AutoFilter method of Range class failed
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're criteria isn't in B3 on the Employees sheet in your dummy data sheet.
 

VickyW

New Member
Joined
Mar 1, 2020
Messages
9
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
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,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,127,107
Messages
5,622,776
Members
415,927
Latest member
vedasinternational

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top