Require formula

[Ron99]

Hello,

The header is the advance filter, where I can select part number from the drop down, and below that is the data with part numbers and the remarks column showing excess and nil stock.
When I select the part number in the header from the drop down, I want the NIL STOCK part numbers to appear under the nil stock column in 1 cell.

[TABLE="width: 111"]
<colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"><colgroup><col width="64" style="width: 48pt;"><tbody>[TR]
[TD="width: 84, bgcolor: #44546A"]Part number[/TD]
[TD="width: 64, bgcolor: #44546A"]nil stock[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1465[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #44546A"]Part number[/TD]
[TD="bgcolor: #44546A"]remarks[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1465[/TD]
[TD="bgcolor: transparent"]excess[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]856[/TD]
[TD="bgcolor: transparent"]nil
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]158[/TD]
[TD="bgcolor: transparent"]excess[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]984[/TD]
[TD="bgcolor: transparent"]nil[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]156[/TD]
[TD="bgcolor: transparent"]nil[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1984[/TD]
[TD="bgcolor: transparent"]excess[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]nil[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]14[/TD]
[TD="bgcolor: transparent"]excess
[/TD]
[/TR]
</tbody>[/TABLE]

In the header Nil stock the formula should show all nil stock part numbers (856,984,156,1)

Regards,
Ron

 

[James006]

Hello,

Instead of selecting a part number in your advanced filter, have you tried to type in nil in the appropriate column ?

It should produce the expected result ...

HTH

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	Part number	remarks			nil
2	1465	excess
3	856	nil			FALSE
4	158	excess			Part number	remarks
5	984	nil			856	nil
6	156	nil			984	nil
7	1984	excess			156	nil
8	1	nil			1	nil
9	14	excess
Sheet1

E3 contains the following formula:

=B2=$E$1

You can now run Advanced Filter with $E$2:$E$3 as Criteria range.