software

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 DSmith2/1/061 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 amp; 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!

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$34gt;=$A$1)*('data
entry'!$C$3:$C$34lt;=$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

quot;steve_sr2quot; gt; wrote in message
news
gt;I have two sheets. Sheet1 has multiple lines of data. Based on criteria
gt; chosen in sheet2, I need to display in sheet2 all of the rows of data that
gt; match the criteria. I'm having trouble with multiple criteria (i.e. for
gt; name1, I want all matching rows of data in a certain timeframe. Not all
gt; data
gt; in ColA is are same name and will not be in any particular order.
gt;
gt; Sheet1
gt; A B C D E F G H I
gt; Bryan D Smith 2/1/06 1 2 2 6 6 1
gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt;
gt; Sheet 2 would return based on user selecting between dates of 3/1/06 amp;
gt; 3/15/06
gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt;
gt; I can get it to return all of the items for that match in column A but how
gt; do I for certain dates?
gt;
gt; Here is my formula that I'm using to get all rows that match
gt;
gt; {=INDEX('data entry'!$C$3:$U$34,SMALL(IF('data
gt; entry'!$C$3:$C$34=$F$5,ROW($1:$32)),ROW(1:1)),3)}
gt;
gt; Thanks in advance for any help!

hi!

i think an quot;advance filterquot; will suffice!
have you tired that?

-via135steve_sr2 Wrote:
gt; I have two sheets. Sheet1 has multiple lines of data. Based on
gt; criteria
gt; chosen in sheet2, I need to display in sheet2 all of the rows of data
gt; that
gt; match the criteria. I'm having trouble with multiple criteria (i.e.
gt; for
gt; name1, I want all matching rows of data in a certain timeframe. Not
gt; all data
gt; in ColA is are same name and will not be in any particular order.
gt;
gt; Sheet1
gt; A B C D E F G H I
gt; Bryan DSmith2/1/061 2 2 6 6 1
gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt;
gt; Sheet 2 would return based on user selecting between dates of 3/1/06 amp;
gt; 3/15/06
gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt;
gt; I can get it to return all of the items for that match in column A but
gt; how
gt; do I for certain dates?
gt;
gt; Here is my formula that I'm using to get all rows that match
gt;
gt; {=INDEX('data entry'!$C$3:$U$34,SMALL(IF('data
gt; entry'!$C$3:$C$34=$F$5,ROW($1:$32)),ROW(1:1)),3)}
gt;
gt; Thanks in advance for any help!--
via135
------------------------------------------------------------------------
via135's Profile: www.excelforum.com/member.php...oamp;userid=26725
View this thread: www.excelforum.com/showthread...hreadid=516454Hi

Anoher way:
On Sheet1, add a (hidden) column as leftmost (column A) into your table.
Into this column, enter some formula, which numbers all rows, matching all
your criterias on Sheet2. I.e. with criterias in your example, your table
will look like
Bryan D Smith 2/1/06 1 2 2 6 6 1
1 Bryan D Jones 3/1/06 2 1 3 4 2 1
2 Bryan D Smith 3/5/06 1 1 1 1 1 1

On Sheet2, use VLOOKUP to get matching rows from Sheet1 - i.e. in 1st row of
return table is displayed data from Sheet1 with 1 in column A, in next row
with 2, etc.Arvi Laanemetsquot;steve_sr2quot; gt; wrote in message
news
gt; I have two sheets. Sheet1 has multiple lines of data. Based on criteria
gt; chosen in sheet2, I need to display in sheet2 all of the rows of data that
gt; match the criteria. I'm having trouble with multiple criteria (i.e. for
gt; name1, I want all matching rows of data in a certain timeframe. Not all
data
gt; in ColA is are same name and will not be in any particular order.
gt;
gt; Sheet1
gt; A B C D E F G H I
gt; Bryan D Smith 2/1/06 1 2 2 6 6 1
gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt;
gt; Sheet 2 would return based on user selecting between dates of 3/1/06 amp;
3/15/06
gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt;
gt; I can get it to return all of the items for that match in column A but how
gt; do I for certain dates?
gt;
gt; Here is my formula that I'm using to get all rows that match
gt;
gt; {=INDEX('data entry'!$C$3:$U$34,SMALL(IF('data
gt; entry'!$C$3:$C$34=$F$5,ROW($1:$32)),ROW(1:1)),3)}
gt;
gt; Thanks in advance for any help!
Thanks for the help....Should this be entered with Ctr Shift Enter?

quot;Biffquot; wrote:

gt; Hi!
gt;
gt; One way:
gt;
gt; Use 2 cells to hold the date criteria:
gt;
gt; A1 = 3/1/06
gt; B1 = 3/15/06
gt;
gt; Array entered:
gt;
gt; =INDEX('data entry'!$A$3:$U$34,SMALL(IF(('data
gt; entry'!$C$3:$C$34gt;=$A$1)*('data
gt; entry'!$C$3:$C$34lt;=$B$1),ROW($1:$32)),ROW(1:1)),CO LUMNS($A:A))
gt;
gt; Copy across then down.
gt;
gt; It'll start extracting from column A (name).
gt;
gt; Biff
gt;
gt; quot;steve_sr2quot; gt; wrote in message
gt; news
gt; gt;I have two sheets. Sheet1 has multiple lines of data. Based on criteria
gt; gt; chosen in sheet2, I need to display in sheet2 all of the rows of data that
gt; gt; match the criteria. I'm having trouble with multiple criteria (i.e. for
gt; gt; name1, I want all matching rows of data in a certain timeframe. Not all
gt; gt; data
gt; gt; in ColA is are same name and will not be in any particular order.
gt; gt;
gt; gt; Sheet1
gt; gt; A B C D E F G H I
gt; gt; Bryan D Smith 2/1/06 1 2 2 6 6 1
gt; gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt; gt;
gt; gt; Sheet 2 would return based on user selecting between dates of 3/1/06 amp;
gt; gt; 3/15/06
gt; gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt; gt;
gt; gt; I can get it to return all of the items for that match in column A but how
gt; gt; do I for certain dates?
gt; gt;
gt; gt; Here is my formula that I'm using to get all rows that match
gt; gt;
gt; gt; {=INDEX('data entry'!$C$3:$U$34,SMALL(IF('data
gt; gt; entry'!$C$3:$C$34=$F$5,ROW($1:$32)),ROW(1:1)),3)}
gt; gt;
gt; gt; Thanks in advance for any help!
gt;
gt;
gt;

Yes...

In article gt;,
steve_sr2 gt; wrote:

gt; Thanks for the help....Should this be entered with Ctr Shift Enter?
gt;
gt; quot;Biffquot; wrote:
gt;
gt; gt; Hi!
gt; gt;
gt; gt; One way:
gt; gt;
gt; gt; Use 2 cells to hold the date criteria:
gt; gt;
gt; gt; A1 = 3/1/06
gt; gt; B1 = 3/15/06
gt; gt;
gt; gt; Array entered:
gt; gt;
gt; gt; =INDEX('data entry'!$A$3:$U$34,SMALL(IF(('data
gt; gt; entry'!$C$3:$C$34gt;=$A$1)*('data
gt; gt; entry'!$C$3:$C$34lt;=$B$1),ROW($1:$32)),ROW(1:1)),CO LUMNS($A:A))
gt; gt;
gt; gt; Copy across then down.
gt; gt;
gt; gt; It'll start extracting from column A (name).
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;steve_sr2quot; gt; wrote in message
gt; gt; news
gt; gt; gt;I have two sheets. Sheet1 has multiple lines of data. Based on criteria
gt; gt; gt; chosen in sheet2, I need to display in sheet2 all of the rows of data that
gt; gt; gt; match the criteria. I'm having trouble with multiple criteria (i.e. for
gt; gt; gt; name1, I want all matching rows of data in a certain timeframe. Not all
gt; gt; gt; data
gt; gt; gt; in ColA is are same name and will not be in any particular order.
gt; gt; gt;
gt; gt; gt; Sheet1
gt; gt; gt; A B C D E F G H I
gt; gt; gt; Bryan D Smith 2/1/06 1 2 2 6 6 1
gt; gt; gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; gt; gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt; gt; gt;
gt; gt; gt; Sheet 2 would return based on user selecting between dates of 3/1/06 amp;
gt; gt; gt; 3/15/06
gt; gt; gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; gt; gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt; gt; gt;
gt; gt; gt; I can get it to return all of the items for that match in column A but how
gt; gt; gt; do I for certain dates?
gt; gt; gt;
gt; gt; gt; Here is my formula that I'm using to get all rows that match
gt; gt; gt;
gt; gt; gt; {=INDEX('data entry'!$C$3:$U$34,SMALL(IF('data
gt; gt; gt; entry'!$C$3:$C$34=$F$5,ROW($1:$32)),ROW(1:1)),3)}
gt; gt; gt;
gt; gt; gt; Thanks in advance for any help!
gt; gt;
gt; gt;
gt; gt;

That worked great. Thx. Couple of more questions. If I were to add an
additional element into the equation, for instance, employee name in between
certain dates, how would the formula look. Also, What does the asterisk in
the formula do?

quot;Domenicquot; wrote:

gt; Yes...
gt;
gt; In article gt;,
gt; steve_sr2 gt; wrote:
gt;
gt; gt; Thanks for the help....Should this be entered with Ctr Shift Enter?
gt; gt;
gt; gt; quot;Biffquot; wrote:
gt; gt;
gt; gt; gt; Hi!
gt; gt; gt;
gt; gt; gt; One way:
gt; gt; gt;
gt; gt; gt; Use 2 cells to hold the date criteria:
gt; gt; gt;
gt; gt; gt; A1 = 3/1/06
gt; gt; gt; B1 = 3/15/06
gt; gt; gt;
gt; gt; gt; Array entered:
gt; gt; gt;
gt; gt; gt; =INDEX('data entry'!$A$3:$U$34,SMALL(IF(('data
gt; gt; gt; entry'!$C$3:$C$34gt;=$A$1)*('data
gt; gt; gt; entry'!$C$3:$C$34lt;=$B$1),ROW($1:$32)),ROW(1:1)),CO LUMNS($A:A))
gt; gt; gt;
gt; gt; gt; Copy across then down.
gt; gt; gt;
gt; gt; gt; It'll start extracting from column A (name).
gt; gt; gt;
gt; gt; gt; Biff
gt; gt; gt;
gt; gt; gt; quot;steve_sr2quot; gt; wrote in message
gt; gt; gt; news
gt; gt; gt; gt;I have two sheets. Sheet1 has multiple lines of data. Based on criteria
gt; gt; gt; gt; chosen in sheet2, I need to display in sheet2 all of the rows of data that
gt; gt; gt; gt; match the criteria. I'm having trouble with multiple criteria (i.e. for
gt; gt; gt; gt; name1, I want all matching rows of data in a certain timeframe. Not all
gt; gt; gt; gt; data
gt; gt; gt; gt; in ColA is are same name and will not be in any particular order.
gt; gt; gt; gt;
gt; gt; gt; gt; Sheet1
gt; gt; gt; gt; A B C D E F G H I
gt; gt; gt; gt; Bryan D Smith 2/1/06 1 2 2 6 6 1
gt; gt; gt; gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; gt; gt; gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt; gt; gt; gt;
gt; gt; gt; gt; Sheet 2 would return based on user selecting between dates of 3/1/06 amp;
gt; gt; gt; gt; 3/15/06
gt; gt; gt; gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt; gt; gt; gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt; gt; gt; gt;
gt; gt; gt; gt; I can get it to return all of the items for that match in column A but how
gt; gt; gt; gt; do I for certain dates?
gt; gt; gt; gt;
gt; gt; gt; gt; Here is my formula that I'm using to get all rows that match
gt; gt; gt; gt;
gt; gt; gt; gt; {=INDEX('data entry'!$C$3:$U$34,SMALL(IF('data
gt; gt; gt; gt; entry'!$C$3:$C$34=$F$5,ROW($1:$32)),ROW(1:1)),3)}
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks in advance for any help!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;

Hi!

Just add another array:

A1 = 3/1/06
B1 = 3/15/06
C1 = some name

Assuming th names are in column A.

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

gt;What does the asterisk in the formula do?

It's the multiplication operator. The (now) 3 arrays are multiplied
together:

(A3:A34=C1)*(C3:C34gt;=A1)*(C3:C34lt;=B1)

For a deluxe explanation of how this formula works, see this

tinyurl.com/njzjh

Biffquot;steve_sr2quot; gt; wrote in message
...
gt; That worked great. Thx. Couple of more questions. If I were to add an
gt; additional element into the equation, for instance, employee name in
gt; between
gt; certain dates, how would the formula look. Also, What does the asterisk
gt; in
gt; the formula do?
gt;
gt; quot;Domenicquot; wrote:
gt;
gt;gt; Yes...
gt;gt;
gt;gt; In article gt;,
gt;gt; steve_sr2 gt; wrote:
gt;gt;
gt;gt; gt; Thanks for the help....Should this be entered with Ctr Shift Enter?
gt;gt; gt;
gt;gt; gt; quot;Biffquot; wrote:
gt;gt; gt;
gt;gt; gt; gt; Hi!
gt;gt; gt; gt;
gt;gt; gt; gt; One way:
gt;gt; gt; gt;
gt;gt; gt; gt; Use 2 cells to hold the date criteria:
gt;gt; gt; gt;
gt;gt; gt; gt; A1 = 3/1/06
gt;gt; gt; gt; B1 = 3/15/06
gt;gt; gt; gt;
gt;gt; gt; gt; Array entered:
gt;gt; gt; gt;
gt;gt; gt; gt; =INDEX('data entry'!$A$3:$U$34,SMALL(IF(('data
gt;gt; gt; gt; entry'!$C$3:$C$34gt;=$A$1)*('data
gt;gt; gt; gt; entry'!$C$3:$C$34lt;=$B$1),ROW($1:$32)),ROW(1:1)),CO LUMNS($A:A))
gt;gt; gt; gt;
gt;gt; gt; gt; Copy across then down.
gt;gt; gt; gt;
gt;gt; gt; gt; It'll start extracting from column A (name).
gt;gt; gt; gt;
gt;gt; gt; gt; Biff
gt;gt; gt; gt;
gt;gt; gt; gt; quot;steve_sr2quot; gt; wrote in message
gt;gt; gt; gt; news
gt;gt; gt; gt; gt;I have two sheets. Sheet1 has multiple lines of data. Based on
gt;gt; gt; gt; gt;criteria
gt;gt; gt; gt; gt; chosen in sheet2, I need to display in sheet2 all of the rows of
gt;gt; gt; gt; gt; data that
gt;gt; gt; gt; gt; match the criteria. I'm having trouble with multiple criteria
gt;gt; gt; gt; gt; (i.e. for
gt;gt; gt; gt; gt; name1, I want all matching rows of data in a certain timeframe.
gt;gt; gt; gt; gt; Not all
gt;gt; gt; gt; gt; data
gt;gt; gt; gt; gt; in ColA is are same name and will not be in any particular order.
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Sheet1
gt;gt; gt; gt; gt; A B C D E F G H I
gt;gt; gt; gt; gt; Bryan D Smith 2/1/06 1 2 2 6 6 1
gt;gt; gt; gt; gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt;gt; gt; gt; gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Sheet 2 would return based on user selecting between dates of
gt;gt; gt; gt; gt; 3/1/06 amp;
gt;gt; gt; gt; gt; 3/15/06
gt;gt; gt; gt; gt; Bryan D Jones 3/1/06 2 1 3 4 2 1
gt;gt; gt; gt; gt; Bryan D Smith 3/5/06 1 1 1 1 1 1
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; I can get it to return all of the items for that match in column A
gt;gt; gt; gt; gt; but how
gt;gt; gt; gt; gt; do I for certain dates?
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Here is my formula that I'm using to get all rows that match
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; {=INDEX('data entry'!$C$3:$U$34,SMALL(IF('data
gt;gt; gt; gt; gt; entry'!$C$3:$C$34=$F$5,ROW($1:$32)),ROW(1:1)),3)}
gt;gt; gt; gt; gt;
gt;gt; gt; gt; gt; Thanks in advance for any help!
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;gt; gt; gt;
gt;gt;