shortning formula

[shamsu203]

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,"")))))))))))))))))))

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

 

[KRice]

I have some questions and some ideas that might address your needs. It appears that B2 assumes a value of either "All" or "Selfi", and nothing else...is this correct? If so, then the OR construction can probably be eliminated. It also appears that E2 assumes a value of either "yearly" or an actual year, and nothing else...is this correct?
Columns B and E have two columns between them. Your example shows "ALL" in the 1st column. Is this supposed to represent column B? And then "Yearly" is shown in the 3rd column. Is this supposed to represent column E, even though there is only one column separating the two entries?

I don't know if you're using data validation to control/facilitate inputs in B2 and E2, but I would recommend that. I've done that in this example, and inserted a blank column to position the B2 and E2 inputs that seem to correspond to your formula. B2 is a simple hardwired list done directly in the data validation window. E2 relies on a helper/lookup list, constructed somewhere else to help organize your yearly updates. That helper table is shown below.

MrExcel_20200325.xlsx
	AE	AF	AG
1		SheetRef
2	Year	Data!$D$	Reference
3	Yearly		Data!$E$15
4	2008
5	2009	16	Data!$D$16
6	2010	28	Data!$D$28
7	2011	40	Data!$D$40
8	2012	52	Data!$D$52
9	2013	64	Data!$D$64
10	2014	76	Data!$D$76
11	2015	88	Data!$D$88
12	2016	100	Data!$D$100
13	2017	112	Data!$D$112
14	2018	124	Data!$D$124
15	2019	136	Data!$D$136
16	2020	148	Data!$D$148
17	2021	160	Data!$D$160
18	2022	172	Data!$D$172
19	2023	184	Data!$D$184
20	2024	196	Data!$D$196
21	2025	208	Data!$D$208
22	2026	220	Data!$D$220
23	2027	232	Data!$D$232
24	2028	244	Data!$D$244
25	2029	256	Data!$D$256
26	2030	268	Data!$D$268
Sheet2

Cell Formulas
Range	Formula
AG5:AG26	AG5	=CONCATENATE($AF$2,AF5)

The lengthy formula you presented is repeated here with some structure to help understand its construction...incidentally, you can enter a long formula just like this in the Excel formula bar, using line breaks (Alt-Enter) to help organize the formula:

=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,
"")))))))))))))))))))

Then the lengthy formula can be replaced with this one:
=IFERROR(INDIRECT(INDEX($AG$3:$AG$26,MATCH($E$2,$AE$3:$AE$26,0))),"")

This formula matches the value shown in $E$2 to that shown in the helper/lookup table, and then it pulls the corresponding reference to your Data sheet. That reference alone can then be used within the INDIRECT function to pull the value from your Data sheet. In this example, I constructed a worksheet named data and place the text "2009 data" in cell D16 to illustrate how choosing
2009 in cell E2 results in the formula using the lookup/helper table to extract the right content from the Data worksheet.

MrExcel_20200325.xlsx
	B	C	D	E	F	G	H	I	J
1
2	All	<--Select the Property		2009	<-- Select a period
3	25-03-2020
4	INCOME	TOTAL			2008	2009	2010	2011	2012
5	Selfi	1,350,900.38			0	72,600.00	#######	#######	#######
6
7						2009 data
Sheet2

Cell Formulas
Range	Formula
G7	G7	=IFERROR(INDIRECT(INDEX($AG$3:$AG$26,MATCH($E$2,$AE$3:$AE$26,0))),"")

I'm not sure how this formula is intended to work with the years that you show in a row. The E2 cell selection returns only one value, but the table you presented appears to be aimed at pulling data for all years. Am I missing something?

Have a look and let me know...

 

[shamsu203]

Hi Tks for ur fast reply. I have not been clear in my requirements any here here are the answers to ur query For ur info the sheet is send to u is part of seven rows showing different names starting from b5 to b11 for eg b5 selfi b6 faraway and so on.to b11. B2 is a drop down list of these names . If I select all in b2 then all the items are shown otherwise the selected name is shown in the respective row (B5:b11) and the other rows are shown blank. i have a vba which hide the unpopulated rows if b2 is selected for a specific name . eg is b2 is selected for selefi thenrows 6 to 11 are hidden .
e2 is also a drop down list which has "yearly "then 2008 to 2030 . in e2 if yearly is selected then the e 4 row onwards will show the year starting from 2008 on wards to 2030 . the column from 5to 11 will show the yearly income. if e2 shows a specific year eg 2019 then depending on the selection in b2 "all monthly total staring from apr to mar is shown for all 7 items again if specific item is selected then only the income for that item is shown rest of the rows being hidden. i am including a copy of the file where e 2 is for 2019 and b2 for all. i also include a sheet showing a specific selection from b2 for the same period . sorry the column have moved to the right

ALL drop down b5=b11

Select the Property

2019

Select a period

25-03-20​

if yearly then 2008 is shown the starting year

INCOME

TOTAL

Apr-19

May-19

Jun-19

Jul-19

Aug-19

Sep-19

