Vlookup or Index,Match solution for a template

[ajayxlnc2]

Hi,

Sample file has been attached.
In the sample file there is a pivot worksheet, Template and Data worksheets
In the Pivot Worksheet i have put a pivot of the Group & Roles and the count of Status (<3 or <=3).
I have a template as shown in Template ws. The template is similar to the layout of the Pivot table and little bit modified in order to be shown as a snapshot.

If can remove the subtotals from the Pivot and then paste the count of the status (<3, or <=3) in the template and thats it, it would be done. But i dont want to do that, i want to put a vlookup or Index, match function so that i can match the count of status from the pivot worksheet every week. The challenge is that i am not able to find a solution for there are many roles which are same for all the groups.
For Example, For App Delivery i want to match A5:D9 of Pivot Table to the Template. The roles might change.
My Requirement is i want a generic function where in it will pull the data irrespective of the rows in App Delivery this week there are 4 roles, next week it might be 2 or even 1 role. So the formula has to check for Match App Delivery in the Pivot table for the Pivot worksheet and pull the values for App Delivery and update the fields for the App Deliver in the Template worksheet.

 

[SirJB7]

Hi, ajayxlnc2!
I'd suggest you to start playing a bit with PivotTables GetData and GetPivotData methods. Check the built-in help.
Just as a tip, remember that if you enter in an empty cell "=XXX(" (unquoted) and press the "fx" button at the left of the edition bar text box, you'll be prompted with the function wizard which shows every argument, gives a brief description of the function and by the bottom left link lets you access the built-in or online help. It works for any function.
The same from the VBA editor, you can place the cursor (keyboard, so click, don't hover with mouse) on any VBA reserved word and press F1 to access the same type of help.
Regards!

 

[ajayxlnc2]

Hi SirJB7,

Thanks for the update, but i am not familiar with the GetPivotData formula aswell. Request you to help me out in figuring out a solution for my problem. I am not that good at this formulas.

 

[aparvez007]

Dear Ajay,

Hope this will work.

Regards,
Pavi

 

[ajayxlnc2]

Thanks Pavi, It is working great, but as SirJB7 suggest can this be done by using GetPivotData formula.
If there is a possibility i would prefer to use GetPivotData.
But this solution is working good.

 

[BBD]

Hi Ajay,

Here is how you use GetPivotData to get the results.

Cheers,
BD

 

[ajayxlnc2]

Thanks Pavi & BBD for helping. Both the files worked like a charm. But there is a challenge.

Actually i want to create a fixed template but the data in Group & Role changes week on week. When i generate a report for next week the group might [not] contain the same names and the Role might [not] contain the same name, so if this is the case how can i pull the dynamic data using Countif as Pavi suggested and using GetPivotData as BBD suggested. Can you help me in solving this challenge for me. I have prepared a static template and the template should be updated as in when i put the data in the data sheet....

 

[aparvez007]

Dear Ajay,

Please upload your final template where you want updated data.


Regards,
pavi

 

[dan_l]

imo, get pivot data is one of the most underrated formulas. To make this be a bit easier to manage day to day, get rid of those big merged cells on the left hand side.

 

[ajayxlnc2]

Thanks i was able to solve this problem.... Thanks for your help