A client asked me today if I could create an excel workbook that could have a Master List and replicate these records to other sheets based on a “y” being put in a certain column. At first, I thought I would have to use some kind of macro, after a little research I found the FILTER function. This function is a life saver and does exactly what I wanted, it even updates in real time. I have also attached an example spreadsheet at the bottom of the page.
- The idea is that the first page is the “Master List”, this page will contain all records. Then on the subsequent pages we will put a formula that only takes the record from this “Master List” if there is a “y” in the particular column.
- On the marketing page we will add the formula =FILTER(‘Master List’!A:D,’Master List’!E:E=”y”) , in this formula ‘Master List’ is the page name, A:D tells the formula to copy columns A-D, E:E is the column that tells the formula whether or not to take the data if there is a ‘y’ in that column for that row or data.
- As we can see, it takes row 2 and 3 as they have a ‘y’ in the Marketing column.
- Now on the Commercial page, the process is exactly the same, however in the formula =FILTER(‘Master List’!A:D,’Master List’!E:E=”y”) , we change the E:E to F:F so it will check the Commercial column on the master list page instead. =FILTER(‘Master List’!A:D,’Master List’!F:F=”y”)
- As we can see, it takes row 2 and 4 as they have a ‘y’ in the Commercial column.
- This is a really powerful formula; it can be really useful as the pages are updated dynamically.