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:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Completed[/TD]
[TD]Grand Total[/TD]
[/TR]
[TR]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl802, width: 81"]2018/10/25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]33[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl802, width: 81"]2018/10/26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]37[/TD]
[TD]37[/TD]
[/TR]
[TR]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl802, width: 81"]2018/10/29[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]36[/TD]
[TD]36[/TD]
[/TR]
</tbody>[/TABLE]

I have a table where I'd like to fetch the data right beside the date when it matches:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Completed[/TD]
[/TR]
[TR]
[TD]2018/10/25[/TD]
[TD]=GETPIVOTDATA("HostName",$A$3,"MigrationStatus","Completed","ScheduleDate","2018/10/25")[/TD]
[/TR]
[TR]
[TD]2018/10/26[/TD]
[TD]=GETPIVOTDATA("HostName",$A$3,"MigrationStatus","Completed","ScheduleDate","2018/10/26")[/TD]
[/TR]
</tbody>[/TABLE]

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.