Creating a List

[Ad_B]

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

 

[Yard]

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.

 

[Aladin Akyurek]

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

 

[Ad_B]

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.

I have tried a pivot table, but I do not know how to make it so that it automatically selects only the items that are supplied by the number that is in the supplier no. cell.

 

[Ad_B]

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

How do I do this?

 

[Peter_SSs]

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

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

 

[Ad_B]

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

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?

 

[Peter_SSs]

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?

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

 

[Ad_B]

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

I got the result as a circular reference??

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

 

[Peter_SSs]

I got the result as a circular reference??

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

Maybe on the 'Consolidated' sheet you put the formula provided into G1 and not G2 as shown? G1 should just house a manually entered 0, or be empty.

If this is not it then please indicate where Excel says there is a circular reference.