software

Hi,

I have a Main Sheet amp; a Data Sheet.

The Data Sheet looks like:Date Name Shift-Time Login Logout AHT
15/1/06 john 1:30-10:30 1:35 10:29 532
15/1/06 Aron 4:30-1:30 4:30 1:35
430
15/1/06 Jacob 4:30-1:30 4:30 1:35 600
15/1/06 Jill 4:30-1:30 4:30 1:35
750
15/2/06 John 3:30-12:30 3:30 1:35
332
15/2/06 Aron 5:30-2:30 5:30 2:35
350
15/3/06 Ami 5:30-1:30 5:30 2:35
600
15/3/06 Jill 6:30-3:30 6:30 3:35
560
15/3/06 Jacob 4:30-1:30 4:30 1:35 560

etc.....

In Main Sheet, in top 2 Rows ( in Center), i have a cell with drop-down
Data Validation List to choose Dates.

Just below that, in another cell, i have a drop-down Data Validation
List to choose Names.

Below that i have a Table consisting of following headers.

Date Name Shift-Time AHT
----------------------------------------------

1] When i select a date from Dates Dropdown list amp; a name from names
dropdpwn list, i should get the date for that name below along with the
other details.

2] If i just want the Date amp; leave the Name blank.... i should get all
the names of people (along with their other details) one below the
other for that date.(somewhat like a filtered list).

3] If i just want the Name amp; leave the Date blank.... i should get all
the Dates for that person (along with their other details) one below
the other (somewhat like a filtered list).
i.e. get all the dates amp; other relevant data on which that person has
worked.

Please note, that i dont want to use filtered lists, but want to use
only 2 drop-down menus.
How to achieve this using worksheet functions or VBA???PLEASE HELP ASAP!Here's a play using non-array formulas which delivers exactly what you're after

A sample construct is available at:
www.savefile.com/files/9480166
Auto Extract Lines into another sheet based on 2 DV selection.xls

Source data assumed in sheet: Data, cols A to F, data from row2 down

In sheet: Main,

DVs in B1:B2 (select [or clear] date / select [or clear] name)
Headers in A44: Date, Name, Shift-Time, AHT

In A5, copied to C5:
=IF(ISERROR(SMALL($E:$E,ROW(A1))),quot;quot;,INDEX(Data!A: A,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0)-3))

In D5:
=IF(ISERROR(SMALL($E:$E,ROW(A1))),quot;quot;,INDEX(Data!F: F,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0)-3))

In E5:
=IF(AND($B$1=quot;quot;,$B$2=quot;quot;),quot;quot;,IF(AND($B$1=quot;quot;,Data!B2 =$B$2),ROW(),IF(AND($B$2=quot;quot;,Data!A2lt;gt;quot;quot;,Data!A2=$B $1),ROW(),IF(AND($B$2lt;gt;quot;quot;,$B$1lt;gt;quot;quot;,Data!A2lt;gt;quot;quot;,Dat a!A2=$B$1,Data!B2=$B$2),ROW(),quot;quot;))))
(Leave E1:E4 empty)

Select A5:E5, copy down to say, E20
to cover the max expected extent of data in source sheet: Data
(Hide away the criteria col E, if desired)

Format col A as dates

The above will return the required results -- depending on the combination
of DV selections made in B1:B2 [combination includes the DV cell(s) being
cleared] -- with all extracted lines neatly bunched at the top
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;junoonquot; wrote:
gt; Hi,
gt;
gt; I have a Main Sheet amp; a Data Sheet.
gt;
gt; The Data Sheet looks like:
gt;
gt;
gt; Date Name Shift-Time Login Logout AHT
gt; 15/1/06 john 1:30-10:30 1:35 10:29 532
gt; 15/1/06 Aron 4:30-1:30 4:30 1:35
gt; 430
gt; 15/1/06 Jacob 4:30-1:30 4:30 1:35 600
gt; 15/1/06 Jill 4:30-1:30 4:30 1:35
gt; 750
gt; 15/2/06 John 3:30-12:30 3:30 1:35
gt; 332
gt; 15/2/06 Aron 5:30-2:30 5:30 2:35
gt; 350
gt; 15/3/06 Ami 5:30-1:30 5:30 2:35
gt; 600
gt; 15/3/06 Jill 6:30-3:30 6:30 3:35
gt; 560
gt; 15/3/06 Jacob 4:30-1:30 4:30 1:35 560
gt;
gt; etc.....
gt;
gt; In Main Sheet, in top 2 Rows ( in Center), i have a cell with drop-down
gt; Data Validation List to choose Dates.
gt;
gt; Just below that, in another cell, i have a drop-down Data Validation
gt; List to choose Names.
gt;
gt; Below that i have a Table consisting of following headers.
gt;
gt; Date Name Shift-Time AHT
gt; ----------------------------------------------
gt;
gt; 1] When i select a date from Dates Dropdown list amp; a name from names
gt; dropdpwn list, i should get the date for that name below along with the
gt; other details.
gt;
gt; 2] If i just want the Date amp; leave the Name blank.... i should get all
gt; the names of people (along with their other details) one below the
gt; other for that date.(somewhat like a filtered list).
gt;
gt; 3] If i just want the Name amp; leave the Date blank.... i should get all
gt; the Dates for that person (along with their other details) one below
gt; the other (somewhat like a filtered list).
gt; i.e. get all the dates amp; other relevant data on which that person has
gt; worked.
gt;
gt; Please note, that i dont want to use filtered lists, but want to use
gt; only 2 drop-down menus.
gt;
gt;
gt;
gt; How to achieve this using worksheet functions or VBA???
gt;
gt;
gt; PLEASE HELP ASAP!
gt;
gt;

Thanks a Bunch, Max!

You are Gr8!Warm regards,

JunoonYou're welcome !
Thanks for the feedback ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;junoonquot; wrote:
gt; Thanks a Bunch, Max!
gt; You are Gr8!
gt; Warm regards,
gt; Junoon