Attempting to use GETPIVOTDATA to find information for an employee based on referenced date.

[8BITADDICTION]

Hello, I have only recently started diving into GETPIVOTDATA and I am stumped as to how to have the following happen.

I would like to have the information for an employee to show up in a cell on another sheet by referencing their name on that sheet. but because we need to deal with different data every day, It needs to reference the date that is selected.

here is the formula I have written, it does not through a #REF or any error, but it isn't showing data so the cell ends up blank.

=IFERROR(IF(GETPIVOTDATA("[Measures].[Count of Duration]",'Activity Pivot'!$A$3,"[Activity RAW].[Label]","[Activity RAW].[Label].&[ACW]","[Activity RAW].[Sort Name]","[Activity RAW].[Sort Name].&["&'Automatic (WIP)'!$D7&"]","[Activity RAW].[Date]","[Activity RAW].[Date].&["&'Automatic (WIP)'!$C$1&"]")="","",GETPIVOTDATA("[Measures].[Count of Duration]",'Activity Pivot'!$A$3,"[Activity RAW].[Label]","[Activity RAW].[Label].&[ACW]","[Activity RAW].[Sort Name]","[Activity RAW].[Sort Name].&["&'Automatic (WIP)'!$D7&"]","[Activity RAW].[Date]","[Activity RAW].[Date].&["&'Automatic (WIP)'!$C$1&"]")),"")

If anyone has been successful in having this work, please let me know!

 

[BSALV]

let excel work for you, start in an empty cell with "=" and point to the desired value in your pivottable, then ajust some parameters, add iferror, etc.
That's a lot easier then looking for your error.

 

[8BITADDICTION]

let excel work for you, start in an empty cell with "=" and point to the desired value in your pivottable, then ajust some parameters, add iferror, etc.
That's a lot easier then looking for your error.

I have done that and it works up until I try to reference my Date field. The date field is a Data Validation list. that is formatted to be a the same Date layout as the PivotTable (yyyy-mm-dd) and is set as a Date as well. I am not sure what to do with it, I tried moving the Date field on the PivotTable to Filter and then I am unable to reference it.

 

[BSALV]

i just tried it myself with such a date in a pivottable and pointing to a pivotcell and then modifying the found formula.
So i think you have to make a real date again of the cell with data-validation, like i did in AW9 and AX9

=+GETPIVOTDATA("E",$I$1,"DATE ",AX9,"B","B","C","E","D","F")

If you use the date only once, you can integrate the formula from AW9 into the formula above.

Map1
	AT	AU	AV	AW	AX
1	jan			formula by pointing	4
2	feb
3	mrt			data validation	2022-mrt-26
4	apr
5	mei			10	2022
6	jun				3
7	jul				26
8	aug
9	sep			10	26/03/2022
10	okt
11	nov
12	dec
Blad1

Cell Formulas
Range	Formula
AX1	AX1	=+GETPIVOTDATA("E",$I$1,"DATE ",DATE(2022,5,9),"B","D","D","F")
AW5	AW5	=+GETPIVOTDATA("E",$I$1,"DATE ",DATE(AX5,AX6,AX7),"B","B","C","E","D","F")
AX5	AX5	=--LEFT($AX$3,4)
AX6	AX6	=MATCH(MID($AX$3,6,3),$AT$1:$AT$12,0)
AX7	AX7	=--RIGHT($AX$3,2)
AW9	AW9	=+GETPIVOTDATA("E",$I$1,"DATE ",AX9,"B","B","C","E","D","F")
AX9	AX9	=DATE(LEFT($AX$3,4),MATCH(MID($AX$3,6,3),$AT$1:$AT$12,0),RIGHT($AX$3,2))
AT1:AT12	AT1	=TEXT(DATE(0,ROW(),1),"mmm")

Cells with Data Validation
Cell	Allow	Criteria
AX3:AX4	List	=$G$2:$G$100