Making a List from a worksheet database

I have a worksheet which is a database of things people have ordered.

Each Row has a customer number, date ordered, item number, item description.
customer name, customer address (I only have to enter the customer number,
and item number - the other fields fill in automatically using vlookup).

I'd like to have another worksheet for a year end type summary where I can
put the customer number on it in a given field and have it search through
the database and insert on the worksheet anything that particular customer
may have purchased.

Is this possible? How?

Thanks

Hi!

Piece of cake!

But, the easiest way to do this would be to just filter your current
database. If you would rather NOT use a filter post back and I'll explain
how to do this with formulas.

Biff

"Glenn" <[email protected]> wrote in message
news:eSEn%[email protected]...
>I have a worksheet which is a database of things people have ordered.
>
> Each Row has a customer number, date ordered, item number, item
> description. customer name, customer address (I only have to enter the
> customer number, and item number - the other fields fill in automatically
> using vlookup).
>
> I'd like to have another worksheet for a year end type summary where I can
> put the customer number on it in a given field and have it search through
> the database and insert on the worksheet anything that particular customer
> may have purchased.
>
> Is this possible? How?
>
> Thanks
>

I would rather not use a filter. I'd really appreciate it if you could
provdie the other instructions.


"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Piece of cake!
>
> But, the easiest way to do this would be to just filter your current
> database. If you would rather NOT use a filter post back and I'll explain
> how to do this with formulas.
>
> Biff
>
> "Glenn" <[email protected]> wrote in message
> news:eSEn%[email protected]...
> >I have a worksheet which is a database of things people have ordered.
> >
> > Each Row has a customer number, date ordered, item number, item
> > description. customer name, customer address (I only have to enter the
> > customer number, and item number - the other fields fill in
automatically
> > using vlookup).
> >
> > I'd like to have another worksheet for a year end type summary where I
can
> > put the customer number on it in a given field and have it search
through
> > the database and insert on the worksheet anything that particular
customer
> > may have purchased.
> >
> > Is this possible? How?
> >
> > Thanks
> >
>
>

Hi!

Ok, here's the quick overview and a link to a sample file I put together:

On sheet1 (database) the range is A1:F21, Row 1 are headers: Customer
Number, Date Ordered, Item number, Description, Name, Address.

You want to extract the dates, item numbers and descriptions for customer X.

On sheet2 create a drop down for the customer number list in say, B2. Put a
lookup formula in say, B3 to return the customer name based on the selection
made from the drop down.

Extract the data:

sheet2 D1:F1 headers - Date Ordered, Item Number, Description

Formula in cell D2 entered as an array with the key combo of
CTRL,SHIFT,ENTER:

=INDEX(Sheet1!B$2:B$21,SMALL(IF(Sheet1!$A$2:$A$21=$B$2,ROW($1:$20)),ROW(1:1)))

Copy across to F1 then down. Copy down to enough rows that will be sure to
return all the possible data. For example, if customer Jones is your best
customer and made the most purchases, 10, copy down AT LEAST 10 rows.

Now, if customer Smith only made 1 purchase then only 1 row of data will be
extracted and the other rows with formulas will return #NUM! errors. I used
conditional formatting to hide those. You can either use cf to hide those or
you can build an error trap into the formula. An error trap in the formula
makes the formula twice as long. The cf method is also more efficient.

Select the entire range of formulas, say that is D2:F17.
Goto Format>Conditional Formatting
Formula is: =ISERROR(D2)
Set the font color to be the same as the background fill color
OK out.

All this might sound somewhat complicated but it's really easy (especially
when you've done it 1000's of times!)

Here's a link to a sample file I put together (no macros, only 22kb)

http://s38.yousendit.com/d.aspx?id=1...I2Z40FWRGCP6GV

Biff

"Glenn" <[email protected]> wrote in message
news:[email protected]...
>I would rather not use a filter. I'd really appreciate it if you could
> provdie the other instructions.
>
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
>> Hi!
>>
>> Piece of cake!
>>
>> But, the easiest way to do this would be to just filter your current
>> database. If you would rather NOT use a filter post back and I'll explain
>> how to do this with formulas.
>>
>> Biff
>>
>> "Glenn" <[email protected]> wrote in message
>> news:eSEn%[email protected]...
>> >I have a worksheet which is a database of things people have ordered.
>> >
>> > Each Row has a customer number, date ordered, item number, item
>> > description. customer name, customer address (I only have to enter the
>> > customer number, and item number - the other fields fill in
> automatically
>> > using vlookup).
>> >
>> > I'd like to have another worksheet for a year end type summary where I
> can
>> > put the customer number on it in a given field and have it search
> through
>> > the database and insert on the worksheet anything that particular
> customer
>> > may have purchased.
>> >
>> > Is this possible? How?
>> >
>> > Thanks
>> >
>>
>>
>
>