getpivotdata function in googlesheet with date as reference.

[kyegozun]

Hi, i've been searching the internet for a possible solution about the getpivotdata function in googlesheet but can't find a working solution.

In this picture, I want to pull a data in a pivot and show in the table below(red circle).
I'm using this formula

=GETPIVOTDATA("Calls",$Q$1,"DATE",$Q46,"Name",$R46)
where $Q$1 is the reference pivot.
$Q46 is the cell that contains the reference date that I want to change everytime.
$R46 is the cell that contains the reference name that I want to change everytime.

My problem is making the cell Q46 as the reference date doesn't work in googlesheets. But its working fine in excel.
I've attached the sample data. Please is there anyone can help me.

 

[shili12]

googlesheet forum is thataway =>

 

[Debaser]

Try using "Date" rather than "DATE"

 

[kyegozun]

Try using "Date" rather than "DATE"

Tried it but still gives an error.

 

[pecoflyer]

Cross post https://www.mrexcel.com/board/threa...esheet-when-making-date-as-reference.1260915/

 

[Debaser]

Worked for me. I suggest you post a link to your file on google

 

[kyegozun]

Worked for me. I suggest you post a link to your file on google

Hi sir. This is the link i'm working on.

Copy of Copy of Weekly Report - Client Care

DATA Date,Name,Calls,Emails,Texts,Convo,Calls Missed Non-Zillow, Set Zillow, Held Zillow,Not Held Zillow, Set Non-Zillow, Held Non-Zillow,Not Held Non-Zillow,Pickups,Call attempts,Date,Name,Calls,Emails March 28, 2024,June Dagohoy,357,22,199,23,10,3,2,1,9,6,3,4,6,March 28, 2024,Casey Doane,201,2...

docs.google.com

 

[Debaser]

Seems to be working in that file with the added text function

 

[kyegozun]

Seems to be working in that file with the added text function

Yeah. Someone told me to use the TEXT function. Thank you for your time replying and checking the link. I appreciate it so much.

For those reading this post with the same problem as mine see the details below for the correction.

=GETPIVOTDATA("Calls",$Q$1,"Date",TEXT(Q46,"mmmm d, yyyy"),"Name",$R46)

text function was added to match the reference date format.

 

[Debaser]

Someone told me to use the TEXT function.

So where else did you post the same question, besides Mr Excel?

 

[kyegozun]

So where else did you post the same question, besides Mr Excel?

i tried stacksoverflow
https://stackoverflow.com/questions...ction-using-date-as-reference-in-another-cell

 

[p45cal]

and

https://www.reddit.com/r/googlesheets/comments/1dpoegu

 

[kyegozun]

Reading excelguru's message for cross posting made me realize that doing this may upset people. I'm now fully aware the consequences that this behavior can have. I apologize to everyone, this wont happen again and thank you for all the people contributing in this community.

 

[pecoflyer]

Again, cross-posting is not forbidden, but you must add links to all the cross-posts. This being said, in my experience , the more cross-posts, the less chance of getting an answer...