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.

Part number	nil stock
1465
Part number	remarks
1465	excess
856	nil
158	excess
984	nil
156	nil
1984	excess
1	nil
14	excess

<colgroup><col width="84" style="width: 63pt; mso-width-source: userset; mso-width-alt: 3072;"><colgroup><col width="64" style="width: 48pt;"><tbody>
</tbody>

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.