View Single Post
#6
Posted to microsoft.public.excel.worksheet.functions
 steve_sr2 Posts: n/a
return multiple rows of data based on criteria

Thanks for the help....Should this be entered with Ctr+Shift+Enter?

"Biff" wrote:

Hi!

One way:

Use 2 cells to hold the date criteria:

A1 = 3/1/06
B1 = 3/15/06

Array entered:

=INDEX('data entry'!\$A\$3:\$U\$34,SMALL(IF(('data
entry'!\$C\$3:\$C\$34=\$A\$1)*('data
entry'!\$C\$3:\$C\$34<=\$B\$1),ROW(\$1:\$32)),ROW(1:1)),CO LUMNS(\$A:A))

Copy across then down.

It'll start extracting from column A (name).

Biff

"steve_sr2" wrote in message
...
I have two sheets. Sheet1 has multiple lines of data. Based on criteria
chosen in sheet2, I need to display in sheet2 all of the rows of data that
match the criteria. I'm having trouble with multiple criteria (i.e. for
name1, I want all matching rows of data in a certain timeframe. Not all
data
in ColA is are same name and will not be in any particular order.

Sheet1
A B C D E F G H I
Bryan D Smith 2/1/06 1 2 2 6 6 1
Bryan D Jones 3/1/06 2 1 3 4 2 1
Bryan D Smith 3/5/06 1 1 1 1 1 1

Sheet 2 would return based on user selecting between dates of 3/1/06 &
3/15/06
Bryan D Jones 3/1/06 2 1 3 4 2 1
Bryan D Smith 3/5/06 1 1 1 1 1 1

I can get it to return all of the items for that match in column A but how
do I for certain dates?

Here is my formula that I'm using to get all rows that match

{=INDEX('data entry'!\$C\$3:\$U\$34,SMALL(IF('data
entry'!\$C\$3:\$C\$34=\$F\$5,ROW(\$1:\$32)),ROW(1:1)),3)}

Thanks in advance for any help!