Shorten the Formula

[chergian]

Hi Everyone,


I want to know if it is possible to shorten my formula in cell B13 to B24 of the attach file.

My problem is that, if i added more years and more branch, the formula will be too long.

I know this will possible, but for now i don't have an idea about it.

Thank you in advance.

[NeedForExcel]

Try this in B13 & Drag Down

=INDEX(INDIRECT("'"&$C$10&"-Year "&$C$9&"'!$B$3:$M$70"),1,MATCH(A13,INDIRECT("'"&$C$10&"-Year "&$C$9&"'!$B$1:$M$1"),0))

[shukla.ankur281190]

Try

C13=INDEX(INDIRECT("'"&$C$10&"-Year "&$C$9&"'!A2:M70"),MATCH($C$8,INDIRECT("'"&$C$10&"-Year "&$C$9&"'!A2:A70"),0),ROWS(C$13:C13)+1) and drag down!!!

[shukla.ankur281190]

Mr. Deep

Your formula will not give result when account name change.

Try this in B13 & Drag Down

=INDEX(INDIRECT("'"&$C$10&"-Year "&$C$9&"'!$B$3:$M$70"),1,MATCH(A13,INDIRECT("'"&$C$10&"-Year "&$C$9&"'!$B$1:$M$1"),0))

[NeedForExcel]

Mr. Deep

Your formula will not give result when account name change.

True!

I forgot to add the row part -

=INDEX(INDIRECT("'"&$C$10&"-Year "&$C$9&"'!$B$3:$M$70"),MATCH($C$8,INDIRECT("'"&$C$10&"-Year "&$C$9&"'!$A$3:$A$70"),0),MATCH(A13,INDIRECT("'"&$C$10&"-Year "&$C$9&"'!$B$1:$M$1"),0))

[shukla.ankur281190]

Now this is perfect

[chergian]

Try

C13=INDEX(INDIRECT("'"&$C$10&"-Year "&$C$9&"'!A2:M70"),MATCH($C$8,INDIRECT("'"&$C$10&"-Year "&$C$9&"'!A2:A70"),0),ROWS(C$13:C13)+1) and drag down!!!

Thank you so much! It works!

[shukla.ankur281190]

Glad to help you buddy

[MarvinP]

Hi chergian,

I think you can shorten your formula to:

=INDIRECT("'"&$C$10&"-Year "&$C$9&"'!"&ADDRESS(MATCH($C$8,'Branch1-Year 2012'!A:A,0),ROW()-11))

See this in the column next to your formula.

Read this site
http://spreadsheetpro.net/how-to-mak...-spreadsheets/

Indirect to another sheet formula with Address helper.xlsx

[chergian]

Now this is perfect

By the way ankur, is there any way that resulted data will automatically convert to "#.##%" when i select "RATIOS" in the category (cell B7)? Because it was converted to decimal...

[shukla.ankur281190]

Sorry I am not getting you as you are not using C7 cell with your formula... can you please explain clear with expected result ???

By the way ankur, is there any way that resulted data will automatically convert to "#.##%" when i select "RATIOS" in the category (cell B7)? Because it was converted to decimal...

[chergian]

Sorry I am not getting you as you are not using C7 cell with your formula... can you please explain clear with expected result ???

I mean in cell C7 when i select "RATIOS" and in C8 i select Capital Adequacy Ratio (CAR), the result data in cell b13 to b24 is 0.10 instead of 10.00%. Though they are the same, but is there any way in the formula to automatically display 10.00% instead of 0.10?

[shukla.ankur281190]

May be try

=IF($C$7="RATIOS",TEXT(INDEX(INDIRECT("'"&$C$10&"-Year "&$C$9&"'!A2:M70"),MATCH($C$8,INDIRECT("'"&$C$10&"-Year "&$C$9&"'!A2:A70"),0),ROWS(C$13:C13)+1),"##%"),INDEX(INDIRECT("'"&$C$10&"-Year "&$C$9&"'!A2:M70"),MATCH($C$8,INDIRECT("'"&$C$10&"-Year "&$C$9&"'!A2:A70"),0),ROWS(C$13:C13)+1))

[chergian]

may be try

=if($c$7="ratios",text(index(indirect("'"&$c$10&"-year "&$c$9&"'!a2:m70"),match($c$8,indirect("'"&$c$10&"-year "&$c$9&"'!a2:a70"),0),rows(c$13:c13)+1),"##%"),index(indirect("'"&$c$10&"-year "&$c$9&"'!a2:m70"),match($c$8,indirect("'"&$c$10&"-year "&$c$9&"'!a2:a70"),0),rows(c$13:c13)+1))

your marvelous!!

Thank you so much!


* a small modification changing "##%" to "0.00%"