Hide rows in range 2 based on filter selection in range 1

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

Hi Charles,

Thanks for your post.

Here is a specimen workbook that will help to explain things.

This is a project resource dashboard that shows project resource capacity (using traffic light colours) based on whether planned effort is >, < or = availability.

When a project assignment is added or changed in the top part of the dashboard, the summary totals are updated in the lower part.

The lower part can look a bit busy especially when the top part is filtered so I would like to filter the bottom part simultaneously with the top part.
For example, if I filter on the Resource name column and select say 'Billy', I would like to hide all rows in the Level1Names range other than 'Billy'.

Alternativelely, and this I'm sure would be easier to implement, it would be OK just to have the name 'Billy' highlighted in the lower list. So I reckon this would be taking whatever has been entered as filter criteria and using this to get a match in the lower table (and changing the backcolour of the cell).

I need some help to figure out how to go about it !

Thanks again,


gtol

Hi Charles,

Thanks for coming back to me.

I can understand your thinking about the dates but this is not what I was after.

Have a look at v2 of the sample spreadsheet attached where I have written in what I want to happen (Option 1 or Option 2). If Option 1 is too messy, I would be happy to go with Option 2 - whatever is easiest to implement.

I just don't know how to approach the coding for doing either of these things. Any help here would be great.

Thanks Charles,


gtol

Hi Charles,

Thanks for your help with this.

I see you have used the Worksheet Calculate event which is what I had in mind also.

I get a compile error when I open the workbook (named argument not found). It seems to have a problem with the 'SearchFormat'. If this works for you, I reckon you must be using a more recent version of Excel (I am stuck with 2003 !)

Anyway, you have given me a few ideas to play around with.

Thanks again for your help.


gtol

Hi Charles,

Many thanks for offering but I don't want you to spend any more time on this. You have helped me enough already.

I am now actually looking to see if I can figure out how to handle the requirement using Conditional Formating.

I think I might be able to apply this in the lower table based on a formula that looks in the upper table. If all visible Name values in the upper table (filtered or unfiltered) are the same, then we highlight the matching Name in the lower table.

This would not involve any code and I am now thinking that this might be be the best and simplest approach. I will let you know how I get on.

All the best,


gtol

Hi Charles,
I tested that there just now and I get the same compile error as before but don't worry - you have helped me a lot and I really appreciate it.

gtol

No problem Charles. I have done that for you.
All the best

gtol