Oct-19

Nov-19

Dec-19

Jan-20

Feb-20

Mar-20

Selfi

₹ 201,300.00

₹ 16,500.00

₹ 16,500.00

₹ 16,500.00

₹ 16,500.00

₹ 16,500.00

₹ 16,500.00

₹ 16,500.00

₹ 16,500.00

₹ 16,500.00

₹ 16,500.00

₹ 18,150.00

₹ 18,150.00

Major

₹ 75,000.00

₹ 5,000.00

₹ 5,000.00

₹ 5,000.00

₹ 5,000.00

₹ 5,000.00

₹ 5,000.00

₹ 5,000.00

₹ 15,000.00

₹ 6,250.00

₹ 6,250.00

₹ 6,250.00

₹ 6,250.00

Global gf

₹ 123,000.00

₹ 10,000.00

₹ 10,000.00

₹ 10,000.00

₹ 10,000.00

₹ 10,000.00

₹ 10,000.00

₹ 10,000.00

₹ 10,000.00

₹ 10,000.00

₹ 11,000.00

₹ 11,000.00

₹ 11,000.00

Global 1

₹ 71,280.00

₹ 5,940.00

₹ 5,940.00

₹ 5,940.00

₹ 5,940.00

₹ 5,940.00

₹ 5,940.00

₹ 5,940.00

₹ 5,940.00

₹ 5,940.00

₹ 5,940.00

₹ 5,940.00

₹ 5,940.00

Global 2

₹ 56,857.90

₹ 4,477.00

₹ 4,477.00

₹ 4,477.00

₹ 4,477.00

₹ 4,477.00

₹ 4,924.70

₹ 4,924.70

₹ 4,924.70

₹ 4,924.70

₹ 4,924.70

₹ 4,924.70

₹ 4,924.70

Global 3

₹ 42,160.00

₹ 3,400.00

₹ 3,400.00

₹ 3,400.00

₹ 3,400.00

₹ 3,400.00

₹ 3,400.00

₹ 3,400.00

₹ 3,400.00

₹ 3,740.00

₹ 3,740.00

₹ 3,740.00

₹ 3,740.00

Capital Mall

₹ 240,000.00

₹ 20,000.00

₹ 20,000.00

₹ 20,000.00

₹ 20,000.00

₹ 20,000.00

₹ 20,000.00

₹ 20,000.00

₹ 20,000.00

₹ 20,000.00

₹ 20,000.00

₹ 20,000.00

₹ 20,000.00

Total I SELFI Select the Property 2019 Select a period 25-03-20​ INCOME TOTAL Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 selfi ₹ 201,300.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 18,150.00 ₹ 18,150.00 ₹ 0.00 Total Income ₹ 201,300.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 16,500.00 ₹ 18,150.00 ₹ 18,150.00 ₹ 0.00 ₹ 0.00 ₹ 0.00 ₹ 0.00 ₹ 0.00 ₹ 0.00 ₹ 0.00 ₹ 0.00 ₹ 0.00 ₹ 0.00 ₹ 0.00 ncome

₹ 809,597.90

₹ 65,317.00

₹ 65,317.00

₹ 65,317.00

₹ 65,317.00

₹ 65,317.00

₹ 65,764.70

₹ 65,764.70

₹ 75,764.70

₹ 67,354.70

₹ 68,354.70

₹ 70,004.70

₹ 70,004.70

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

 

[KRice]

Thanks for the clarification. Some of my assumptions were not correct. When "yearly" is selected in E2, and the range F4:AB4 displays the years, are they calendar years or fiscal years? I'm confused by this because when a year is selected in E2, you show Apr of that year through Mar of the following year...I'm assuming that is due to the definition of your fiscal year, but could you please clarify that...when years are selected, do you want Jan-Dec or Apr-Mar?

And does your Data worksheet resemble this one? I'm attempting to revise your complex formula into a two-way lookup-type formula that will find the relevant values for the year, or month-year combination, and the associated name. This is what led to my question about the year. Further detail about the Data worksheet structure would be helpful.

MrExcel_20200325.xlsx
	A	B	C	D	E	F	G	H	I	J
2				Selfi	Major	Global gf	Global 1	Global 2	Global 3	Capital Mall
3	Begin FY	Cal Year	Month
4	2008	2008	Apr
5		2008	May
6		2008	Jun
7		2008	Jul
8		2008	Aug
9		2008	Sep
10		2008	Oct
11		2008	Nov
12		2008	Dec
13		2009	Jan
14		2009	Feb
15		2009	Mar
16	2009	2009	Apr	1500
17		2009	May	2000
18		2009	Jun	2500
19		2009	Jul	3000
20		2009	Aug	3500
21		2009	Sep	4000
22		2009	Oct	4500
23		2009	Nov	5000
24		2009	Dec	5500
25		2010	Jan	6000
26		2010	Feb	6500
27		2010	Mar	7000
28	2010	2010	Apr	1750
29		2010	May	2250
30		2010	Jun	2750
31		2010	Jul	3250
32		2010	Aug	3750
33		2010	Sep	4250
34		2010	Oct	4750
35		2010	Nov	5250
36		2010	Dec	5750
37		2011	Jan	6250
38		2011	Feb	6750
39		2011	Mar	7250
40	2011	2011	Apr
41		2011	May
42		2011	Jun
Data

