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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>

Glad it worked for you !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"JAHanlon" <[email protected]> wrote in message
news:[email protected]...
> That worked perfectly. Thanks so much for your help, it is greatly
> appreciated.
>
> Sincerely, Andy