GetPivotData REF! Error when field exists.

  • Hi!

    I've searched Google and looked at countless posts to no avail, and was wondering if anyone could shed any light to this problem.

    I currently have an Access database linked in a pivot table as per image below:
    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tdataset.png Views:\t0 Size:\t81.5 KB ID:\t1224444","data-align":"none","data-attachmentid":"1224444","data-size":"full","title":"dataset.png"}[/ATTACH]
    "RTime" is a date field in the "Short Time" format in the database and Date/Time in Excel.

    I'm currently designing an Excel report in another sheet in the same workbook that shows for which Agent how many Values are in the IBWWCR-OL field.

    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tdataset2.png Views:\t0 Size:\t36.3 KB ID:\t1224445","data-align":"none","data-attachmentid":"1224445","data-size":"full","title":"dataset2.png"}[/ATTACH]

    However, the above happens. I've narrowed it down as the RTime field, as when i delete it from the GetPivotData formula, it shows the correct value, but as a sum not as per individual date which is what i require. The field RTime exists and im not sure why its saying it doesnt. The Date input also exists in the data set.

    Any ideas on why this would throw a Cell reference error?

    Thanks in advance :)

  • The labels in a PivotTable, including dates, are all displayed as Text. So, when trying to match up against the numerical date in A1, it's failing. You might have success by changing the "$A$1" argument to this
    TEXT($A$1, "dd/mm/yyyy h:mm")

    Best Regards,
    Luke M
    "A little knowledge is a dangerous thing."

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!