HI All you Gurus out there,
I need a bit of help. I am working on a report that I need to basically get a percentage of revenue. My formula is Dymanic using Index where I can drill down to the account and using a Indirect for the Tab based on the selection of Year.
Below is a formula that I need to sum the correct revenue. The Revenue I need to add at three segments Internal, External and Broker from the Whole. Is there a way I can make this smaller maybe making it into a function?
Another thought is I just create a Macro where it inserts the formula into the cell and then save as values....Thanks in advance for your suggestions.....
=SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8)-SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,14),"Brk")+SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B8,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8)
I need a bit of help. I am working on a report that I need to basically get a percentage of revenue. My formula is Dymanic using Index where I can drill down to the account and using a Indirect for the Tab based on the selection of Year.
Below is a formula that I need to sum the correct revenue. The Revenue I need to add at three segments Internal, External and Broker from the Whole. Is there a way I can make this smaller maybe making it into a function?
Another thought is I just create a Macro where it inserts the formula into the cell and then save as values....Thanks in advance for your suggestions.....
=SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8)-SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,14),"Brk")+SUMIFS(INDEX(INDIRECT("'"&$BR$3&"'!"&"$W:$AH"),0,N$6),INDIRECT("'"&$BR$3&"'!"&"$L:$L"),$B8,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$4),$BE$4,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$5),$BE$5,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$6),$BE$6,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$7),$BE$7,INDEX(INDIRECT("'"&$BR$3&"'!"&"$A:$N"),0,$BF$8),$BE$8)