Not sure what function to use to return records that fit criteria
 

Ok here is what I want to do:
I have a sheet called 'Raw data' that has all my records in it. Then I have
three other sheets that each will be linked to 'raw data' but will only
contain specific records based on a field in 'Raw data'. For example raw
data sheet will look like this:

Name Type
john A
mike B
jerry C
Sarah --
Janet B

etc....

So I want sheet A to only give me the records that are Type 'A' (John in
this example)
I want sheet B to only give me the records that are Type 'B' (Mike & Janet
in this example)
I want sheet C to only give me the records that are Type 'C' (Jerry in this
example)

If a record in 'Raw data' sheet has neither A, B or C, then i don't want it
in any of my sheets.

How would I do this? Is it a Lookup or could I use an IF function?

Not sure what function to use to return records that fit criteria
 

Name and Type are defined name ranges.


In Sheet A:

=IF(ISERR(SMALL(IF(Type="A",ROW(INDIRECT("1:"&ROWS (Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF(Type ="A",ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1) )))

ctrl+shift+enter, not just enter
Copy down as far as needed

In Sheet B:

Copy the formula to Sheet B and change the criteria "A" to "B"
ctrl+shift+enter, not just enter
copy down as far as needed

and so on...


"Roy Bernal" wrote:

Ok here is what I want to do:
I have a sheet called 'Raw data' that has all my records in it. Then I have
three other sheets that each will be linked to 'raw data' but will only
contain specific records based on a field in 'Raw data'. For example raw
data sheet will look like this:

Name Type
john A
mike B
jerry C
Sarah --
Janet B

etc....

So I want sheet A to only give me the records that are Type 'A' (John in
this example)
I want sheet B to only give me the records that are Type 'B' (Mike & Janet
in this example)
I want sheet C to only give me the records that are Type 'C' (Jerry in this
example)

If a record in 'Raw data' sheet has neither A, B or C, then i don't want it
in any of my sheets.

How would I do this? Is it a Lookup or could I use an IF function?

Not sure what function to use to return records that fit crite
 

Thanks, I will try this...

"Teethless mama" wrote:

Name and Type are defined name ranges.


In Sheet A:

=IF(ISERR(SMALL(IF(Type="A",ROW(INDIRECT("1:"&ROWS (Name)))),ROWS($1:1))),"",INDEX(Name,SMALL(IF(Type ="A",ROW(INDIRECT("1:"&ROWS(Name)))),ROWS($1:1) )))

ctrl+shift+enter, not just enter
Copy down as far as needed

In Sheet B:

Copy the formula to Sheet B and change the criteria "A" to "B"
ctrl+shift+enter, not just enter
copy down as far as needed

and so on...


"Roy Bernal" wrote:

Ok here is what I want to do:
I have a sheet called 'Raw data' that has all my records in it. Then I have
three other sheets that each will be linked to 'raw data' but will only
contain specific records based on a field in 'Raw data'. For example raw
data sheet will look like this:

Name Type
john A
mike B
jerry C
Sarah --
Janet B

etc....

So I want sheet A to only give me the records that are Type 'A' (John in
this example)
I want sheet B to only give me the records that are Type 'B' (Mike & Janet
in this example)
I want sheet C to only give me the records that are Type 'C' (Jerry in this
example)

If a record in 'Raw data' sheet has neither A, B or C, then i don't want it
in any of my sheets.

How would I do this? Is it a Lookup or could I use an IF function?