I would like to be able to generate a macro that checks if 2 criteria are met. based on the combination of these 2 criteria that are met, I would like excel to return a certain record. In the excel sheet attached, under the Property Revenue & Margin cost sheet, There is a Urban fee Column. This fee is calculated based on the property type and and no. of payments. The urban fee table with the correct calculated amounts can be found under the Property Information sheet.
What I have done to calculate the urban fee, is:
1. Filter the no. of payments (ex. 1)
2. Applied this formula which worked: IF(J2=1,IF(A2="1 BR",'Property Information'!$F$16, IF(A2="2 BR  A ",'Property Information'!$F$17, IF(A2="2 BR  B ", 'Property Information'!$F$18, IF(A2="3 BR ",'Property Information'!$F$19)))))
3. Filter to 2 payments
4. Edited the above formula: IF(J2=2,IF(A4="1 BR",'Property Information'!$G$16, IF(A2="2 BR  A ",'Property Information'!$G$17, IF(A2="2 BR  B ", 'Property Information'!$G$18, IF(A2="3 BR ",'Property Information'!$G$19)))))
5. Filter to 4 payments
6. Formula: IF(J2=4,IF(A2="1 BR", 'Property Information'!$H$16, IF(A2="2 BR  A ",'Property Information'!$H$17, IF(A2="2 BR  B ", 'Property Information'!$H$18, IF(A2="3 BR ",'Property Information'!$H$19)))))
7. Filter to 12 payments
8. Formula: IF(J2=12,IF(A16="1 BR",'Property Information'!$I$16, IF(A2="2 BR  A ",'Property Information'!$I$17, IF(A2="2 BR  B ",'Property Information'!$I$18, IF(A2="3 BR ",'Property Information'!$I$19)))))
While this works, I want a macro that can return the right answer without me having to filter the No. of Payments and editing the formula
What I have done to calculate the urban fee, is:
1. Filter the no. of payments (ex. 1)
2. Applied this formula which worked: IF(J2=1,IF(A2="1 BR",'Property Information'!$F$16, IF(A2="2 BR  A ",'Property Information'!$F$17, IF(A2="2 BR  B ", 'Property Information'!$F$18, IF(A2="3 BR ",'Property Information'!$F$19)))))
3. Filter to 2 payments
4. Edited the above formula: IF(J2=2,IF(A4="1 BR",'Property Information'!$G$16, IF(A2="2 BR  A ",'Property Information'!$G$17, IF(A2="2 BR  B ", 'Property Information'!$G$18, IF(A2="3 BR ",'Property Information'!$G$19)))))
5. Filter to 4 payments
6. Formula: IF(J2=4,IF(A2="1 BR", 'Property Information'!$H$16, IF(A2="2 BR  A ",'Property Information'!$H$17, IF(A2="2 BR  B ", 'Property Information'!$H$18, IF(A2="3 BR ",'Property Information'!$H$19)))))
7. Filter to 12 payments
8. Formula: IF(J2=12,IF(A16="1 BR",'Property Information'!$I$16, IF(A2="2 BR  A ",'Property Information'!$I$17, IF(A2="2 BR  B ",'Property Information'!$I$18, IF(A2="3 BR ",'Property Information'!$I$19)))))
While this works, I want a macro that can return the right answer without me having to filter the No. of Payments and editing the formula
Attachments

153.2 KB Views: 5