MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hyperlink and Auto filter combine


Posted by Phil on June 12, 2001 7:45 AM

Does any1 know how to do a hyperlink that will take the user to a specified sheet and autofilter it depending on two variables (a set date & Product Code).

I have two work sheets

- the first has the stock levels for all our products. And how much is due to sold on each day.

- the second is customers order details inc. how much product they want & on what day they want it.

below is an example of worksheet 1 - the mon, tue, wed... is made from the second worksheet that details customers order.


e.g
......A.................B...........C.......D.......E.........F
1 PRODUCT STOCK MON TUE WED THURS
2
3 ABC...............150.....50.......20......30.......50
4 XYZ...............170.....60.......30......10.......70


I need a hyperlink on C3, D3, E3, C4, D4, E4 that when clicked will AutoFiter WorkSheet 2 & tell the user what customers has ordered that stock.

so if the user clicked on C3 the Hyperlink would Filter - ABC, Mon = 50

result would be along these lines...

DAY STOCK PRODUCT CUSTOMER NAME
MON 10...............ABC.....CARLS BIKE SHOP
MON 5.................ABC.....TOMS BIKE SHOP
MON 10...............ABC.....BOBS BIKE SHOP
MON 5.................ABC.....HARRYS BIKE SHOP
MON 20...............ABC.....PETES BIKE SHOP


....i HOPE THAT MADE SENSE.

Any help is welcomed (and greatly needed)

Cheers

Phil


Posted by Phil on June 12, 2001 7:50 AM

Sorry - ignore the 9 entries below... I was trying to get the collumns in a line - & i didn't realize it was posting them