Cell Formulas
Range	Formula
B13:B42	B13	=IF(MOD(ROW()-ROW($B$13),12)=0,B12+1,B12)

 

[shamsu203]

Hi the dates shown are the fiscal year which starts from apr . I am attaching a copy of the data sheet for ur information .the spreadsheet the data sheet includes all the properties shown i the earlier sheets and automatically calculates the rent rent increase on the specified period the formula used.=IF(B136>$B$5,0,IF(A136>=$D$6,IF(MOD(A136-$D$6,$D$7)=0,FV($D$5,INT((A136-$D$6)/$D$7)+1,0,-C136),FV($D$5,INT((A136-$D$6)/$D$7)+1,0,-C136)),C136)). if the tenant detail changes then the rental rate lease start date first payment date % and increase at period is changed the formula up to the new date is changed by copy and paste function.

Menu

Lease Term

96​

Lease Term

96​

Lease Term

96​

Lease Term

96​

Lease Term

96​

Lease Term

96​

96​

Rental Rate

₹ 16,500.00​

Rental Rate

₹ 5,000.00​

Rental Rate

₹ 10,000.00​

Rental Rate

₹ 5,400.00​

Rental Rate

₹ 4,070.00​

Rental Rate

₹ 3,400.00​

₹ 20,000.00​

Lease Date

15-02-19​

Lease Date

01-12-15​

Lease Date

01-01-17​

Lease Date

01-01-16​

Lease Date

01-09-13​

Lease Date

01-12-16​

01-07-12​

First Payment Date

15-02-19​

First Pay Date

01-12-15​

First Pay Date

01-01-17​

First Pay Date

01-01-16​

First Pay Date

01-09-13​

First Pay Date

01-12-16​

01-07-12​

Current Date

26-03-20​

Rent Increase %

10.00%​

Rent Increase %

25.00%​

Rent Increase %

10.00%​

Rent Increase %

10.00%​

Rent Increase %

10.00%​

Rent Increase %

10.00%​

0.00%​

Increase @ Period

134​

Increase @ Period

132​

Increase @ Period

133​

Increase @ Period

121​

Increase @ Period

93​

Increase @ Period

132​

250​

