Hi,

I am building a Project Resource Dashboard application in Excel 2003 and I need some help detecting, and acting upon, filtered rows.

On the main sheet, I have 2 separate blocks of data. The first block (let's call this Range 1) contains several rows for each unique project / resource assignment. For example :

Row 1 shows John assigned to Project A
Row 2 shows John assigned to Project B
Row 3 shows John assigned to Project C
Row 4 shows Paul assigned to Project B
Row 5 shows Paul assigned to Project D
Row 6 shows Mary assigned to Project A
Row 7 shows Mary assigned to Project D
etc

The second block (let's call this Range 2) sits below the main block and this shows summary totals for each person (just one row per person).
eg.
Row 1 will show totals for John
Row 2 will show totals for Mary
Row 3 will show totals for Paul

When a standard filter is applied to Range 1 (say to select 'John' in the Name column), only the 3 rows for John will be displayed (and all other rows in Range 1 will be hidden). This is basic stuff and is fine.

The requirement, however, is for the 2 ranges to be filtered simultaneously so that when a name is selected in Range 1, both ranges will be filtered using the Name. Continuing with the examle, 3 rows would be displayed in Range 1 (John's projects) and just one row would be displayed in Range 2 (John's totals).

I know this can't be done using the auto filter in Excel. I played around to try to get it working using the Advanced Filter but no joy there either so I reckon I need to use VBA.

Looking at some of the threads, it seems that the Worksheet Calculate event could perhaps be used to trigger a macro to hide all rows in Range 2 that don't match the filter selection in Range 1.

Can anyone give me some guidance here please. Specifically, I need something that will tell me when the Name column in Range 1 has been filtered (but I don't think this is possible without perhaps checking that Range 1 has been filtered and that all visible names are the same ????)

Hopefully there is reasonably simple solution to this problem (I don't have much experience of using VBA)

Any help would be greatly appreciated.

Thanks in advance,


gtol