Jul 27 2022 03:31 PM - edited Jul 27 2022 10:24 PM
I have data in an Excel table. I want to first use either filters or slicers to limit the data down to specific cities and zip codes. This level of filtering should be dynamic because I want to be able to change this filtering depending on the area I am analyzing.
I then want to use the FILTER function to copy the data to other sheets to organize it by the month of the sale. ie. the first sheet will contain the data for Jan, and other for Feb, etc.
The Filter function on the secondary sheets should grab the data that is already limited by the slicers or filters in step one.
Edit:
I was going to add a sample .xlsx file to demonstrate, but I don't see the functionality that allows me to add anything but images. I am adding screenshots of my xlsx file, but if someone can tell me how to add it, I will.
The first sheet contains all the data. I want to be able to use either
1. the default filters that are given when a table is created or
2. slicers
to limit the data to say only in "Kirkland" and with 3 bedrooms under 1200sf. Each of the subsequent sheets for example June 2022, can be populated using a filter function (cell B2) to bring only the data with "Listed Date" during that month.
Yes I could do this by adding an initial sheet with variables, where I specify the filter values, but I want to be able to use the default filters, or slicers because with each analysis I may filter on different fields, and may have 1 filter, many filters, or no filters. I like the flexibility and ease of using built in filters or slicers.
How do I do this, since the Filter function is grabbing All the data, and not the data that is limited by the filtering (or slicers) in the first step?
Jul 27 2022 04:39 PM
=FILTER('master sheet'!A2:C321,('master sheet'!A2:A321=F2)*('master sheet'!C2:C321=1)*('master sheet'!B2:B321>G2)*('master sheet'!B2:B321<H2))
You can try this formula in the attached file if you work with Office365 or 2021. The example shows the sheet for january. In cells F1, G1 and H1 you can dynamically select the city and zip code range. The file has sheets for january to april and you can set up sheets for the other months accordingly.
Jul 27 2022 10:28 PM
Jul 27 2022 11:40 PM
Solution@cravis777 Add a helper column to the data table that flags the rows as visible or not. Then, in the month sheets you can filter the original for the time period you want AND where the visible flag = 1.
I used the file that @OliverScheurich uploaded to demonstrates this method. See attached.
Jul 27 2022 11:44 PM
Jul 29 2022 04:02 PM
Jul 27 2022 11:40 PM
Solution@cravis777 Add a helper column to the data table that flags the rows as visible or not. Then, in the month sheets you can filter the original for the time period you want AND where the visible flag = 1.
I used the file that @OliverScheurich uploaded to demonstrates this method. See attached.