Creating a Vertical Header list

[Venom]

Hello again,

This is probably simple (I hope). But I'd like to create a list of Locations from a dynamic list on a separate sheet.

Sheet2!H2:H10000 has a list of Locations, with a LOT of repeats.
Sheet1!A2:A100 is where I'd like to list the locations, without repeats.

Sheet1!A2:A100 is also used for a summary (in column B) and as a Data Validation point (for a list in G1)

The table on Sheet2 can vary from 0 items to 10,000 items and the number of different possible Locations is about to expand as it's resulted from a query to an Access database with filters.

Any help would be appreciated. =)

 

[Venom]

Refresh on Open is already available... But the "Refresh every" is unavailable. =(

 

[PCL]

The macro sent gives you the possibility to refresch the PT every time you access to the sheet and not only when openning the file.

 

[Venom]

In this instance, they are one in the same... Sheet1 is always active if the workbook is open. And if I'm going to have to click something, I can just R-click on the Pivot Table and click "Refresh Data".

I'm trying to limit user interaction down to the drop menu that allows them to select a single location to view (G1), but also allow them to leave the sheet open and use it at-a-glance.

 

[PCL]

Parhaps you could use the Change event macro and change the macro's name to

Private Sub Worksheet_Change(ByVal Target As Range)

For each change in the sheet the PT will be refreshed

 

[Venom]

Ok, I'm trying that script, thank you for being patient with me. =)