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]

Hi Shamsu,
Can you confirm that the left side of your data table looks like this:

MrExcel_20200325.xlsx
	B
5	Selfi
6	Major
7	Global GF
8	Global 1
9	Global 2
10	Global 3
11	Capital
Sheet2

Cell Formulas
Range	Formula
B5:B11	B5	=IF(OR($B$2="all",$B$2=AD5),AD5,"")

The above list is copied directly from the list in Helper Table:

MrExcel_20200325.xlsx
	AD
1	Helper Table:
2	Col for
3	Names
4	All
5	Selfi
6	Major
7	Global GF
8	Global 1
9	Global 2
10	Global 3
11	Capital
12
Sheet2

And then the main formula in the body of the summary table examines Data!:A7:V7....that's what this part of the formula is doing: MATCH($B10,Data!$A$7:$V$7,0)
Can you have a look at your Data table and tell me the cell location that says "Capital". On my version, that is cell Data!V7, but I am wondering if your version is the same. Then check that there are no extra spaces before or after the word "Capital" in the Data table and in the Helper table, and that the spelling is the same. For example, if I go to Data!V7 and add a space so that it reads "Capital ", then the main summary lookup fails and I get blanks in the monthly and yearly summaries.

 

[KRice]

For the array formula, copy this formula to your clipboard:
=IF($E$2="Yearly",TRANSPOSE(AE4:AE26),TRANSPOSE(AF4:AF26))
Then go to the first cell of your summary table column headers. This is the cell where you would like either 2008 or Apr-year to appear. Paste the formula and hit enter...you should see an error...but that's expected. Confirm that the arrays referenced in the TRANSPOSE functions point to the correct cells in the helper table. You may have the helper table in a different location...so array {2008,2009,...,2030} is described by the first range (AE4:AE26 in my version), and array {Apr-year, May-year, Jun-year,,..,Mar-year+1,blank,blank,...blank} is described by the second range (AF4:AF26 in my version). You may need to adjust these references so that they point to your Helper table lists. After making any changes to this formula hit enter...and again see the error.

Then select the same cell containing this formula and extend the selection to the right to cover all 23 cells that can contain a column header, hit F2, and then hit Control-Shift-Enter (all three keys at the same time). This enters the formula as an array formula across all 23 cells. You should see one of the arrays populate the column headers, depending on the option chosen in E2.

The good news is that once this is done, it does not need to be repeated again.

 

[KRice]

If you want to avoid an array formula, you could place this formula in the F column in the cell where you would like to see either 2008 or Apr-year (cell F4 in my version) and then copy across for a total of 23 cells. Adjust the "F" references to reflect the starting column. This builds the year headings starting with 2008 or the month-year headings starting with April (month 4).
=IF($E$2="Yearly",2008+COLUMN(F3)-COLUMN($F3),IF(COLUMN(F3)-COLUMN($F3)<12,TEXT(DATE($E$2,4+COLUMN(F3)-COLUMN($F3),15),"mmm-yyyy"),CHAR(129)))

 

[shamsu203]

Hi
Tks for ur reply . As suggested by you I checked the cell ref of capital and found that it was w7 and not v7 in my copy of the worksheet. I do not know how ur worksheet shows v 7 anyway that problem is solved and the ret exactly confirms to my old summary sheet . I will stick with ur array formula ,. I very much appreciate your help in solving this problem for me.???

 

[KRice]

Excellent, I am glad to hear that, and happy to help. I am puzzled by the W7 location. Your copy and paste of Data showed "Capital" in the 22nd column, but something must have changed. I believe three columns are used for each Name, the 1st describing lease details, the 2nd showing monthly amounts, and the 3rd being used for yearly sums. Capital is the exception...only two columns are shown for it. Perhaps the Data table was adjusted to maintain the same structure for Capital? In any case, the only change necessary (it sounds like you've already made it) is to expand the MATCH function's array for searching to column W or beyond. If more Names are added to the Data table, recall that the monthly amounts relied upon are being taken from the column immediately before the column where the Name is found.

Incidentally, the annual sums shown in the 3rd column of each Name group that I mentioned are not used by the summary table formula, but you may wish to keep those columns intact in your Data table for running periodic sums on the data, and of course, for storing the Name in the upper section.

Best wishes!

 

[shamsu203]

TKs once again for all ur help bi and have a good day
Shamsu