Forum Discussion

melaniestokes1985's avatar
melaniestokes1985
Occasional Reader
Jan 15, 2026

Summary sheet, copy entire row if a cell criteria is met

https://arengio-my.sharepoint.com/:x:/g/personal/melanie_stokes_areng_io/IQC2OhPfZnKEQL6qfPj11gqfAQSh8Em3Zp95fkYbLBTZc3g?e=oNU1pEHi

Can you please help me, I have a schedule for jobcards, each tab is a book that is given to someone, if that specific jobcard is invoices, then in column T where it says "Incomplete", I want that entire row to be copied to the "Outstanding Jobcards"tab.  As a new book is handed out, a new tab is create and the tab is named according to the book sequence.  This is updated daily as jobcards are being invoiced.

admin
community
excel
Formulas and Functions

2 Replies

  • IlirU's avatar
    IlirU
    Brass Contributor
    Jan 16, 2026

     

    Hi,

    For each of the tables, I have added a column (the first column) with the name of each sheet you used in your file.

    Look at the Sheet named: Outstanding Jobcards where I used Power Query. This sheet gives all the INCOMPLETE data for all the sheets you have in your workbook.

    If you want to see INCOMPLETE only for sheets that contain the word ACTIVE then in cell A1 of the Outstanding Jobcards sheet click on the small arrow that is located to the right of this cell. A window will open. Select Text Filters then click on Contains. In the window that opens write ACTIVE. This way you will have filtered only the sheets that contain the ACTIVE sheet.

    I am also attaching the file that you can download.

    Hope this helps.

    IlirU

     

    https://1drv.ms/x/c/511e91319deee91f/IQBNS6ic9Z6kSI9BMux53l9VARsA2q9zLTUbKr0WX9vzCyk?e=9eoAy2

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jan 15, 2026

    I have entered this formula onto that sheet:

    =LET(in,TRIMRANGE(VSTACK(start:end!A1:Z1000)),out,FILTER(in, CHOOSECOLS(in,20)="Incomplete"),IF(out="","",out))

    NOTES:

    - I inserted 2 sheets: 'start' and 'end' to mark the range of sheets to check

    - this formula does NOT filter sheets that have been named ... -CLOSED

    - your header on the 'OUTSTANDING JOBCARDS' sheet only goes to column O (DAYS OUTSTANDING) but this formula pulls the full rows through column U.  If you only want through column O then you can wrap the final part of the LET statement with a TAKE so the end would look like: TAKE(IF(out="","",out),,15) )

     

     

Resources