I have a file that I am importing that has warehouses across the top and the
inventory items down the side. Not every warehouse has every item. When I
try to do a pivot table on the warehouse (part number in row area) I get
EVERY inventory item, even those with zero values. Is there any way,
excluding macros, to have the rows where the total inventory for that part
number is zero not display? Thanks for any help.

Note: I am using Excel 2002 - SP3

"les8" wrote:

I have a file that I am importing that has warehouses across the top and the
inventory items down the side. Not every warehouse has every item. When I
try to do a pivot table on the warehouse (part number in row area) I get
EVERY inventory item, even those with zero values. Is there any way,
excluding macros, to have the rows where the total inventory for that part
number is zero not display? Thanks for any help.

This should work..
assume Col A is product, Col B is qty, Col C is labelled "display?" and
rows 2 to 5 contain data
C2 contains a value calculated by the formula..
=IF(SUMIF($A$2:$A$5,A3,$B$2:$B$5)=0,"hide","show")
copy formula into c3 to c5.

In the pivot table put the "display" column in page and select only
"show". If you are using 'get external data' the formula can be
auto-added by the refresh.

regards...


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=525257

correction
cell c2 ... =IF(SUMIF($A$2:$A$5,A2,$B$2:$B$5)=0,"hide","show")


--
steven1001
------------------------------------------------------------------------
steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757
View this thread: http://www.excelforum.com/showthread...hreadid=525257