Linking tables on different tabs to filter assignments by individual

[Surferboy1500]

Hi,

I'm trying to come up with either a table setting, formula, macro or pivot that will easily show pull the information of each responsible party on a separate tab pulling from a master listing is below and updating when chagnes are made to the master listing, basically want a tab for Jason, John, and Sara where it shows all of the tasks assigned to just them. So for John, it would pull all data within the rows related to "clean desk", "clear cabinet", Work on Project X", and "Work on Project Y" into another tab (one I could take out and send to that individual or see how their work stacks up), but if he got removed from review Project X (and Sara reviewed it instead and that was updated on the master listing tab), it would remove that from his tab. I feel there's a way to link the information and keep it live or have the ability to "refresh" those tabs but I haven't been successful at getting anything to work. I would like to not have external sources that are linked to locations on my harddrive (like using microsoft queries on external data), as this workbook would be sent via email and I believe that causes the links to break.

Test File.xlsx

Assignment	Prepared by	Reviewed by	Date Started	Date Complete
Clean Desk	John	Sara	1/13/2014	1/16/2014
Clear Cabinets	John	Sara	1/17/2014	1/20/2014
Work on project X	Jason	John	1/21/2014	1/24/2014
Work on project Y	Jason	John	1/25/2014	1/28/2014
Sleep	Sara	Jason	1/29/2014	2/1/2014

[natefarm]

Create a shortcut key, or a button on the Master Sheet, and assign it to the following subroutine.

Please Login or Register  to view this content.

[Surferboy1500]

Could you add it to the excel and reattach or is that not allowed? I learn better seeing it work inside an excel, I have very limited knowledge on code.

[natefarm]

"Teach a man to fish"

With your workbook open (and none others, just to avoid confusion), press Alt+F11 to open the VBA window. Click the Project Explorer button, or select View - Project Explorer. You'll see your workbook in a tree-view structure. Select Insert - Module to create a blank code module. Maximize it if needed, and copy and paste all the code above into it. With your cursor somewhere in the code, press F5 to run it. Then look at your workbook to see the results.

You can run the code any time from the VBA window with F5. To create a button on your worksheet, do the following: In your workbook, select the Master Sheet. If you don't have a Developer tab showing, select the File tab, click Options - Customize Ribbon, and check Developer on the right side, then click Ok. Click the Developer tab and click Design Mode (it's a toggle button). Then click Insert, and click the Button control (upper left icon in the Form section). On your worksheet, click your mouse, and while holding it down, drag it a little bit down and to the right (you can resize it later if you want). When you release the mouse, an Assign Macro window will pop up. Select the RefreshTables subroutine and click OK. Right-click the button, select Edit Text, and change it to "Refresh Tables", or whatever you want, and click outside the button to save it. Right-click to move or resize as needed, then unselect Design Mode, and it's ready to use.

Have fun! If you still need help, let me know.