Lease Term (no months

12​

SELFI

Increase Interval

48​

MAJOR

Increase Interval

36​

GLOBAL GF

Increase Interval

36​

GLOBAL 1

Increase Interval

36​

GLOBAL 2

Increase Interval

36​

GLOBAL 3

1​

Capital

Increase Due

15-02-20​

01-12-22​

01-01-23​

01-01-22​

01-09-22​

01-12-22​

00-01-00​

New Rent

₹ 19,965.00​

₹ 7,812.50​

₹ 12,100.00​

₹ 6,534.00​

₹ 5,417.17​

₹ 4,114.00​

₹ 20,000.00​

Period No

Month

Rent

Rent Increase

Rent

Rent Increase

Rent

Rent Increase

Rent

Rent Increase

Rent

Rent Increase

Rent

Rent Increase

Rent Increase

01-01-09​

Montly Rate

Montly Rate

Montly Rate

Montly Rate

Montly Rate

Montly Rate

Montly Rate

01-01-09​

₹ 0.00

1​

01-01-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

2​

01-02-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

3​

01-03-09​

₹ 6,050.00

₹ 18,150.00​

₹ 1,500.00

₹ 4,500.00​

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

4​

01-04-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

5​

01-05-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

6​

01-06-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

7​

01-07-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

8​

01-08-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

9​

01-09-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

10​

01-10-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

11​

01-11-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

12​

01-12-09​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

₹ 0.00

13​

01-01-10​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 4,700.00

₹ 0.00

₹ 0.00

₹ 0.00

14​

01-02-10​

₹ 6,050.00

₹ 1,500.00

₹ 0.00

₹ 4,700.00

₹ 0.00

₹ 0.00

₹ 0.00

15​

01-03-10​

₹ 6,050.00

₹ 72,600.00​

₹ 1,500.00

₹ 18,000.00​

₹ 0.00

₹ 0.00​

₹ 4,700.00

₹ 14,100.00​

₹ 0.00

₹ 0.00​

₹ 0.00

₹ 0.00​

₹ 0.00

₹ 0.00​

16​

01-04-10​

₹ 6,050.00

₹ 1,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

17​

01-05-10​

₹ 6,050.00

₹ 1,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

18​

01-06-10​

₹ 6,050.00

₹ 1,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

19​

01-07-10​

₹ 6,050.00

₹ 1,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

20​

01-08-10​

₹ 6,050.00

₹ 1,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

21​

01-09-10​

₹ 6,050.00

₹ 1,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

22​

01-10-10​

₹ 6,050.00

₹ 1,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

23​

01-11-10​

₹ 6,050.00

₹ 1,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

24​

01-12-10​

₹ 6,050.00

₹ 1,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

₹ 0.00​

25​

01-01-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

26​

01-02-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

27​

01-03-11​

₹ 6,050.00

₹ 72,600.00​

₹ 2,500.00

₹ 21,000.00​

₹ 6,200.00

₹ 74,400.00​

₹ 4,700.00

₹ 56,400.00​

₹ 3,700.00

₹ 44,400.00​

₹ 2,850.00

₹ 34,200.00​

₹ 0.00

₹ 0.00​

28​

01-04-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

29​

01-05-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

30​

01-06-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

31​

01-07-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

32​

01-08-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

33​

01-09-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

34​

01-10-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

35​

01-11-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

36​

01-12-11​

₹ 6,050.00

₹ 2,500.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

₹ 0.00​

37​

01-01-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

38​

01-02-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

39​

01-03-12​

₹ 6,050.00

₹ 72,600.00​

₹ 3,750.00

₹ 33,750.00​

₹ 6,200.00

₹ 74,400.00​

₹ 4,700.00

₹ 56,400.00​

₹ 3,700.00

₹ 44,400.00​

₹ 2,850.00

₹ 34,200.00​

₹ 0.00

₹ 0.00​

40​

01-04-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

41​

01-05-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

42​

01-06-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 0.00

43​

01-07-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

44​

01-08-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

45​

01-09-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

46​

01-10-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

47​

01-11-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

48​

01-12-12​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 4,700.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

49​

01-01-13​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 5,000.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

50​

01-02-13​

₹ 6,050.00

₹ 3,750.00

₹ 6,200.00

₹ 5,000.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

51​

01-03-13​

₹ 6,050.00

₹ 72,600.00​

₹ 3,750.00

₹ 45,000.00​

₹ 6,200.00

₹ 74,400.00​

₹ 5,000.00

₹ 57,300.00​

₹ 3,700.00

₹ 44,400.00​

₹ 2,850.00

₹ 34,200.00​

₹ 20,000.00

₹ 180,000.00​

52​

01-04-13​

₹ 6,050.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

53​

01-05-13​

₹ 6,050.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

54​

01-06-13​

₹ 6,050.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

55​

01-07-13​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

56​

01-08-13​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 3,700.00

₹ 2,850.00

₹ 20,000.00

57​

01-09-13​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 2,850.00

₹ 20,000.00

58​

01-10-13​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 2,850.00

₹ 20,000.00

59​

01-11-13​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 2,850.00

₹ 20,000.00

60​

01-12-13​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 2,850.00

₹ 20,000.00

61​

01-01-14​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

62​

01-02-14​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

63​

01-03-14​

₹ 9,000.00

₹ 99,150.00​

₹ 3,750.00

₹ 45,000.00​

₹ 6,820.00

₹ 81,840.00​

₹ 5,000.00

₹ 60,000.00​

₹ 4,070.00

₹ 46,990.00​

₹ 3,135.00

₹ 35,055.00​

₹ 20,000.00

₹ 240,000.00​

64​

01-04-14​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

65​

01-05-14​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

66​

01-06-14​

₹ 9,000.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

67​

01-07-14​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

68​

01-08-14​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

69​

01-09-14​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

70​

01-10-14​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

71​

01-11-14​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

72​

01-12-14​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

73​

01-01-15​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

74​

01-02-15​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

75​

01-03-15​

₹ 9,450.00

₹ 112,050.00​

₹ 3,750.00

₹ 45,000.00​

₹ 6,820.00

₹ 81,840.00​

₹ 5,000.00

₹ 60,000.00​

₹ 4,070.00

₹ 48,840.00​

₹ 3,135.00

₹ 37,620.00​

₹ 20,000.00

₹ 240,000.00​

76​

01-04-15​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

77​

01-05-15​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

78​

01-06-15​

₹ 9,450.00

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

79​

01-07-15​

₹ 9,922.50

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

80​

01-08-15​

₹ 9,922.50

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

81​

01-09-15​

₹ 9,922.50

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

82​

01-10-15​

₹ 9,922.50

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

83​

01-11-15​

₹ 9,922.50

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

84​

01-12-15​

₹ 9,922.50

₹ 3,750.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

85​

01-01-16​

₹ 9,922.50

₹ 5,000.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

86​

01-02-16​

₹ 9,922.50

₹ 5,000.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

87​

01-03-16​

₹ 9,922.50

₹ 117,652.50​

₹ 5,000.00

₹ 48,750.00​

₹ 6,820.00

₹ 81,840.00​

₹ 5,000.00

₹ 60,000.00​

₹ 4,070.00

₹ 48,840.00​

₹ 3,135.00

₹ 37,620.00​

₹ 20,000.00

₹ 240,000.00​

88​

01-04-16​

₹ 9,922.50

₹ 5,000.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

89​

01-05-16​

₹ 9,922.50

₹ 5,000.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

90​

01-06-16​

₹ 9,922.50

₹ 5,000.00

₹ 6,820.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

91​

01-07-16​

₹ 10,418.63

₹ 5,000.00

₹ 7,200.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

92​

01-08-16​

₹ 10,418.63

₹ 5,000.00

₹ 7,200.00

₹ 5,000.00

₹ 4,070.00

₹ 3,135.00

₹ 20,000.00

93​

01-09-16​

₹ 10,418.63

₹ 5,000.00

₹ 7,200.00

₹ 5,000.00

₹ 4,477.00

₹ 3,135.00

₹ 20,000.00

94​

01-10-16​

₹ 10,418.63

₹ 5,000.00

₹ 7,200.00

₹ 5,000.00

₹ 4,477.00

₹ 3,135.00

₹ 20,000.00

95​

01-11-16​

₹ 10,418.63

₹ 5,000.00

₹ 7,200.00

₹ 5,000.00

₹ 4,477.00

₹ 3,135.00

₹ 20,000.00

96​

01-12-16​

₹ 10,418.63

₹ 5,000.00

₹ 10,000.00

₹ 5,000.00

₹ 4,477.00

₹ 3,135.00

₹ 20,000.00

97​

01-01-17​

₹ 10,418.63

₹ 5,000.00

₹ 10,000.00

₹ 5,000.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

98​

01-02-17​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,000.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

99​

01-03-17​

₹ 15,000.00

₹ 132,697.88​

₹ 5,000.00

₹ 60,000.00​

₹ 10,000.00

₹ 96,460.00​

₹ 5,000.00

₹ 60,000.00​

₹ 4,477.00

₹ 51,689.00​

₹ 3,400.00

₹ 38,415.00​

₹ 20,000.00

₹ 240,000.00​

100​

01-04-17​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,000.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

101​

01-05-17​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,000.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

102​

01-06-17​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,000.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

103​

01-07-17​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

104​

01-08-17​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

105​

01-09-17​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

106​

01-10-17​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

107​

01-11-17​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

108​

01-12-17​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

109​

01-01-18​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

110​

01-02-18​

₹ 15,000.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

111​

01-03-18​

₹ 16,500.00

₹ 181,500.00​

₹ 5,000.00

₹ 60,000.00​

₹ 10,000.00

₹ 120,000.00​

₹ 5,400.00

₹ 63,600.00​

₹ 4,477.00

₹ 53,724.00​

₹ 3,400.00

₹ 40,800.00​

₹ 20,000.00

₹ 240,000.00​

112​

01-04-18​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

113​

01-05-18​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

114​

01-06-18​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

115​

01-07-18​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

116​

01-08-18​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

117​

01-09-18​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

118​

01-10-18​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

119​

01-11-18​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

120​

01-12-18​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,400.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

121​

01-01-19​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,940.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

122​

01-02-19​

₹ 0.00​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,940.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

123​

01-03-19​

₹ 16,500.00​

₹ 16,500.00

₹ 198,000.00​

₹ 5,000.00

₹ 60,000.00​

₹ 10,000.00

₹ 120,000.00​

₹ 5,940.00

₹ 66,420.00​

₹ 4,477.00

₹ 53,724.00​

₹ 3,400.00

₹ 40,800.00​

₹ 20,000.00

₹ 240,000.00​

124​

01-04-19​

₹ 16,500.00​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,940.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

125​

01-05-19​

₹ 16,500.00​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,940.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

126​

01-06-19​

₹ 16,500.00​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,940.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

127​

01-07-19​

₹ 16,500.00​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,940.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

128​

01-08-19​

₹ 16,500.00​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,940.00

₹ 4,477.00

₹ 3,400.00

₹ 20,000.00

129​

01-09-19​

₹ 16,500.00​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,940.00

₹ 4,924.70

₹ 3,400.00

₹ 20,000.00

130​

01-10-19​

₹ 16,500.00​

₹ 16,500.00

₹ 5,000.00

₹ 10,000.00

₹ 5,940.00

₹ 4,924.70

₹ 3,400.00

₹ 20,000.00

131​

01-11-19​

₹ 16,500.00​

₹ 16,500.00

₹ 15,000.00

₹ 10,000.00

₹ 5,940.00

₹ 4,924.70

₹ 3,400.00

₹ 20,000.00

132​

01-12-19​

₹ 16,500.00​

₹ 16,500.00

₹ 6,250.00

₹ 10,000.00

₹ 5,940.00

₹ 4,924.70

₹ 3,740.00

₹ 20,000.00

133​

01-01-20​

₹ 16,500.00​

₹ 16,500.00

₹ 6,250.00

₹ 11,000.00

₹ 5,940.00

₹ 4,924.70

₹ 3,740.00

₹ 20,000.00

134​

01-02-20​

₹ 16,500.00​

₹ 18,150.00

₹ 6,250.00

₹ 11,000.00

₹ 5,940.00

₹ 4,924.70

₹ 3,740.00

₹ 20,000.00

135​

01-03-20​

₹ 16,500.00​

₹ 18,150.00

₹ 201,300.00​

₹ 6,250.00

₹ 75,000.00​

₹ 11,000.00

₹ 123,000.00​

₹ 5,940.00

₹ 71,280.00​

₹ 4,924.70

₹ 56,857.90​

₹ 3,740.00

₹ 42,160.00​

₹ 20,000.00

₹ 240,000.00​

 

[KRice]

To eliminate the lengthy nested IF statement with the embeded ANDs and ORs that was the subject of your question, I propose a different approach that relies on creating a summary table whose Names at left and whose column headings above can be used to find the relevant information in the data table using a single, simpler formula that does not require any year-to-year updating. I'll explain the details:

A helper table is constructed somewhere to the right of the summary table. This isn't absolutely necessary (and it can be hidden if desired), but it is an easy way to construct the column headings: one set of column headings (i.e, the "Row for Yearly" column in the helper table) is used to display sequential years when "Yearly" is selected in the E2 drop down list, the other set of column headings (i.e, the "Row for Monthly" column in the helper table) is used to display sequential months in the fiscal year when a specific fiscal year is selected in the E2 drop down list. The column headings are inserted with a formula in F4:AB4...that formula relies on the TRANSPOSE function, as well as an IF statement to determine which of the two column headings to insert. When months are shown, the summary table extends only to column Q, so I padded the remainder of the column headings (R4:AB4) with a nonprinting character (CHAR(129)) to clean up the appearance. The portion of the helper/lookup table goes blank if "yearly" is selected.

Based on which column heading is inserted, we would like to identify the place in the Data table corresponding to the column heading (either a specific month when Month-Year is shown, or the beginning of the fiscal year when only the Year is shown). To do that, I added a helper row in small grey font above the columns in F3:AB3, and the formula used in that helper row takes advantage of the fact that you have a Period Number column in your Data table (column A), and cell Data!:$A$13 represents the upper left corner of the income data table, which corresponds to period 1 beginning on 1 January 2009. Using that information, the helper row determines the number of months after 1 Jan 2009 until the payment period of interest for that particular column. This helper row can be hidden, the font lightened, made smaller, changed in color, but it should not be deleted, as the summary table lookups depend on these numbers.

B2 uses Data Validation and relies on the column "Names" in the helper/lookup area. Formulas in B5:B11 then examine the content of B2 and either copy the name from the helper/lookup area or display a blank, depending on whether "All" or a name has been selected from the B2 dropdown list. If this works for you, the VBA code performing this hiding function isn't necessary, as the formula does that.

Your data table has a consistent structure, and we know where to find the names (Data!$A$7:$V$7) and the payment period numbers (Data!$A$13:$A$276)---the last point corresponding to payment period 264 on 1 December 2030---and we've already identified the payment period number of interest for each column in the summary table, so we can use a single formula to obtain the relevant information from the data table:

=IFERROR(SUM(OFFSET(Data!$A$13,F$3-1,MATCH($B5,Data!$A$7:$V$7,0)-2,IF($E$2="YEARLY",12,1),1)),"")

This formula uses the OFFSET function, essentailly using the upper left corner of your data table as a common reference point, and then we determine how many columns to move to the right to find the monthly data corresponding to the name (found using the MATCH function). We know how many rows to move down from the reference point (given by the helper row F3:AB3). You will find an IF statement in this formula, and it is used to either select 1 cell or 12 cells starting at the prescribed location to feed into the SUM function, because we want either a single month or an entire fiscal year reported back to the SUM function. Finally, the entire formula is wrapped in an IFERROR function to display a blank if an error is encounted, such as would occur if a match is not found in a given cell.

This single formula is copied throughout the table, and once it is confirmed to pick up the correct values from the data table, it should require no further maintenance until you need to adjust the range limts after year 2030, or if you add other names to the data table (in which case, the helper table and other formulas would need some light attention to ensure that the expanded ranges were accounted for).

I noticed a couple of things while confirming functionality: 1) check payment period 131 for Selfi where that payment stands out because it is much different than others before and after it...I didn't know if that was a typo; and 2) check the sum of fiscal year 2016 for Selfi...in your first post, it shows a value ending in 88 while the sum I show here ends in 91...a minor difference, but the approach I describe here computes the sum for 12 months rather than pulling a pre-summed number from the data table.

