Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort & Show Data
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort & Show Data
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 A4:D4: 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<"",Dat a!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 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "junoon" wrote: 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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort & Show Data
Thanks a Bunch, Max!
You are Gr8! Warm regards, Junoon |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sort & Show Data
You're welcome !
Thanks for the feedback .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "junoon" wrote: Thanks a Bunch, Max! You are Gr8! Warm regards, Junoon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sort source workbook data, maintain formulas in destination workb. | Excel Worksheet Functions | |||
Match and Sort for two range of data on different worksheets? | New Users to Excel | |||
How to Match and Sort two range of data? | New Users to Excel | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
macro to sort data in worksheet by specific date | Excel Discussion (Misc queries) |