Search code examples
excelms-office

Populate sheet from primary sheet column with condition

I have been trying to resolve in Excel using VLOOKUP and INDEX/MATCH and am hitting a dead end.

Initial conditions are: Excel 2016 with no VBA and No Excel 365 capabilities (so I can't use the fancy array FILTER available in Excel Online)

I have a primary sheet with child sheets. Primary Sheet - MAIN MAIN Sheet

Child Sheet - DAILY DAILY - Child Sheet

All I am trying to do is read if the columns DAILY, WEEKLY or MONTHLY are set to "Yes" - then populate the respective Sheet with the data (FirstName, LastName, Team, Email) - to automate the creation of the Child Sheet - DAILY.

If any changes are made in MAIN Sheet they should automatically update the DAILY/MONTHLY/WEEKLY Sheets if it is set to Yes.

I am aware that filters may work if I keep this in one sheet - I am deliberately trying to filter this data into another Sheet.

Any answers would be appreciated.

Solution

  • So , this is what I have tried using INDEX(), AGGREGATE() & MATCH() Functions, Download_Workbook

    FORMULA_SOLUTION

    • Formula used in cell A2

    =IFERROR(INDEX(MAIN!$A$2:$G$6,AGGREGATE(15,6,(ROW(MAIN!$A$2:$G$6)-ROW(MAIN!$A$2)+1)/
((MATCH($E$1,MAIN!$E$1:$G$1,0)=(COLUMN(MAIN!$E$1:$G$1)-COLUMN(MAIN!$E$1)+1))*
(MAIN!$E$2:$G$6="Yes")),ROW(A1)),MATCH(A$1,MAIN!$A$1:$G$1,0)),"")

    This one formula serves the purpose of other sheets viz. WEEKLY & MONTHLY as well, paste the formula in cell A2 and then fill down and fill across !

    Refer screenshots for other sheets as well,

    WEEKLY_SHEET

    MONTHLY_SHEET