Filter With Data Valadation

[Tooflas]

Hi All,

Losing my marbles with this now...... I have been trying to work out a way to filter on Data Validation. I:E On one sheet I have all my data and the separated by 5 different categories.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cat[/TD]
[TD]sub Cat[/TD]
[TD]Price[/TD]
[TD]Pack Size[/TD]
[TD]Colour[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]12[/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]7[/TD]
[TD]45[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]4[/TD]
[TD]7[/TD]
[TD]34[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]34[/TD]
[TD]red[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]45[/TD]
[TD]green[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]72[/TD]
[TD]yellow[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]33[/TD]
[TD]blue[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]34[/TD]
[TD]red[/TD]
[/TR]
</tbody>[/TABLE]


Then on the next sheet I will have the categories you see at the top with the data list drop down next to them. So that if I select a colour from the drop down I:E red it will only give me the red products in all the other sections to choose from. Therefore I can filter further. Now I know I can just filter on the 1st sheet. However need to do it this way for other reasons.

Hope I make sense and I hope some can help.

 

[Tooflas]

Im still struggling with this and someone suggested an offset and match with i have googled but to no avail.