So I have 3 sheets in my spreadsheet. One sheet contains the drop down values, one is the processed data that I want to convert into a pivot table and the other is a spreadsheet of my raw data.

Excel Sheet 1.png

However, the processed data changes depending on which option I choose on my drop down list from the image above. Therefore, this affects the data that will be shown on the pivot table. The dropdown values is located on a seperate specialized sheet for modifying and filtering settings. I want to create a pivot table that automatically selects a dropdown value and displays the data based on the changed values.

Excel Sheet 2.png

What I have tried was adding the table to a data model and then putting the "city selection" under filters. However as you can see above, it doesn't work as intended, the filter needs to show the other selections in the dropdown.

Is there any way to achieve what I'm trying to do?