Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup and show all results
I'm kinda new to this, so please bear with me if something isn't clear in
this. I pull data from one worksheet and summarize it on another, my question is this: If I have a lookup function that looks for the date in one worksheet and gives me the results for that date in another, how do I get all the results posted in separate cells for the same lookup date? Here's what I do: in worksheet A we have years worth of numbers in the form A B C 1 1/1/2006 8 4 2 1/1/2006 5 6 3 1/1/2006 6 5 4 1/1/2006 8 4 and I want to get that to a worksheet that only shows week to week (and the dates are changed week to week). I set it up the same way, A being the date, B the numbers, etc, but I don't know how to get A1's B1, etc, to to go to the summary worksheet and then that work for all dates that are later entered. In the other summary pages, there is only one row per date and the lookup function works great, but on this one where there is multiple rows for each date, I'm only getting the last one for each date displayed for all the rows of the same date. How do I get all the corresponding numbers for each date to go to the summary worksheet in the same order? Thanks for anyone's help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup and show all results
Here's one play to try, using non-array formulas ..
Sample construct available at: http://www.savefile.com/files/8960288 LookUp_and_Show_All_Results_JAHanlon_wks.xls Assume source data in sheet: A, cols A to C from row1 down (dates are in col A) In sheet: B The StartDate will be input in B1, EndDate in D1 (EndDate input should be =StartDate If we want to filter results for only 1 particular date, enter the same date in both B1 and D1) Put in say, F1: =IF(OR(A!A1="",$B$1="",$D$1=""),"", IF(AND(A!A1=$B$1,A!A1<=$D$1),ROW(),"")) Copy down to say, F20, to cover the max expected extent of data in sheet: A Put in say, A3: =IF(ISERROR(SMALL($F:$F,ROW(A1))),"", INDEX(A!A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0))) Copy A3 across to C3, fill down to C22 (cover the same extent as done in col F) Format A3:A22 as date The "filtered" lines will be extracted within A3:C20, all neatly bunched at the top. Adapt to suit. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "JAHanlon" wrote in message ... I'm kinda new to this, so please bear with me if something isn't clear in this. I pull data from one worksheet and summarize it on another, my question is this: If I have a lookup function that looks for the date in one worksheet and gives me the results for that date in another, how do I get all the results posted in separate cells for the same lookup date? Here's what I do: in worksheet A we have years worth of numbers in the form A B C 1 1/1/2006 8 4 2 1/1/2006 5 6 3 1/1/2006 6 5 4 1/1/2006 8 4 and I want to get that to a worksheet that only shows week to week (and the dates are changed week to week). I set it up the same way, A being the date, B the numbers, etc, but I don't know how to get A1's B1, etc, to to go to the summary worksheet and then that work for all dates that are later entered. In the other summary pages, there is only one row per date and the lookup function works great, but on this one where there is multiple rows for each date, I'm only getting the last one for each date displayed for all the rows of the same date. How do I get all the corresponding numbers for each date to go to the summary worksheet in the same order? Thanks for anyone's help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup and show all results
That worked perfectly. Thanks so much for your help, it is greatly
appreciated. Sincerely, Andy "Max" wrote: Here's one play to try, using non-array formulas .. Sample construct available at: http://www.savefile.com/files/8960288 LookUp_and_Show_All_Results_JAHanlon_wks.xls Assume source data in sheet: A, cols A to C from row1 down (dates are in col A) In sheet: B The StartDate will be input in B1, EndDate in D1 (EndDate input should be =StartDate If we want to filter results for only 1 particular date, enter the same date in both B1 and D1) Put in say, F1: =IF(OR(A!A1="",$B$1="",$D$1=""),"", IF(AND(A!A1=$B$1,A!A1<=$D$1),ROW(),"")) Copy down to say, F20, to cover the max expected extent of data in sheet: A Put in say, A3: =IF(ISERROR(SMALL($F:$F,ROW(A1))),"", INDEX(A!A:A,MATCH(SMALL($F:$F,ROW(A1)),$F:$F,0))) Copy A3 across to C3, fill down to C22 (cover the same extent as done in col F) Format A3:A22 as date The "filtered" lines will be extracted within A3:C20, all neatly bunched at the top. Adapt to suit. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "JAHanlon" wrote in message ... I'm kinda new to this, so please bear with me if something isn't clear in this. I pull data from one worksheet and summarize it on another, my question is this: If I have a lookup function that looks for the date in one worksheet and gives me the results for that date in another, how do I get all the results posted in separate cells for the same lookup date? Here's what I do: in worksheet A we have years worth of numbers in the form A B C 1 1/1/2006 8 4 2 1/1/2006 5 6 3 1/1/2006 6 5 4 1/1/2006 8 4 and I want to get that to a worksheet that only shows week to week (and the dates are changed week to week). I set it up the same way, A being the date, B the numbers, etc, but I don't know how to get A1's B1, etc, to to go to the summary worksheet and then that work for all dates that are later entered. In the other summary pages, there is only one row per date and the lookup function works great, but on this one where there is multiple rows for each date, I'm only getting the last one for each date displayed for all the rows of the same date. How do I get all the corresponding numbers for each date to go to the summary worksheet in the same order? Thanks for anyone's help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup and show all results
Glad it worked for you !
Thanks for the feedback .. -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "JAHanlon" wrote in message ... That worked perfectly. Thanks so much for your help, it is greatly appreciated. Sincerely, Andy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP(?) Challenge | Excel Worksheet Functions | |||
How to overcome LOOKUP function problems? | Excel Worksheet Functions | |||
Lookup values in multipul sheets and show value in another sheet | Excel Worksheet Functions | |||
lookup tables in cells | Excel Worksheet Functions | |||
Problem with IF condition or vector lookup? | Excel Worksheet Functions |