Jan 13 2022 02:46 PM
Hello I am trying to make my life a little bit easier, I have several sheets that I would like to extract an entire row using specific criteria in one cell. I am not tech savvy so coding and macros are not an option would I be able to achieve this using a formula?
The criteria is NT in cell block F from multiple sheets for example the picture shows 00GM14 and i would like to put all rows that read NT into the sheet labeled NT
Many Thanks in advance for any responses even if it is an impossibility as I have ran out of ways to search for the function required that doesn't seem like complete gibberish to me and havent found a yes or no answer yet.
Jan 13 2022 02:54 PM
Jan 13 2022 03:13 PM
=INDEX('00GM14'!A$2:A$6,SMALL(IF('00GM14'!$F$2:$F$6="NT",ROW($A$2:$A$6)),ROW(1:1))-1)
This formula works for data from one sheet as shown in the attached file. Enter formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Jan 13 2022 03:51 PM
= FILTER(Table1, Table1[faul state]="NT")
You didn't mention which version of Excel. The above works in Excel 365 and 2021 by converting the source data to a Table and applying the FILTER function. I was happy to finish with traditional spreadsheet methods, but others feel differently.