Pivot table and pivotcaches not pulling correct data from source.


New Member
Dec 5, 2013

I'm having an intermittant problem with Excel 2010 which for some reason is pulling out a value of #n/a rather than the actual value in the field in the source data:

Data Structure/Flow:
Microsoft Access Table 1 -> SQL Database Query -> Excel Workbook A, Sheet 1

Excel Workbook A, Sheet 1 -> VLookup unique key data from Query for data in Excel Workbook, Sheet 2 to provide 18 additional columns of data.

Excel Workbook B: Mulitple Pivot Tables using all data on Workbook A Sheet 1, all using 1 pivotcache.

The pivot table is used to count the number of items with an "Outstanding" State (text lookup) from the DB query against a list of users (text lookup from the DB query) against a product release (Number, originally from Workbook A Excel Sheet 2, but now on the same line as the corresponding data on Sheet 1from the query ), so that we can quickly track the number of defects still against the product release and see where we are as a dashboard. ( Don't ask, i can't change to bugzilla, and the DB structure is locked down tighter than, well, you know, and can't be touched at all.)

Intermittently, one specific line in the database query keeps coming up in the PT with #N/A rather than the name of the defect owner, and i can't see why.

If i double click on the 1 item with N/A, the new sheet comes up with all fields apart from the unique ID as #N/A, which I don't understand. The DB query when pulled across to Excel definitely contains the right data in workbook A, sheet 1.

I can't provide any of this data to the forum to assist in debugging i'm afraid, but would really appreciate some guidance on what and where to look to try and diagnose/work around the problem.

Has anyone else seen similar effects to this? I can't find anything anywhere, all references to #N/A in a pivot table seem to refer to excluding #n/a in the source data from appearing in the PT.

Thanks in advance,

Last edited:

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Watch MrExcel Video

Forum statistics

Latest member