Newbie in forum. Automate filters to another sheet.

hrbs

New member
Joined
Nov 26, 2012
Messages
1
Reaction score
0
Points
0
Hi,

I am new to this thread and would like to ask a question.
Attached is a sample sheet. The original sheet has about 33k of lines and 24 columns and is updated about 3 times a week, sometimes everyday.
I would like to know if there is a way based on column B and D to list only the active sales agents to the "Active" sheet and to have it automatically update when the Employee list is updated.

I know that this could be done manually but I would just like to know if there is a way to automate.
 

Attachments

  • Sample Data.xlsx
    77.5 KB · Views: 18

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
A formula solution would be to add first a helper column to the Employee List sheet...

So in G2:

=IF(AND(B2="sales agent",D2="Active"),COUNT(G$1:G1)+1,"")

copied down.

Then in the Active sheet, use a cell to get the count of matches from the Employee List sheet, so for example in Active sheet, C1: =MAX('Employee List'!$G:$G)

then in A2:

=IF(ROWS($A$1:$A1)>$C$1,"",(INDEX('Employee List'!A:A,MATCH(ROWS($A$2:$A2),'Employee List'!$G:$G))))

copied down as far as you need.

Adjust all references and ranges to suit.

Note:

If you turn the Employee List into a Table, then the helper formula should automatically get copied down as you enter new data below.
 

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
Personally, I'd probably lean towards the PivotTable, as in the attached. Like NBVC suggests though, the data should really be in an Excel table, as this is a way better data source for this kind of activity than a list or non-structured table (as you currently have it.)

One thing that gives me a bit of pause though, is tracking updates. I suppose you could copy the existing table, to a history sheet, then pull in the new data table and add a VLOOKUP to check what's changed, then report on that in the PivotTable.
 

Attachments

  • Sample Data.xlsx
    81.9 KB · Views: 12
Top