=INDEX question?

[Merellia]

I have a database in which I've been collecting art historical information about representations of people engaged in a particular activity, one person per row in Sheet1. One of my columns (H) is for gender (M/F), and another is for whether a certain item is present (Column I, with data either Yes, No, or Other). There are header rows in this sheet.

I'd like to have my database duplicate a subset of information from Sheet1 to Sheet2, so that the second sheet shows me only the data for rows that meet the criteria from both H AND I columns (for example all M with item Yes).

I thiiiiink I would use =INDEX for this? But I'm not sure of how to formulate it. Any suggestions would be appreciated!

 

[63falcondude]

Why don't you filter your data, and then copy that to a new page?

 

[CyrusTheVirus]

You could do this with advanced filter, but that would still take a setup. But, if you want a formulaic approach, then maybe a set up like the below would help? Paste data in G1.

You can change the Criteria however you'd like (in cells L2 and M2).

And just modify the formulas how you need to, since I didn't put this on two difference sheets.

[TABLE="width: 628"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Gender
[/TD]
[TD]Results
[/TD]
[TD]Helper
[/TD]
[TD][/TD]
[TD]Gender Criteria
[/TD]
[TD]Results Criteria
[/TD]
[TD]No. of Matches
[/TD]
[/TR]
[TR]
[TD]John
[/TD]
[TD]Male
[/TD]
[TD]No
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD]Male
[/TD]
[TD]Other
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Barbara
[/TD]
[TD]Female
[/TD]
[TD]Other
[/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Daniel
[/TD]
[TD]Male
[/TD]
[TD]Other
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD]Name
[/TD]
[TD]Gender
[/TD]
[TD]Results
[/TD]
[/TR]
[TR]
[TD]Samuel
[/TD]
[TD]Male
[/TD]
[TD]Yes
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD]Daniel
[/TD]
[TD]Male
[/TD]
[TD]Other
[/TD]
[/TR]
[TR]
[TD]Kathy
[/TD]
[TD]Female
[/TD]
[TD]No
[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD]Brad
[/TD]
[TD]Male
[/TD]
[TD]Other
[/TD]
[/TR]
[TR]
[TD]Brad
[/TD]
[TD]Male
[/TD]
[TD]Other
[/TD]
[TD="align: right"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


J2:

=SUM(AND($H2=$L$2,$I2=$M$2),$J1)

N2:

=MAX($J$2:$J$7)

L5 (and copy through N whatever):

=IF(ROWS(L$5:L5)>$N$2,"",INDEX(G$2:G$7,MATCH(ROWS(L$5:L5),$J$2:$J$7,0)))