Does =GetPivotData work in a reliable way?????

I have an Access table of data relating to the current working days of the week (Mon to whatever day of the week it is)

I have created a pivot table which displays this data - No problem.

I then want an excel range which displays the positions for each day of the week. This links to the pivot table, so each cell contains the following

formula:

=IF(ISNA(GETPIVOTDATA(Data,[letter] & " " & [Date])),0,GETPIVOTDATA(Data,[Letter] & " " & [Date]))

- obviously I am using cell references to reference respective [Letter] and [Date] pairings. The ISNA is used to handle when no data for the pair is returned from the pivot table.

Now Assuming this is run on Wednesday the table could contain data for Monday and Tuesday, if it contains data for both days all works fine. But if there is no data for Monday then whilst the pivot table obviously works correctly just showing Tuesday data, the summary table appears as below.

MonDate TueDate WedDate ThuDate FriDate

A #Ref 100 0 0 0

B #Ref 200 0 0 0

C #Ref 150 0 0 0

D #Ref 140 0 0 0

E #Ref 0 0 0 0

Now if I go to the Access table and add a record for Monday all now works again, giving the table below.

MonDate TueDate WedDate ThuDate FriDate

A 0 100 0 0 0

B 0 200 0 0 0

C 0 150 0 0 0

D 25 140 0 0 0

E 0 0 0 0 0

I cannot see what I am doing wrong?

Addition Observations/information...

All the cells contain the same formula!

On Wednesday, if I force a piece of Friday data into the Access Table, this is registers even though there is nothing for Wednesday and Thursday!

The [Letters] are actually generated via links to the Pivot Table

The [Dates] are held as Text everywhere, Access, Pivot Table and summary table, but are generated for the GetPivotData references by =TEXT([Reference], "dd/mm/yy") - This obviously matches because data is returned.

I have to get this working so any help will be gratefully accepted!

Paul Smith