Click on the clipboard icon in the XL2BB spreadsheet rendering here to copy this to your clipboard for easy pasting into Excel to try it out. The icon is found at the upper left of the sheet at the intersection of column and row headings.

MrExcel_20200325.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1		Income Summary																												Helper Table:
2		Selfi	<-Select Property		Yearly	<- Select FY																								Col for	Row for	Row for
3	Current Date	2020-03-26	Period Ref# ->				4	16	28	40	52	64	76	88	100	112	124	136	148	160	172	184	196	208	220	232	244	256		Names	Yearly	Monthly
4			Row Total			2008	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021	2022	2023	2024	2025	2026	2027	2028	2029	2030		All	2008
5	Name 1 ->	Selfi	₹ 1,332,750.41				₹ 72,600.00	₹ 72,600.00	₹ 72,600.00	₹ 72,600.00	₹ 99,150.00	₹ 112,050.00	₹ 117,652.50	₹ 132,697.91	₹ 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		Selfi	2009
6	Name 2 ->																													Major	2010
7	Name 3 ->																													Global GF	2011
8	Name 4 ->																													Global 1	2012
9	Name 5 ->																													Global 2	2013
10	Name 6 ->																													Global 3	2014
11	Name 7 ->																													Capital	2015
12		Total Income	₹ 1,332,750.41				₹ 72,600.00	₹ 72,600.00	₹ 72,600.00	₹ 72,600.00	₹ 99,150.00	₹ 112,050.00	₹ 117,652.50	₹ 132,697.91	₹ 181,500.00	₹ 198,000.00	₹ 201,300.00														2016
13																															2017
14																															2018
15																															2019
16																															2020
17																															2021
18																															2022
19																															2023
20																															2024
21																															2025
22																															2026
23																															2027
24																															2028
25																															2029
26																															2030
Sheet2

