Hello Friend;
Good Morning;
My name is Mitul Parikh.
In f Excel workbook attached, in Getpivotdata with Data validation - worksheet , I am having 2 difficulties.
1) In month column, all months are checked, but it is showing up only January & February.
2) I placed countries - UK & USA in cell P21 , but, in GetPivotdata in cell N28 , getting #REF! error.
Also, there is GetPivotData with Row function worksheet (which has same problem of getting #REF! error in cell G43)
Please give me correct solution for all 3 problems and explain me what I am doing wrong on Sheet1 worksheet.
You can ignore previous topic submitted for GetPivotdata with Row function problem.
Thank you very much.
Have a great day.
Hi Mitul,
The ROW function returns the row number, so in your formula:
=GETPIVOTDATA("Sum of 2016",, "Order Date", ROW(F22), "Years", 2016)
It's looking for order date (month) number 22, and of course there isn't ever going to be month number 22.
That said, your source data is in the wrong format for a PivotTable as you already have the years pivoted into separate columns for each year.
You need a tabular layout for PivotTables: https://www.myonlinetraininghub.com/excel-tabular-data-format
It should have the following columns:
Country (this should be populated for all rows in the table)
Month
Year
Value/Amount
Until you fix the layout of the data you can't use the GETPIVOTDATA function the way it's written above because you don't have a 'Years' field in your PivotTable.
I recommend you always start building GETPIVOTDATA by entering an = sign and then selecting a field in the PivotTable that you want. This will insert the GETPIVOTDATA formula for you, then you can edit the hard keyed arguments in the formula to reference cells or use functions like ROW to complete the arguments dynamically.
In your second attempt you don't have a total for the countries present in the PivotTable, so you can't get this value from the PivotTable with GETPIVOTDATA. Again, because your data layout is incorrect. (GETPIVOTDATA will only return values that are present in the PivotTable).
Mynda
This is Mitul.
Good Morning
Thank you very much, Mynda;
I truly appreciate it.
