Search code examples
excelexcel-formulaoffice365excel-online

How to copy Excel cells for matched value to other workseets?

I'm trying to build a way to track issued access cards. Our access cards are (4) digits in length. I'd like to track the status of the card "Issued, Faulty, Lost, Returned" as well as the date of the status change. Ideally, when someone updates the Card Status to a new status, the "Status, Card Number, and Date" cells would be copied to a separate worksheet named after those statuses. Thank you in advance! Example below with 5 worksheets "Main, Issued, Faulty, Lost, Returned":

MAIN WORKSHEET

Card Status Employee Card Number Date
Card Issued John 1234 7/9/2021
Card Faulty Paul 1235 7/9/2021
Card Lost Ringo 1236 7/9/2021
Card Returned George 1237 7/9/2021

Card Issued Worksheet

Card Status Employee Card Number Date
Card Issued John 1234 7/9/2021

Card Faulty Worksheet

Card Status Employee Card Number Date
Card Faulty Paul 1235 7/9/2021

Card Lost Worksheet

Card Status Employee Card Number Date
Card Lost Ringo 1236 7/9/2021

Card Returned Worksheet

Card Status Employee Card Number Date
Card Returned George 1237 7/9/2021
Solution

  • The FILTER function should work for Excel 365 for Web. Here is an example for the Card Issued worksheet:

    =FILTER(CardStatusTable,CardStatusTable[Card Status]="Card Issued")

    You put the body of the card status table into the "array" part of the function. Then put the column that contains the card status into the "include" part and test to see if it is equal to "Card Issued", "Card Faulty", etc.

    example

    Reference: https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759