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.

  1. 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.
  2. 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.
  3. As we can see, it takes row 2 and 3 as they have a ‘y’ in the Marketing column. 
  4. 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”)
  5. As we can see, it takes row 2 and 4 as they have a ‘y’ in the Commercial column. 
  6. This is a really powerful formula; it can be really useful as the pages are updated dynamically. 
Excel Spreadsheet ExampleDownload