Cell Formulas
Range	Formula
B3	B3	=TODAY()
F3:AB3	F3	=IFERROR(DATEDIF(DATE(2009,1,1),IF(ISNUMBER(F4),DATE(F4,4,1),DATE(YEAR(F4),MONTH(F4),1)),"m")+1,"")
F4:AB4	F4	=IF($E$2="Yearly",TRANSPOSE(AE4:AE26),TRANSPOSE(AF4:AF26))
F5:AB11	F5	=IFERROR(SUM(OFFSET(Data!$A$13,F$3-1,MATCH($B5,Data!$A$7:$V$7,0)-2,IF($E$2="YEARLY",12,1),1)),"")
F12:AB12	F12	=IF(SUM(F5:F11)<>0,SUM(F5:F11),"")
B5:B11	B5	=IF(OR($B$2="all",$B$2=AD5),AD5,"")
C5:C12	C5	=IF(SUM(F5:AB5)<>0,SUM(F5:AB5),"")
AF4	AF4	=IFERROR(TEXT(DATE($E$2,4,15),"mmm-yyyy"),"")
AF5	AF5	=IFERROR(TEXT(DATE($E$2,5,15),"mmm-yyyy"),"")
AF6	AF6	=IFERROR(TEXT(DATE($E$2,6,15),"mmm-yyyy"),"")
AF7	AF7	=IFERROR(TEXT(DATE($E$2,7,15),"mmm-yyyy"),"")
AF8	AF8	=IFERROR(TEXT(DATE($E$2,8,15),"mmm-yyyy"),"")
AF9	AF9	=IFERROR(TEXT(DATE($E$2,9,15),"mmm-yyyy"),"")
AF10	AF10	=IFERROR(TEXT(DATE($E$2,10,15),"mmm-yyyy"),"")
AF11	AF11	=IFERROR(TEXT(DATE($E$2,11,15),"mmm-yyyy"),"")
AF12	AF12	=IFERROR(TEXT(DATE($E$2,12,15),"mmm-yyyy"),"")
AF13	AF13	=IFERROR(TEXT(DATE($E$2+1,1,15),"mmm-yyyy"),"")
AF14	AF14	=IFERROR(TEXT(DATE($E$2+1,2,15),"mmm-yyyy"),"")
AF15	AF15	=IFERROR(TEXT(DATE($E$2+1,3,15),"mmm-yyyy"),"")
AF16:AF26	AF16	=CHAR(129)
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[shamsu203]

