Trouble pasting results from Pivot table into acceptable format for Master Spreadsheet

[Christopher]

I have been using the Excel Pivot Table successfully to count and list
the occurence of certain jobs types spread over the 50 States.

However, I need to somehow paste the results from these multiple data
sets into a master spreadsheet so that the correct count for each
state lines up in the appropriate column.

The problem is, the pivot table will only list the States that have
openings for a particular job, so there are gaps in the calculated
list which don't allign with the (50) sets of columns in the Master
Sheet.

Any help or suggestions would be greatly appreciated !!

 

[Debra Dalgleish]

You could use a GETPIVOTDATA formula to extract the data from the
PivotTable. For example, if the first state name is in cell A2 on the
master sheet:

=IF(ISERROR(GETPIVOTDATA("Jobs",Pivot!$A$3,"State",A2)),,GETPIVOTDATA("Jobs",Pivot!$A$3,"State",A2))

This example is for Excel 2002 -- the GETPIVOTDATA arguments vary in
other versions of Excel.

 

[Bob Cresto]

I don't know what your data looks like, but I would paste all the data into
one database and then use the pivot table as the master with States in one
column and job types across the top and occurrence as the data.

 

[Dave Peterson]

I don't know what your pivottable looks like either, but maybe you could just
force the pivottable to "show items with no values"

If you double click on a row item, you can check that box.

(or rightclick on the grey header button and select Field Settings.)