Making a List from a worksheet database

  • Thread starter Thread starter Glenn
  • Start date Start date
G

Glenn

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
 
I would rather not use a filter. I'd really appreciate it if you could
provdie the other instructions.
 
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=1MZDG3N063GXI2Z40FWRGCP6GV

Biff
 
Back
Top