Pulling specific data from main sheet into a seperate one

Posted on

QUESTION :

I’m trying to figure out the best way to pull particular data from the main sheet, into a separate sheet.

It was a gym membership database, and we have staff, student and public members. They fill in a sheet online which automatically gets submitted into the database.

What I want to do is have all the rows that have answered ‘public’ into a sheet specifically for public members and the same for both staff and student members.

Picture of spreadsheet

Any advice on the best way to do that would be great

ANSWER :

Your issue can be solved by using a Helper Column along source data in Main Sheet.

enter image description here

How it works:

  • Source data range in MainSheet is
    A3:D12.
  • Enter desire Criteria in Cell A2 of
    OtherSheet.
  • Enter this Formula in Cell E3 of Main
    Sheet.

    =IF(D3<>OtherSheet!$A$2,"",IF(D3=OtherSheet!$A$2,1+MAX($E$2:E2)))

  • Fill Formula down.

  • Enter this Formula in Cell A4 of
    OtherSheet, then fill Right & Down.

=IFERROR(INDEX(MainSheet!A$3:D$12,MATCH(ROWS($1:1),MainSheet!$E$3:$E$12,0)),"")

N.B.

  • Criteria Public in OtherSheet's Cell A2
    makes the Formula versatile, since as soon you
    replace Public with Staff or
    Student, the Formula will filter data
    accordingly.

enter image description here

Adjust cell references in the Formula as needed.

Leave a Reply

Your email address will not be published. Required fields are marked *