Creating a List

Yard said:

Have you considered a Pivot Table? That would be a much more flexible tool to give you filtered results and reports from a data table.

Click to expand...

Aladin Akyurek said:

Care to provide a small sample from which to contruct/extract a list?

Click to expand...

Ad_B said:

Hi All!

I have a sheet of information which includes supplier numbers, item numbers and item descriptions.

Is there a way to create a table on a different sheet which includes the item number and description of all the items supplied by a supplier.

The supplier number will be entered in a seperate cell and the list will need to change according to the supplier number entered in the cell.

Can this be done?

I hope it makes sense...

Thanks

Ad_B

Click to expand...

Peter_SSs said:

Maybe like this. This is the main sheet with all the data.

Formula in F2 is copied down (then col F could be hidden)

Excel Workbook
	A	B	C	D	E	F
1	Supplier No	Item No	Description			Helper
2	Sup No 1	Item 1	Desc 1			Sup No 1|1
3	Sup No 1	Item 2	Desc 2			Sup No 1|2
4	Sup No 2	Item 3	Desc 3			Sup No 2|1
5	Sup No 3	Item 4	Desc 4			Sup No 3|1
6	Sup No 2	Item 5	Desc 5			Sup No 2|2
7	Sup No 1	Item 1	Desc 6			Sup No 1|3
8	Sup No 3	Item 2	Desc 7			Sup No 3|2
9	Sup No 3	Item 3	Desc 8			Sup No 3|3
10	Sup No 1	Item 4	Desc 9			Sup No 1|4
11
Consolidated

Then sheet for your individual list.
Supplier of interest entered in A2.
Formula in F2 copied down as far as you might need (then col F could be hidden)
Formula in B2 copied across and down.

Excel Workbook
	A	B	C	D	E	F
1	Supplier No	Item No	Description
2	Sup No 3	Item 4	Desc 4			5
3		Item 2	Desc 7			8
4		Item 3	Desc 8			9
5						#N/A
6						#N/A
Supplier

Click to expand...

Ad_B said:

Thanks Peter!!! This works great!!

In addition, each item has a buyer associated with it (e.g. Buyer 1, Buyer 2, etc)

Is there a way to consolidate the list created so that it only shows the items sourced from the supplier by the buyer?

Click to expand...

Peter_SSs said:

Sure.

Excel Workbook
	A	B	C	D	E	F	G
1	Supplier No	Item No	Description	Buyer			0
2	Sup No 1	Item 1	Desc 1	Buyer 1			0
3	Sup No 1	Item 2	Desc 2	Buyer 2			0
4	Sup No 2	Item 3	Desc 3	Buyer 1			0
5	Sup No 3	Item 4	Desc 4	Buyer 2			1
6	Sup No 2	Item 5	Desc 5	Buyer 1			1
7	Sup No 1	Item 1	Desc 6	Buyer 2			1
8	Sup No 3	Item 2	Desc 7	Buyer 1			1
9	Sup No 3	Item 3	Desc 8	Buyer 2			2
10	Sup No 1	Item 4	Desc 9	Buyer 1			2
Consolidated

Excel Workbook
	A	B	C	D	E	F
1	Supplier	Item No	Description
2	Sup No 3	Item 4	Desc 4			5
3		Item 3	Desc 8			9
4	Buyer					#N/A
5	Buyer 2					#N/A
6						#N/A
Supplier

Click to expand...

Ad_B said:

I got the result as a circular reference??

I have tried to duplicate what you have posted and got the same?

Click to expand...