Hi Experts,
I am having difficulty in creating formula with index match (first try on this formula to replace vlookup) with the dynamic range.
Attached with my sample file, hope it can be easy understanding.
My current index match formula is stay with fixed range,so if any adding rows to the two pivot table, the table result will not get updated.
I am following the step as the below link to define the name(PartUsage_Customer,Revenue_Customer) and refers to the formula
=Sheet1!$A$6:INDEX(Sheet1!$A$6:$A$30,COUNTA(Sheet1!$A$6:$A$30))
=Sheet1!$G$7:INDEX(Sheet1!$G$8:$G$30,COUNTA(Sheet1!$G$8:$G$30))
https://www.myonlinetraininghub.com/excel-dynamic-named-ranges
But i have no clue on the next action how to apply the INDEX COUNTA function into the index match formula? So that any new row inserted to any of the pivot table, the 3rd table can immediately updated the new row and perform the calculation at the same time.
Will be greatly appreciate if anyone can help. Thanks!
Regards,
Hooi Thin
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 🙂
