Help with GetPivotData formula

[pujo]

Made my first PivotTable.
I want to get data from the PivotTable into a cell on another sheet.
Using the GETPIVOTDATA function, I can retrieve data however...

This returns exactly what the formula says.

=(GETPIVOTDATA(" Products",ProductionPivot!$B$2,"Months",4,"Years",2023))

I want to retrieve data based on current month and year. (MONTH(TODAY()), YEAR(TODAY()) if possible.
How do I accomplish this?

Thanks!

 

[Skybluekid]

Have you not answered you own question?

 

[Alex Blakenburg]

As @Skybluekid indicated, you are almost there.
=(GETPIVOTDATA(" Products",ProductionPivot!$B$2,"Months",MONTH(TODAY()),"Years",YEAR(TODAY()) ))
PS: I personally don't like having Today() in formulas. I would put it in a cell and then reference that cell.

 

[pujo]

Have you not answered you own question?

Should have been that simple however I was getting an error in the formula.
My first pivot table...

 

[pujo]

As @Skybluekid indicated, you are almost there.
=(GETPIVOTDATA(" Products",ProductionPivot!$B$2,"Months",MONTH(TODAY()),"Years",YEAR(TODAY()) ))
PS: I personally don't like having Today() in formulas. I would put it in a cell and then reference that cell.

When I use the reference "Months"(Today()) or year, I ge tthe #REF! error.
I don't know much about pivot tables, what am I missing?

 

[RoryA]

The #REF! error means that there is no cell in the pivot table matching the specific criteria. Are you sure there is a single cell matching Products data for May 2023 in the pivot table?

 

[pujo]

The #REF! error means that there is no cell in the pivot table matching the specific criteria. Are you sure there is a single cell matching Products data for May 2023 in the pivot table?

Yes, "Months", "Years" are present in the pivot table.

This formula works however, it is specific to one month:

=(GETPIVOTDATA("Products",ProductionPivot!$B$2,"Months",4,"Years",2023))

I am trying to get the current month using this formula that returns the #REF! error:

=(GETPIVOTDATA("Products",ProductionPivot!$B$2,"Months",(Month(Today())),"Years",(Year(Today())) ))

I've even tried to use cell references for the current month and year and still getting the same error.

=(GETPIVOTDATA("Products",ProductionPivot!$B$2,"Months",ProductionPivot!$A$1,"Years",ProductionPivot!$A$2))
A1: =(MONTH(TODAY()))
A2: =(YEAR(TODAY()))

Can't figure it out...

 

[RoryA]

Does this work though:

=(GETPIVOTDATA("Products",ProductionPivot!$B$2,"Months",5,"Years",2023))

 

[pujo]

Does this work though:

=(GETPIVOTDATA("Products",ProductionPivot!$B$2,"Months",5,"Years",2023))

Yes, this formula works, but how would I make it to where the values would always be for the current month?
If I am reading the formula correct, the formula will always display the value from May, 2023. (Not dynamic)
Excuse my ignorance, this is my first pivot table...

 

[Alex Blakenburg]

@pujo, Rory was trying to make sure that you actually had data for May 2023 especially since it has only just clicked over into May and it would not be too surprising if you had no May data yet which would cause the REF error.

This is what the formula(s) look like at my end.
(although I have the data and pivot on the same sheet)

Note: Like I said previously I prefer not to have Today() in the actual formula.
1) It makes it easier to test the formula as you can try different dates.
2) It makes is easier to run a different period should you need to eg on the 1st of the month you still want last months figures

20230502 Pivot GetPivot Formula pujo v02.xlsm
	A	B	C	D	E	F	G	H
1						Input
2		Years	Months	Sum of Products		Month	5
3		2023	4	5800		Year	2023
4			5	23800
5		2023 Total		29600		Result 1	23800	=GETPIVOTDATA("Products",$B$2,"Months",$G$2,"Years",$G$3)
6		Grand Total		29600		Result 2	23800	=GETPIVOTDATA("Products",$B$2,"Months",MONTH(TODAY()),"Years",YEAR(TODAY()))
7						With error handling	23800	=IFERROR(GETPIVOTDATA("Products",$B$2,"Months",$G$2,"Years",$G$3),"")
8
Sheet1

Cell Formulas
Range	Formula
G2	G2	=MONTH(TODAY())
G3	G3	=YEAR(TODAY())
G5	G5	=GETPIVOTDATA("Products",$B$2,"Months",$G$2,"Years",$G$3)
H5:H7	H5	=FORMULATEXT(G5)
G6	G6	=GETPIVOTDATA("Products",$B$2,"Months",MONTH(TODAY()),"Years",YEAR(TODAY()))
G7	G7	=IFERROR(GETPIVOTDATA("Products",$B$2,"Months",$G$2,"Years",$G$3),"")