I have a large worksheet called "Products" which lists all our building materials which are organised with 3 categories and then the product description i.e.
Cat1 Cat2 Cat3 Product Name
Accessories, Decking, Wood, Wood Plank 001
Accessories, Decking, Wood, Wood Plank 002
Accessories, Decking, Plastic, Plastic Plank 001
Accessories, Decking, Plastic, Plastic Plank 002
I have defined the categories on another sheet to list all the available options and then made a 3rd worksheet to enable staff to choose materials based on drop boxes.
I have done the first 3 list boxes as cascading list boxes which filter the categories OK. This all works fine. I then use data validation on the fourth cell to choose the product description using the following formula
=OFFSET(Products!$D$2,MATCH(1,(Products!$A:$A=B8)*(Products!$B:$B=C8)*(Products!$C:$C=D8),0)-2,0,COUNTIFS(Products!$A$2:$A$417,B8,Products!$B$2:$B$417,C8,Products!$C$2:$C$417,D8))
The worksheet all operates perfectly. However when I Save and Reopen the last part (fourth cell with the product description) does not work anymore. BUT if I simply click data validation and then OK (Without changing the formula or anything) it works again fine.
I have tried saving as an xls file AND the newer xlxs file AND the Xlm file with all macros enable. Nothign changes.
Every time i must open the data validation and click OK again and it will work until I close the worksheet again.
I am Stumped! Any one can help?
Thanks
SF
Bookmarks