Hi There ,
I have a work book sample attached and hope someone can provide some advice, I have been looking through many dynamic array's, dependent drop down boxes and also a few dynamic drop down boxes linked to pivot tables. The reason I went onto pivot tables is because it removes the duplicates and sorts the data easily.
I created a table of Site names, regions and districts. Because there are various managers linked to several districts when the table is complete it created duplicated in a specific columns (as can be seen in column B and C) - therefore a created a pivot table
On the Data_Validation_Sheet - I created a a pivot table with both the District and Site name in the Rows Filed area of the pivot table - this splits the information nicely and removes duplicated. I came across a formula to use (which i will not even mention here) because the formula works well if there was only one column rage to the pivot table.
As can be seen in column B of the Data_Validation_Sheet the site names are sorted nicely grouped with the district it falls in.
The problem is, when i point the data validation, lets say to range B5:B12 and i add site names, the data validation is not updated as the pivot table is refreshed
is there a different way to approach this or is there a specific dynamic formula I can apply to expand and even decrease as i make changes to the Table and refresh the pivot tables
Bookmarks