Possible to link filtered data?

[tewlbocks]

Hello all. I've looked around here a lot, but I can't seem to find a solution that resolves all the issues I have. In an Excel 2010, what I'm trying to do is filter data from a main worksheet based on a single criterion to another worksheet, but also have that data linked. In other words, a change in a cell in either worksheet would be reflected in the other.

Worksheet 1
<TABLE style="WIDTH: 215pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=287><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=64>Client</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>Salesman</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=68>Date</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=91>Sales</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>FGI</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fabf8f; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl72>CRT</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #00b0f0; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>2/7/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: yellow; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>$231.43 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=20>CMC</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fabf8f; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl72>JIN</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #00b050; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 align=right>2/7/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: red; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71 align=right>$460.14 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl74 height=20>HF</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl74>CRT</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl73 align=right>2/9/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl75 align=right>$742.56 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffc000; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70 height=20>BWC</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fabf8f; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl72>JIN</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #00b0f0; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>2/10/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 align=right>$873.82 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl76>Total:</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl77 align=right>$2,307.95 </TD></TR></TBODY></TABLE>

This is an oversimplified example of what we're working with. Currently, in the main worksheet, we have filtering turned on and managers can filter "on the fly" using the drop down boxes in the column headers. This has become a nuisance for them (don't ask me why) and what they would like is separate worksheets for each salesman. Cell fill colors are managed by conditional formatting rules and this should be maintained in the other worksheets, so that the other worksheets would appear as follows.

Worksheet 2
<TABLE style="WIDTH: 215pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=287><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=64>Client</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>Salesman</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=68>Date</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=91>Sales</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20>FGI</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fabf8f; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67>CRT</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #00b0f0; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 align=right>2/7/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: yellow; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 align=right>$231.43 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>HF</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69>CRT</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>2/9/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #bfbfbf; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70 align=right>$742.56 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71>Total:</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl72 align=right>$973.99 </TD></TR></TBODY></TABLE>

Worksheet3
<TABLE style="WIDTH: 215pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=287><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3328" width=91><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=64>Client</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>Salesman</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 51pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=68>Date</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 68pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=91>Sales</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: yellow; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl66 height=20>CMC</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fabf8f; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71>JIN</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #00b050; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl68 align=right>2/7/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: red; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl70 align=right>$460.14 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #ffc000; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl69 height=20>BWC</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #fabf8f; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl71>JIN</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: #00b0f0; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl67 align=right>2/10/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 align=right>$873.82 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8"></TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl72>Total:</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl73 align=right>$1,333.96 </TD></TR></TBODY></TABLE>

So far I've tried various combinations of advanced filtering, worksheet grouping, and copy linking, but nothing seems to produce the desired results. I have some coding experience, but I don't have much exposure to VBA and, I figure, if this problem can be solved, it'll most likely require VBA scripting. Anyway, thank you all in advance for any help you can give.

Regards,
Tewl

 

[jim may]

See:

http://www.contextures.com/excelfiles.html

then scroll down to:

FL0013 - Create New Sheets from Filtered List

 

[tewlbocks]

Hi, Jim. Thanks for the response. I actually found the contextures site when I began researching this and that's where I learned about advanced filtering. The problem is that the filtered data isn't linked to the original data. In other words, a change in the cells of the filtered worksheets doesn't reflect in the main worksheet, Sheet1. This is my real dilemma.

Regards,
Tewl

 

[tewlbocks]

I think I'm really close on this. I recorded a macro of the steps that need to take place, but am unable to get it to work properly when I plug it into the VB editor. All sheets start out grouped and all but the main sheet are protected. Whenever something on one of the sheets changes, the sheets need to be ungrouped, unprotected, filter reapplied, protected, and regrouped. This is what the recorded macro looked like. I'm hoping there's some more efficient way of doing it with VBA.

Sheets("CRT").Select
ActiveSheet.Unprotect
ActiveSheet.Range("$A$1:$T$69").AutoFilter Field:=5, Criteria1:="CRT"
Sheets("CRT").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
True, AllowFormattingCells:=True, AllowDeletingRows:=True, AllowSorting:= _
True, AllowFiltering:=True
Sheets(Array("ALL SALES", "CRT")).Select
Sheets("CRT").Activate