Hi
This formula is upto to year 25 . when I extend this to 30 the formula will be become large. Anyway of shorting this depending on the cells in b2 and e 2 the yearly or monthly data is populated in the respective cells.
=IF(OR(AND($E$2="yearly",$B$2="all"),AND($E$2="yearly",$B$2="selfi")),Data!$E$15,IF(OR(AND($E$2=2008,$B$2="all"),AND($E$2=2008,$B$2="selfi")),"",IF(OR(AND($E$2=2009,$B$2="all"),AND($E$2=2009,$B$2="selfi")),Data!$D$16,IF(OR(AND($E$2=2010,$B$2="all"),AND($E$2=2010,$B$2="selfi")),Data!$D$28,IF(OR(AND($E$2=2011,$B$2="all"),AND($E$2=2011,$B$2="selfi")),Data!$D$40,IF(OR(AND($E$2=2012,$B$2="all"),AND($E$2=2012,$B$2="selfi")),Data!$D$52,IF(OR(AND($E$2=2013,$B$2="all"),AND($E$2=2013,$B$2="selfi")),Data!$D$64,IF(OR(AND($E$2=2014,$B$2="all"),AND($E$2=2014,$B$2="selfi")),Data!$D$76,IF(OR(AND($E$2=2015,$B$2="all"),AND($E$2=2015,$B$2="selfi")),Data!$D$88,IF(OR(AND($E$2=2016,$B$2="all"),AND($E$2=2016,$B$2="selfi")),Data!$D$100,IF(OR(AND($E$2=2017,$B$2="all"),AND($E$2=2017,$B$2="selfi")),Data!$D$112,IF(OR(AND($E$2=2018,$B$2="all"),AND($E$2=2018,$B$2="selfi")),Data!$D$124,IF(OR(AND($E$2=2019,$B$2="all"),AND($E$2=2019,$B$2="selfi")),Data!$D$136,IF(OR(AND($E$2=2020,$B$2="all"),AND($E$2=2020,$B$2="selfi")),Data!$D$148,IF(OR(AND($E$2=2021,$B$2="all"),AND($E$2=2021,$B$2="selfi")),Data!$D$160,IF(OR(AND($E$2=2022,$B$2="all"),AND($E$2=2022,$B$2="selfi")),Data!$D$172,IF(OR(AND($E$2=2023,$B$2="all"),AND($E$2=2023,$B$2="selfi")),Data!$D$184,IF(OR(AND($E$2=2024,$B$2="all"),AND($E$2=2024,$B$2="selfi")),Data!$D$196,IF(OR(AND($E$2=2025,$B$2="all"),AND($E$2=2025,$B$2="selfi")),Data!$D$208,"")))))))))))))))))))
This formula is upto to year 25 . when I extend this to 30 the formula will be become large. Anyway of shorting this depending on the cells in b2 and e 2 the yearly or monthly data is populated in the respective cells.
=IF(OR(AND($E$2="yearly",$B$2="all"),AND($E$2="yearly",$B$2="selfi")),Data!$E$15,IF(OR(AND($E$2=2008,$B$2="all"),AND($E$2=2008,$B$2="selfi")),"",IF(OR(AND($E$2=2009,$B$2="all"),AND($E$2=2009,$B$2="selfi")),Data!$D$16,IF(OR(AND($E$2=2010,$B$2="all"),AND($E$2=2010,$B$2="selfi")),Data!$D$28,IF(OR(AND($E$2=2011,$B$2="all"),AND($E$2=2011,$B$2="selfi")),Data!$D$40,IF(OR(AND($E$2=2012,$B$2="all"),AND($E$2=2012,$B$2="selfi")),Data!$D$52,IF(OR(AND($E$2=2013,$B$2="all"),AND($E$2=2013,$B$2="selfi")),Data!$D$64,IF(OR(AND($E$2=2014,$B$2="all"),AND($E$2=2014,$B$2="selfi")),Data!$D$76,IF(OR(AND($E$2=2015,$B$2="all"),AND($E$2=2015,$B$2="selfi")),Data!$D$88,IF(OR(AND($E$2=2016,$B$2="all"),AND($E$2=2016,$B$2="selfi")),Data!$D$100,IF(OR(AND($E$2=2017,$B$2="all"),AND($E$2=2017,$B$2="selfi")),Data!$D$112,IF(OR(AND($E$2=2018,$B$2="all"),AND($E$2=2018,$B$2="selfi")),Data!$D$124,IF(OR(AND($E$2=2019,$B$2="all"),AND($E$2=2019,$B$2="selfi")),Data!$D$136,IF(OR(AND($E$2=2020,$B$2="all"),AND($E$2=2020,$B$2="selfi")),Data!$D$148,IF(OR(AND($E$2=2021,$B$2="all"),AND($E$2=2021,$B$2="selfi")),Data!$D$160,IF(OR(AND($E$2=2022,$B$2="all"),AND($E$2=2022,$B$2="selfi")),Data!$D$172,IF(OR(AND($E$2=2023,$B$2="all"),AND($E$2=2023,$B$2="selfi")),Data!$D$184,IF(OR(AND($E$2=2024,$B$2="all"),AND($E$2=2024,$B$2="selfi")),Data!$D$196,IF(OR(AND($E$2=2025,$B$2="all"),AND($E$2=2025,$B$2="selfi")),Data!$D$208,"")))))))))))))))))))
ALL | Select the Property | Yearly | Select a period | ||||||||||||||||||||||
25-03-20 | |||||||||||||||||||||||||
INCOME | TOTAL | 2008 | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | 2018 | 2019 | 2020 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 | 2029 | 2030 | |
Selfi | ₹ 1,350,900.38 | ₹ 0.00 | ₹ 72,600.00 | ₹ 72,600.00 | ₹ 72,600.00 | ₹ 72,600.00 | ₹ 99,150.00 | ₹ 112,050.00 | ₹ 117,652.50 | ₹ 132,697.88 | ₹ 181,500.00 | ₹ 198,000.00 | ₹ 201,300.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 | ₹ 0.00 |