Hi Hooi Thin

Normally dynamic range name is used for data table and not Pivot Table.

In your situation I would prefer covering a range that is more than the maximum size of the Pivot Table (maximum rows) in my formulas.

Example =IFERROR(INDEX(B$8:B$100,MATCH($L5,$A$8:$A$100,0))/INDEX(H$8:H$100,MATCH($L5,$G$8:$G$100,0)),"")

I have covered up till row 100 (more than sufficient for your Pivot Table)

Please refer to the range in yellow in the attachment.

Hope this is what you are looking for.

Hi Sunny,

Thanks for your replied.

I think I am mistakenly using wrong formula(index match), because my pivot table once filtered, then the row and column position changes, and i will not get the correct calculation data ( Part Usage table divided Revenue table shown on attachment)

Before i have also one case on other sheet, i was using GETPIVOTDATA to pick up the correct position for calculation.But that case is after filter, position column changes but row position was fixed (eg: I have fixed 4 rows label with the name- AXO,API,ICT,LBT)

For example:    =IFERROR(GETPIVOTDATA("USD Amount",$A$64,"Fiscal Quarter","2015 Q 1","Platform","ICT")/GETPIVOTDATA("USD Amount",$A$41,"Fiscal Quarter","2015 Q 1","Platform","ICT"),"")

But for my current case is my row position was unknown, the items might be keep increasing or change, so i can't put "ICT" a fixed label or cell reference right?

How do i correct the formula so that it can capture the correct row and column label and to divided the correct row and column label on 2nd pivot table?

Appreciate your helps again! 

Please refer to the 2nd attachment (reporting-tool-automation-2-1.xlsm) and ignore the 1st one, Thanks!

Hi Hooi Thin

Can you consider using SUMIFS with multiple criteria instead of Pivot Tables?

What are the fields you are filtering?

My attachment contains 2 criteria but can add more if necessary.

Hi Sunny,

The field i want to filter is Activity Billing Type on Part Usage Table while Category field on Revenue table.

I have to use pivot table because users might want to filter the different criteria on each table.On my real project have more than 8 criteria per table.

The desired table result i want to show on percentage value as shown in attachment.The top customer based on whatever shown on Part Usage table.

If I filter agreement on part usage table,there are no 2016 Q 2 column information.So the result table also will not have the 2016 Q 2 data.

I want the the flexibility to grab the customer and the quarter correctly match with both the table then only to perform the division calculation(shown the same colour cells between the two table),i guess can't locate cell reference? as the rows and columns will run.

Previously i was using GETPIVOTDATA, but for this case i am not sure how to apply correctly.

Can you help again?

Hi Hooi Thin

The best I can think of is using formulas to read your filters and extract using SUMIFS.

Sorry, I can't think of another way to solve your issue, especially when you have 8 criteria.

Maybe someone else here could offer you a solution.

Sunny

Hi Sunny,

Its ok. Thanks for your effort trying to help me. Appreciate 🙂