GetPivotData Returning Error when Using Reference cell for Date

[Crane]

Hello,

I need some help, using getpivotdata function for the first time. My pivot looks like this:

Date	Completed	Grand Total
2018/10/25 <tbody> </tbody>	33	33
2018/10/26 <tbody> </tbody>	37	37
2018/10/29 <tbody> </tbody>	36	36

<tbody>
</tbody>

I have a table where I'd like to fetch the data right beside the date when it matches:

Date	Completed
2018/10/25	=GETPIVOTDATA("HostName",$A$3,"MigrationStatus","Completed","ScheduleDate","2018/10/25")
2018/10/26	=GETPIVOTDATA("HostName",$A$3,"MigrationStatus","Completed","ScheduleDate","2018/10/26")

<tbody>
</tbody>

Using the above formula returns the value without errors. But when I replace the scheduledate with the cell reference, for instance, E2, which is the cell containing the date 2018/10/25, I get the "#Ref!" error. This is the formula is use:

=GETPIVOTDATA("HostName",$A$3,"MigrationStatus","Completed","ScheduleDate",E2)

Please help how to correct this. Thank you.

 

[sandy666]

maybe it will help:

read REMARKS here
Getpivotdata()

or here: how to...

 

[RoryA]

Judging by the quotes in your working version, the date field is actually text, so you'd need something like this :

=GETPIVOTDATA("HostName",$A$3,"MigrationStatus","Completed","ScheduleDate",TEXT(E2,"yyyy/mm/dd"))

 

[Crane]

Thank you, Sandy. I tried virtually everything. I read those sources as well until I finally end up here again..none worked

 

[Crane]

Rory, you truly are an MVP! Can't thank you enough! Have a great year, all the best!

 

[RoryA]

Glad to help.