Populating a list from a data table

[troop2118]

Hi,

I need to populate a list from a data table (see attachment as an example). This data table contains the monthly sales data. I would like to populate a list in a new sheet based on 1 criteria "client name" column D.

Requirement (1)
Preferably I would like to have a drop down list on this new sheet that allows me to choose from all the clients that are listed in the data table.

Requirement (2)
The Drop Down list with the client names should update automatically based on the names that are in the data table, since every month there will be new client names.

Requirement (3)
The list that I am trying to populate should list out the data from the columns "Date", "Report No", "Supplier", "Description", "QTY", "Price".

Any ideas if and how this can be done?

PS: The Sales data table will have different row numbers every month, somewhere between 2000 and 3000 usually.

[Russell Dawson]

Why not just filter?

[royUK]

For a start you are using one of the newer versions of Excel so make the data a Table. Then use Russell's suggestion

[troop2118]

Filter would work, However, I don't want to show all columns in the "new" file and I would like to have a different format since this "new" file will be send out to customers.
Requirement 2 would not be so important (believe this is the most difficult).

I found this post:
http://www.excelforum.com/excel-gene...onditions.html

In its core it does what I need (I need only one criteria). However, I can't figure out how they did this.

[Fotis1991]

Hi

One way is this

In Sheet2!A1, create a Validation List to choose your Suppliers

In Row 2, starting in Column B, put your Headers.

In B3 and copy across and then down, put this Array Formula(Control+Shift+Enter)Not just Enter.

Please Login or Register  to view this content.

Is this, works for you?

[Fotis1991]

Most obviously, there is a small error in the formula, which gives wrong results ..

Apologize for this.

Please Login or Register  to view this content.