Hi Wowl", great it works fine but for the following
1) B2 ALL E2 yearly the following not shown in 2008 Selfi Major (simlilary if u select slefi or major alone
2) b2 all e 2 yearly Capital not shown not yrarly wise nor for each year.
u asked to use the array function is it necessary and how it benefits I have not used array function up to now.
Once again very much appreciated It has been a great learning exp for me tks once again looking forward to ur reply
bi

 

[shamsu203]

the error u pointed out are typo errors

 

[KRice]

Good...almost there.
For #1, I see that B2=All, E2=2008 shows income details for periods 1, 2, and 3 (Jan-Mar 2009) at the end of fiscal year 2008 for both Selfi and Major...so that part is working correctly. And 0's are shown for the other names, consistent with the Data table. However, as you point out, upon making a change so that E2=Yearly, the 2008 information is missing for both Selfi and Major. And now that I look into it, I see why. The helper row that has been placed above the column headers is blank for year 2008, so the OFFSET formula that uses the helper row as a coordinate for finding information in the Data table gives an error (because no data table row coordinate is being provided). This is because the helper row was constructed to find the beginning of the fiscal year, and the beginning of FY2008 is not shown in the Data table. Rather than change the approach here completely, I made a few changes to the main formula in the summary table. This revised formula is placed in F5 and then copied throughout the body of the table:
=IFERROR(IF(AND($E$2<>"Yearly",F$3=""),"",SUM(OFFSET(Data!$A$13,IF(F$3="",0,F$3-1),MATCH($B5,Data!$A$7:$V$7,0)-2,IF($E$2="Yearly",IF(F$3="",G$3-1,12),1),1))),"")

