Automatically Keep Filtered Data on New Worksheet Up-to-Date

[Zyniker]

I currently have a Workbook with five Worksheets, organized thus:

• Worksheet1 - actual data, 5 columns (B:F).
• Worksheet2 - would like this Worksheet to display filtered data (data from Worksheet1 where a certain value is found in Column2)
• Worksheet3 - same as Worksheet2, but with a different value in Column2 by which to filter.
• Worksheet4 - same as Worksheet2, but with a different value in Column2 by which to filter.
• Worksheet5 - DATA Worksheet (the dark place where I store random data in my Workbooks)

Basically, all of the information is contained in the little list above this sentence. I simply want to mirror the data on Worksheet1 into Worksheets 2-4, but filtered based on a specific value that will be found in Column2 on Worksheet1. At present, Worksheet1 has 43 lines (though it will expand in the future) and Column2 has 3 different values. The number of times each value appears in Column2 is relatively equal, so figure 14 items will be mirrored onto each of the subsequent sheets.

Thanks in advance for any help!

 

[jbeaucaire]

Look at the answer I just gave this OP in another thread, it's basically the same thing for you I would think.

http://www.mrexcel.com/forum/showthread.php?t=372815

 

[Zyniker]

Look at the answer I just gave this OP in another thread, it's basically the same thing for you I would think.

http://www.mrexcel.com/forum/showthread.php?t=372815

I am attempting to use a modified version of your formula from the other thread; however, I am consistently getting errors (latest error: #NUM).
Here is the formula I am attempting to use:

=IF(ROW($B5)-ROW($B$5)+1>'YCC Case Assignments'!$Z$5,"",SMALL(IF('YCC Case Assignments'!$Y$5:$Y$200="1",ROW($B$5:$B$200),9999),ROW($B5)-ROW($B$5)+1))

Z5 on 'YCC Case Assignments' contains the COUNTIF for Attorney1, Z6 is Attorney2, and Z7 is Attorney3. In order to obtain something relatively akin to the "Yes" column available in the other post, I created a nested IF statement to return 1, 2, or 3 (according to which attorney's name appears in column B), this nested statement is in column Y.
The formula I quoted above is my attempt to replicate data for Attorney1 (once I get it working for one attorney, it will be trivial to get it working on the other sheets).

 

[Zyniker]

The formula currently returns "9999" for the first cell B5 and then returns an error for the remaining cells (#NUM).

 

[jbeaucaire]

First, make sure all the ranges are the same depth. Then when you're done editing it, press CTRL-SHIFT-ENTER to activate the array. If you get the right answer for the first one, then copy down the formula.

 

[Zyniker]

Thank you very much for your help, jbeaucaire! This solution is much more elegant than the hokey IF()/Auto Filter combo I had going.