DavidLuhut
New Member
Hello Excel Experts,
I have a pivot table and i am trying to pull cell values based on multiple criteria, and the data i am trying to pull is from a pivot table.
The criterias are:
A Exchange Rate Center - Exact Match Type
Z Exchange Rate Center - Exact Match Type
Effective MAF Date - CLOSEST Match (more explanation below)
Exchange Rate Center are interexchangeable!!! So if the Pivot Table has i.e. Canada in 'A Exchange Rate Center' and USA in 'Z Exchange Rate Center', it's STILL THE SAME even if the search for 'A Exchange Rate Center' is USA and 'Z Exchange Rate Center' is Canada
The 'Effective MAF Date' is where we had an agreement. So it can be any random date. But the rule is, assuming for a particular Route (Exchange Rate centre from A to Z) has an agreement say in Jan 1, 2010 and in the future, the same Route has an ammendment say in Mar 15, 2013. If a service was required say Aug 8, 2011, the desired result/outcome of MAF and mileage will be taken as per Jan 1, 2010 agreement NOT Mar 15, 2013.
Given the same agreement dates (Jan 1, 2010 and Mar 15, 2013), if the required service date was AT and/or AFTER Mar 15, 2013, then I would need to have the MAF and mileage that was per Mar 15, 2013 agreement. If it's BEFORE Jan 1, 2010, I would want it to be 'UA'.
I hope my explanation is clear enough, if not, the attached file will give you a better explanation (I have 3 scenarios).
Thank you in advanced for your help!
I have a pivot table and i am trying to pull cell values based on multiple criteria, and the data i am trying to pull is from a pivot table.
The criterias are:
A Exchange Rate Center - Exact Match Type
Z Exchange Rate Center - Exact Match Type
Effective MAF Date - CLOSEST Match (more explanation below)
Exchange Rate Center are interexchangeable!!! So if the Pivot Table has i.e. Canada in 'A Exchange Rate Center' and USA in 'Z Exchange Rate Center', it's STILL THE SAME even if the search for 'A Exchange Rate Center' is USA and 'Z Exchange Rate Center' is Canada
The 'Effective MAF Date' is where we had an agreement. So it can be any random date. But the rule is, assuming for a particular Route (Exchange Rate centre from A to Z) has an agreement say in Jan 1, 2010 and in the future, the same Route has an ammendment say in Mar 15, 2013. If a service was required say Aug 8, 2011, the desired result/outcome of MAF and mileage will be taken as per Jan 1, 2010 agreement NOT Mar 15, 2013.
Given the same agreement dates (Jan 1, 2010 and Mar 15, 2013), if the required service date was AT and/or AFTER Mar 15, 2013, then I would need to have the MAF and mileage that was per Mar 15, 2013 agreement. If it's BEFORE Jan 1, 2010, I would want it to be 'UA'.
I hope my explanation is clear enough, if not, the attached file will give you a better explanation (I have 3 scenarios).
Thank you in advanced for your help!
Attachments
Last edited: