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

 

[Ad_B]

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.

I have now got some results, however it is only showing the first and last item sourced by the buyer selected from the supplier selected.

There are six lines that should be displayed, however it is only displaying two?

I have checked the formula and it seems to be correct...

 

[Peter_SSs]

I have now got some results, however it is only showing the first and last item sourced by the buyer selected from the supplier selected.

There are six lines that should be displayed, however it is only displaying two?

I have checked the formula and it seems to be correct...

The method definitely works for more than two. It's a bit hard to diagnose your particular problem without seeing your data or your formulas.

The most likely causes are

1. The formulas in the helper columns not copied down far enough, or

2. Your buyer and/or supplier names not being identical in all the rows. In particular, check for any leading or trailing spaces.

If you still cannot locate the problem, please post a small sample of data and formulas, ensuring you include at least one of the 'problem' rows from the 'Consolidated' (or equivalent) sheet.

Another diagnostic tool would be this. Suppose the two blue rows below should be flowing to the summary sheet but only one of them is, use a couple of vacant cells with formulas like I have in I9 & J9. Notice how D9 and D11 appear identical but the formula in J9 shows FALSE. (The reason in this case is that D9 has a space after the visible text - 'Buyer 2 ')

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
9	Sup No 3	Item 3	Desc 8	Buyer 2			1		TRUE	FALSE
10	Sup No 1	Item 4	Desc 9	Buyer 1			1
11	Sup No 3	Item 5	Desc 10	Buyer 2			2
Consolidated

 

[Ad_B]

The method definitely works for more than two. It's a bit hard to diagnose your particular problem without seeing your data or your formulas.

The most likely causes are

1. The formulas in the helper columns not copied down far enough, or

2. Your buyer and/or supplier names not being identical in all the rows. In particular, check for any leading or trailing spaces.

If you still cannot locate the problem, please post a small sample of data and formulas, ensuring you include at least one of the 'problem' rows from the 'Consolidated' (or equivalent) sheet.

Another diagnostic tool would be this. Suppose the two blue rows below should be flowing to the summary sheet but only one of them is, use a couple of vacant cells with formulas like I have in I9 & J9. Notice how D9 and D11 appear identical but the formula in J9 shows FALSE. (The reason in this case is that D9 has a space after the visible text - 'Buyer 2 ')

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
9	Sup No 3	Item 3	Desc 8	Buyer 2			1		TRUE	FALSE
10	Sup No 1	Item 4	Desc 9	Buyer 1			1
11	Sup No 3	Item 5	Desc 10	Buyer 2			2
Consolidated

Peter,

I've managed to get your original solution to work. Not sure how, but I was just trying different things whilst waiting for a response from you. All seems to be working fine now!

Thanks for your help on this! Greatly appreciated!

Ad_B