View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
Workforce_info_Kings_NHS Workforce_info_Kings_NHS is offline
external usenet poster
 
Posts: 1
Default Show only relevant no-data items in pivot table

Currently using Excel 2003

I have two pivot tables based upon same data source. Both are summarised in
rows by two data items, but second table may not necessarily contain same
number of rows because of page selection criteria.
In older version of Excel I could "show items with no data" and this would
ensure that both pivot tables would always have the same number of rows.
When I try to build this sort of table and "show items with no data"
a) First data item shows all the data items for irrelevant categories (i.e.
these are categories that can not (should not) appear because they are
definitively excluded by page selection item.
b) Second data item is attempting to show empty data lines for data items
that only belong to particular sub-categories of the first data item. (e.g.
if first data item had category "Nurse" and "Administrator", and second data
item had category "Staff Nurse" which will only ever be a valid category if
the first data item is "Nurse", the pivot table is now showing this group
under "Administrator" as well.)

I can deal with problem "a" by manually unflagging the unwanted categories.
However this approach doesn't work for problem "b".