Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I filter data from a master worksheet to other worksheets
I am pretty new to this so excuse my ignorance.
I finally got Auofilter to work so i am pretty happy. We have a master sheet which has all the tasks all employees are working on. We use an x to dictate a column where someone has to do it. The master work sheet may look like this Date Task EmpName1 EmpName2 EmpName3 1-nov Call client x 1-nov Attend meeting x x 2-nov Photocopy Doc x I created a workheet for each employee by referencing everything in the master sheet (i.e for a1 in EmpName1 i just put =master!a1) - and then applying an autofilter with "x" as the criteria for each employee worksheet. so the EmpName1 worksheet would look like this Date Task EmpName1 EmpName2 EmpName3 1-nov Call client x 1-nov Attend meeting x x This all works really well except when new tasks are added to the Master worksheet - for the task to show up on each employees worksheet they need to go and "re-filter" using the x selection to bring up the new tasks. Ideally I would like this to be automated. is this possible? Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I filter data from a master worksheet to other worksheets
Hi Dave
One way would be to use Advanced Filter rather than Autofilter. For more help on Advanced Filter, take a look at Debra Dalgleish's site http://www.contextures.com/xladvfilter01.html and for Dynamic Ranges http://www.contextures.com/xlNames01.html#Dynamic Define a Dynamic range to reference your data on Master sheet InsertNameDefineName Mydata Refers to =OFFSET($A$1,0,0,COUNT($A:$A),COUNTA($1:$1)) This assumes that your header row on Master starts in A1 On each employee sheet, in cell A1 enter Employee1 (the name as it exists on your master sheet) In cell A2 enter ="=x" Delete everything else on the sheet. Repeat the procedure for all other employee sheets, changing just the name in A1, but use the same formula in A2. Copy the following short piece of code into a module in your workbook (instructions on how to do this at end) Sub UpdateEmployee() Dim colno As Long, dest As Range If ActiveSheet.Name = "Master" Then MsgBox "Please go to Employee Sheet before running this macro" Exit Sub End If colno = Workbooks.Application.CountA(Sheets("master").Rang e("1:1")) Set dest = Range(Cells(4, 1), Cells(4, colno)) Sheets("Master").Range("Mydata").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Range("A1:A2"), CopyToRange:=dest, Unique:=False End Sub Then, on each employees sheet, press Alt+F8 which will bring up the Macro dialogue box and choose Run with Update Employee highlighted. This will update the employee sheet for any changes that have taken place in the Master sheet You can copy the code and paste it into your Visual Basic Editor (VBE) in a Standard Module located in your file. To do this, Alt + F11 (open VBE) Ctrl + R (open Project Explorer) Select the file name on the left Insert Module Paste code in Module David McRitchie has lots of useful help about installing macros on his site at http://www.mvps.org/dmcritchie/excel/install.htm http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Regards Roger Govier "Dave from Perth" <Dave from wrote in message ... I am pretty new to this so excuse my ignorance. I finally got Auofilter to work so i am pretty happy. We have a master sheet which has all the tasks all employees are working on. We use an x to dictate a column where someone has to do it. The master work sheet may look like this Date Task EmpName1 EmpName2 EmpName3 1-nov Call client x 1-nov Attend meeting x x 2-nov Photocopy Doc x I created a workheet for each employee by referencing everything in the master sheet (i.e for a1 in EmpName1 i just put =master!a1) - and then applying an autofilter with "x" as the criteria for each employee worksheet. so the EmpName1 worksheet would look like this Date Task EmpName1 EmpName2 EmpName3 1-nov Call client x 1-nov Attend meeting x x This all works really well except when new tasks are added to the Master worksheet - for the task to show up on each employees worksheet they need to go and "re-filter" using the x selection to bring up the new tasks. Ideally I would like this to be automated. is this possible? Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Consolidate Several Workbooks into One | Excel Worksheet Functions | |||
advanced: synchronizing data value across two worksheet drop boxes | Excel Worksheet Functions | |||
Merge Worksheets | Excel Worksheet Functions | |||
access my data from my master worksheet while calculation is don. | Excel Worksheet Functions |