Highlighting Filtered Cell Data in different work sheet

[pratheesh1983]

Hello,

I'm preparing a sheet for a construction site, the first sheet contains all the construction materials details in Sheet 1 Column A. Sheet 2 contains the list of dealers/shops who sells the products mentioned in the Sheet 1, each coulmn here contains Dealers/ shop name and materials which they sell listed below to it under the same column. I need to put a conditional formatting which will highlight the materials filtered (Sheet 1, Column A) in Sheet 2, which will ideally help us to identify the dealers or shops who have products. For Example: If I Filter in Column A Sheet 1 under Material: Fired Bricks and Clay Blocks and Concrete, then the same material (which are filtered) should get highlighted in color in Sheet 2 under each dealers or shops were the product is applicable. I have put the sample data below for better understanding. Would appreciate in any one can assist me doing this.

Thanks in advance!




Sheet 1: Column A

[TABLE="width: 205"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Materials[/TD]
[/TR]
[TR]
[TD]Fired bricks and clay blocks[/TD]
[/TR]
[TR]
[TD]Cement composites[/TD]
[/TR]
[TR]
[TD]Concrete[/TD]
[/TR]
[TR]
[TD]Fabric[/TD]
[/TR]
[TR]
[TD]Foam[/TD]
[/TR]
[TR]
[TD]Glass[/TD]
[/TR]
[TR]
[TD]Gypcrete[/TD]
[/TR]
[TR]
[TD]Metal[/TD]
[/TR]
[TR]
[TD]Plastics[/TD]
[/TR]
[TR]
[TD]Papers and membranes[/TD]
[/TR]
[TR]
[TD]Ceramics[/TD]
[/TR]
[TR]
[TD]Brush[/TD]
[/TR]
[TR]
[TD]Ice and snow[/TD]
[/TR]
[TR]
[TD]Mud and clay[/TD]
[/TR]
[TR]
[TD]Wet-laid clay walls[/TD]
[/TR]
[TR]
[TD]Structural clay blocks and bricks[/TD]
[/TR]
[TR]
[TD]Sand[/TD]
[/TR]
[TR]
[TD]Stone or rock[/TD]
[/TR]
[TR]
[TD]Thatch[/TD]
[/TR]
[TR]
[TD]Wood and timber[/TD]
[/TR]
</tbody>[/TABLE]



Sheet 2 Dealers

[TABLE="width: 639"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 639"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]MJ Infrastructure[/TD]
[TD]Alpex Hardware[/TD]
[TD]PN Constructions[/TD]
[/TR]
[TR]
[TD]Fired bricks and clay blocks[/TD]
[TD]Cement composites[/TD]
[TD]Fabric[/TD]
[/TR]
[TR]
[TD]Cement composites[/TD]
[TD]Concrete[/TD]
[TD]Foam[/TD]
[/TR]
[TR]
[TD]Concrete[/TD]
[TD]Fabric[/TD]
[TD]Glass[/TD]
[/TR]
[TR]
[TD]Fabric[/TD]
[TD]Foam[/TD]
[TD]Gypcrete[/TD]
[/TR]
[TR]
[TD]Foam[/TD]
[TD]Glass[/TD]
[TD]Metal[/TD]
[/TR]
[TR]
[TD]Glass[/TD]
[TD]Gypcrete[/TD]
[TD]Ice and snow[/TD]
[/TR]
[TR]
[TD]Gypcrete[/TD]
[TD]Metal[/TD]
[TD]Mud and clay[/TD]
[/TR]
[TR]
[TD]Metal[/TD]
[TD]Plastics[/TD]
[TD]Wet-laid clay walls[/TD]
[/TR]
[TR]
[TD]Plastics[/TD]
[TD]Papers and membranes[/TD]
[TD]Structural clay blocks and bricks[/TD]
[/TR]
[TR]
[TD]Papers and membranes[/TD]
[TD]Ceramics[/TD]
[TD]Sand[/TD]
[/TR]
[TR]
[TD]Ceramics[/TD]
[TD]Brush[/TD]
[TD]Stone or rock[/TD]
[/TR]
[TR]
[TD]Brush[/TD]
[TD]Ice and snow[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ice and snow[/TD]
[TD]Mud and clay[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mud and clay[/TD]
[TD]Wet-laid clay walls[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wet-laid clay walls[/TD]
[TD]Structural clay blocks and bricks[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Structural clay blocks and bricks[/TD]
[TD]Sand[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sand[/TD]
[TD]Stone or rock[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Stone or rock[/TD]
[TD]Thatch[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thatch[/TD]
[TD]Wood and timber[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wood and timber[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

What do you mean by:

If I Filter in Column A Sheet 1 under Material

 

[pratheesh1983]

Filter is kept in cells which have "Material". So the drop down will give list of items. The items which I selected, same items mentioned in sheet 2 and should get highlighted.

 

[mumps]

In which cells are the drop down list?

 

[Yongle]

Here is a much easier way to achieve what you want

Sheet 2 (I named this sheet "Dealers")
- leave Column A Blank
- row 1 = Headers (Dealer names)
- other rows for what they supply

Looks Like this

<tbody>
[TD="class: xl67"]Col A[/TD]
[TD="class: xl66"] MJ Infrastructure
[/TD]
[TD="class: xl66"] Alpex Hardware [/TD]
[TD="class: xl66, width: 223"] PN Constructions [/TD]

[TD="class: xl65, width: 223"]Fired bricks and clay blocks[/TD]
[TD="class: xl65, width: 223"]Cement composites[/TD]
[TD="class: xl65, width: 223"]Fabric[/TD]

[TD="class: xl65, width: 223"]Cement composites[/TD]
[TD="class: xl65, width: 223"]Concrete[/TD]
[TD="class: xl65, width: 223"]Foam[/TD]

[TD="class: xl65, width: 223"]Concrete[/TD]
[TD="class: xl65, width: 223"]Fabric[/TD]
[TD="class: xl65, width: 223"]Glass[/TD]

[TD="class: xl65, width: 223"]Fabric[/TD]
[TD="class: xl65, width: 223"]Foam[/TD]
[TD="class: xl65, width: 223"]Gypcrete[/TD]

[TD="class: xl65, width: 223"]Foam[/TD]
[TD="class: xl65, width: 223"]Glass[/TD]
[TD="class: xl65, width: 223"]Metal[/TD]

[TD="class: xl65, width: 223"]Glass[/TD]
[TD="class: xl65, width: 223"]Gypcrete[/TD]
[TD="class: xl65, width: 223"]Ice and snow[/TD]

[TD="class: xl65, width: 223"]Gypcrete[/TD]
[TD="class: xl65, width: 223"]Metal[/TD]
[TD="class: xl65, width: 223"]Mud and clay[/TD]

</tbody>

Sheet1
- A1 = Materials
- Column A = list of materials
- Row 1 starting in B1 = Dealer Names (exactly as in sheet2)

Looks like this

[TABLE="width: 931"]
<tbody>[TR]
[TD="class: xl66, width: 256"]Materials
[/TD]
[TD="class: xl65, width: 253"]MJ Infrastructure[/TD]
[TD="class: xl65, width: 209"]Alpex Hardware[/TD]
[TD="class: xl65, width: 213"]PN Constructions[/TD]
[/TR]
</tbody>[/TABLE]

Formula in cell B2, copied down and across
=IF(COUNTIF(Dealers!B:B,$A2)>0,$A2,"")

If autofilter already enabled click Filter twice to reset it so that all columns are included
When user filters on column A the relevant suppliers are listed

Looks Like this

<tbody>
[TD="class: xl66"] Materials
[/TD]
[TD="class: xl67"] MJ Infrastructure [/TD]
[TD="class: xl67, width: 209"] Alpex Hardware [/TD]
[TD="class: xl67, width: 213"] PN Constructions [/TD]

[TD="class: xl67, width: 256"]Plastics[/TD]
[TD="class: xl68"]Plastics[/TD]
[TD="class: xl68"]Plastics[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl67, width: 256"]Ceramics[/TD]
[TD="class: xl68"]Ceramics[/TD]
[TD="class: xl68"]Ceramics[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl67, width: 256"]Brush[/TD]
[TD="class: xl68"]Brush[/TD]
[TD="class: xl68"]Brush[/TD]
[TD="class: xl68"][/TD]

[TD="class: xl67, width: 256"]Wet-laid clay walls[/TD]
[TD="class: xl68"]Wet-laid clay walls[/TD]
[TD="class: xl68"]Wet-laid clay walls[/TD]
[TD="class: xl68"]Wet-laid clay walls[/TD]

</tbody>

 

[pratheesh1983]

Thanks for this..Actually I need to put a condictional formating on Sheet 2 (dealers) so that which all items filtered in Sheet 1 same should get highlight in special colour in dealer sheet.ie sheet 2