Within this formula, you will see:
IF(AND($E$2<>"Yearly",F$3=""),""
This part checks if the helper row entry is blank and a specific year was selected in E2. When this condition is TRUE, that means a specific month-year period does not exist in the Data table, so we insert a blank ("") for the result. When the condition is FALSE, then that means there are three potential scenarios---either F3 has a value (and E2 is either Yearly or a specific year), or F3 is blank and E2 is Yearly, as shown here:
a. E2=Yearly , F3 is blank
b. E2=Yearly , F3 contains some value
c. E2=some year, F3 contains some value

We move to the second part of the formula to address these remaining scenarios. The last version of the formula adequately addressed scenarios "b" and "c", those scenarios where F3 held a value. But to address scenario "a" a few adjustments to the last version of the formula are necessary. The row argument in OFFSET function is now:
IF(F$3="",0,F$3-1)
This checks to see if the helper row has a blank, which will occur if there is no Period Number entry in the Data table that corresponds to the beginning of the fiscal year. This is what caused the problem you identified. If F3 has a value, we use the row argument previously specified, but if F3 is blank, we set the row argument to 0...at the top of the data table.

Then we need to ensure that the correct number of rows are being selected. In the earlier formula, the expression
IF($E$2="YEARLY",12,1)
returned either 12 rows or 1 row, depending on whether E2=Yearly. This needs some revision to account for the situation where we need less than 12 months at the top of the data table. So some nested IF statements can be used:
IF($E$2="Yearly",IF(F$3="",G$3-1,12),1)
and this sets the number of rows to return as 1 less than the helper row value in the column to the right. This will pick up the incomplete fiscal years at the top of the Data table.

For #2, check your Data sheet and confirm that the name cell is "Capital" and then check the helper table and confirm that the list also includes "Capital". In an earlier post, you showed "Capital Mall" in the summary table. When I put together the summary table, I noticed this and changed the helper table entry to "Capital". Pick whichever you prefer, but be sure that the helper table entries and the names in the Data table match exactly.

Your question about the array formula has two answers:
1) If the TRANSPOSE functions are used (they are in the current implementation), then, yes, an array formula is required because TRANSPOSE depends on that.
2) There are other ways to construct the column headings without using TRANSPOSE, and some of those options would not require an array formula. For example the listed items and formulas in the two columns of the helper table could be incorporated in formulas running across the column header row of the summary table, and an IF statement could specify which to display...year or month-year.

I'm including only a portion of the summary table because only one formula has changed. Give this a try and let me know if it helps...

MrExcel_20200325.xlsx
	A	B	C	D	E	F	G
1		Income Summary
2		All	<-Select Property		Yearly	<- Select FY
3	Current Date	2020-03-27	Period Ref# ->				4
4			Row Total			2008	2009
5	Name 1 ->	Selfi	₹ 1,350,900.41			₹ 18,150.00	₹ 72,600.00
6	Name 2 ->	Major	₹ 516,000.00			₹ 4,500.00	₹ 18,000.00
7	Name 3 ->	Global GF	₹ 928,180.00			₹ 0.00	₹ 0.00
8	Name 4 ->	Global 1	₹ 625,500.00			₹ 0.00	₹ 14,100.00
9	Name 5 ->	Global 2	₹ 493,864.90			₹ 0.00	₹ 0.00
10	Name 6 ->	Global 3	₹ 375,070.00			₹ 0.00	₹ 0.00
11	Name 7 ->	Capital	₹ 1,860,000.00			₹ 0.00	₹ 0.00
12		Total Income	₹ 6,149,515.31			₹ 22,650.00	₹ 104,700.00
Sheet2

Cell Formulas
Range	Formula
B3	B3	=TODAY()
F3:G3	F3	=IFERROR(DATEDIF(DATE(2009,1,1),IF(ISNUMBER(F4),DATE(F4,4,1),DATE(YEAR(F4),MONTH(F4),1)),"m")+1,"")
F4:G4	F4	=IF($E$2="Yearly",TRANSPOSE(AE4:AE26),TRANSPOSE(AF4:AF26))
F5:G11	F5	=IFERROR(IF(AND($E$2<>"Yearly",F$3=""),"",SUM(OFFSET(Data!$A$13,IF(F$3="",0,F$3-1),MATCH($B5,Data!$A$7:$V$7,0)-2,IF($E$2="Yearly",IF(F$3="",G$3-1,12),1),1))),"")
F12:G12	F12	=IF(SUM(F5:F11)<>0,SUM(F5:F11),"")
B5:B11	B5	=IF(OR($B$2="all",$B$2=AD5),AD5,"")
C5:C12	C5	=IF(SUM(F5:AB5)<>0,SUM(F5:AB5),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[shamsu203]

Hi
I changed as suggested it works fine except for the Capital wihch same both in Data and Summary worksheet. I would like to avoid the array functions as I have no knowledge of it.
tks once again
shamsu