Pivot Table and Data Validation

Hi All,

good evening - monday tomorrow! yeyyyy! i'll get some peace and quiet after a hectic w'end with wife ordering me around and children requiring lots of attention!!

Please find attached a sample pivot table.

I have four field headings in Report Filter, Region (B1), Site Name (B2), Month and Number of Nights. At the moment when I select a region and then a site name - I can still see ALL site names, I would like that when I apply a filter on Region that i can only see the sites associated with that region when i then click on B2. Much like a data validation list.

My actual data set is some 400,000 rows..........

Ideas on a postcard!! thanks guys!

Hi Blake 7,

I believe you can do what you want by making the pivot table based on the data from an advanced filter. Make the advanced filter based on the change of B1 cell in the pivot Table. You will need to write some code to update the advanced filter and then the Pivot Table. Find the attached where part of the scheme is used.

If you can't get the whole thing to work, keep asking. My question is why?

Marvellous Marvin.

Thanks for your input. I will crack on with your suggested route and post back when I crack it or it cracks me! Time for DD's PT Recipe Book!

cheers