Sort & Show Data

[junoon]

Hi,

I have a Main Sheet & 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 & 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 & 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 & 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 & 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!

 

[Guest]

Here's a play using non-array formulas which delivers exactly what you're after

A sample construct is available at:
http://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))),"",INDEX(Data!A:A,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0)-3))

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

In E5:
=IF(AND($B$1="",$B$2=""),"",IF(AND($B$1="",Data!B2=$B$2),ROW(),IF(AND($B$2="",Data!A2<>"",Data!A2=$B$1),ROW(),IF(AND($B$2<>"",$B$1<>"",Data!A2<>"",Data!A2=$B$1,Data!B2=$B$2),ROW(),""))))
(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

---

Hi,

I have a Main Sheet & 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 & 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 & 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 & 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 & 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!

 

[junoon]

Thanks a Bunch, Max!

You are Gr8!


Warm regards,

Junoon

 

[Guest]

You're welcome !
Thanks for the feedback ..