Building a summary report with ONE formula.

[Speshul]

This is useful for periodic reports you need to build daily/weekly/monthly, that consist entirely of formulas, and especially helpful when there are numerous types of formulas.

You can use a CHOOSE(COLUMN(), ) to do the job for you.

An example:

I have a sheet that has five columns, each with a unique formula that is copied down.

B =INDEX(Sheet2!A2:A500,MATCH(A2,Sheet3!F2:F50000,0),0)
C =SUM(B2,N(VLOOKUP(A2,Sheet3!C2:F500,3,0)))
D =F2*1.07
E =D2+SUM(A2:C2)
F =VLOOKUP(A2,Sheet2!B2:F500,4,0)

Ok. So my sheet has those formulas. When I have to build it each month, I either have to re-write them, or copy them one by one. Copying them might not be so bad, but what if you have 30 columns of formulas to make? That takes a little time, what if you mess it up?

My solution requires you pay attention to the cell references, the $'s need to be in the right place for your application. For these formulas I just have to lock the columns down so they don't move about when I copy the formula across.

Since I am starting in Column B, I want to set my COLUMN() in there to a 1, so;
=CHOOSE(COLUMN()-1,

Now simply paste all of your formulas in there, and fix the $'s so it copies correctly

=CHOOSE(COLUMN()-1,INDEX(Sheet2!$A2:$A500,MATCH($A2,Sheet3!$F$2:$F$50000,0),0),SUM($B2,N(VLOOKUP($A2,Sheet3!$C$2:$F$500,3,0))),$F2*1.07,$D2+SUM($A2:$C2),VLOOKUP($A2,Sheet2!$B$2:$F$500,4,0))

or, broken down,

=CHOOSE(COLUMN()-1,INDEX(Sheet2!$A2:$A500,
MATCH($A2,Sheet3!$F$2:$F$50000,0),0),
SUM($B2,N(VLOOKUP($A2,Sheet3!$C$2:$F$500,3,0))),
$F2*1.07,
$D2+SUM($A2:$C2),
VLOOKUP($A2,Sheet2!$B$2:$F$500,4,0))

The choose function is limited to 254 places, so that's as many columns as can be included in this formula.

-------------------

If you want to get fancy, and have a similar formula occurring multiple times, you can make it shorter by nesting a VLOOKUP in there with a formula index lookup table based on Row number, using the returned value to choose the formula appropriate to you. This is a slightly modified version of this formula I have in use at the moment. It is working on 15 columns, with 9 different formula structures.

=CHOOSE(VLOOKUP(COLUMN(),{1,0;2,1;3,1;4,2;5,3;6,4;7,1;8,0;9,1;10,1;11,5;12,6;13,7;14,8;15,9},2,0),VLOOKUP($A2,'Source'!$A:$I,MATCH(B$1,'Source File'!$A$1:$I$1,0),0),VLOOKUP($B2,T1!$A:$B,2,0),VLOOKUP($B2,T3!$B:$C,2,0),VLOOKUP($B2,T4!$A:$B,2,0),$J2*0.6,VLOOKUP($B2,T5!$A:$D,4,0),VLOOKUP($L2,T6!$B:$C,2,0),$K2*0.5,CONCATENATE("T", $B2, "-", $G2, "-", $L2))

Broken down so you can see the formulas;

=CHOOSE(VLOOKUP(COLUMN(),{1,0;2,1;3,1;4,2;5,3;6,4;7,1;8,0;9,1;10,1;11,5;12,6;13,7;14,8;15,9},2,0),
VLOOKUP($A2,'Source'!$A:$I,MATCH(B$1,'Source File'!$A$1:$I$1,0),0),
VLOOKUP($B2,T1!$A:$B,2,0),
VLOOKUP($B2,T3!$B:$C,2,0),
VLOOKUP($B2,T4!$A:$B,2,0),
$J2*0.6,
VLOOKUP($B2,T5!$A:$D,4,0),
VLOOKUP($L2,T6!$B:$C,2,0),
$K2*0.5,
CONCATENATE("T", $B2, "-", $G2, "-", $L2))

You can even get more fancy if your formulas change at a certain row by putting a second level of choose in there which looks at ROW()!


Hope someone finds this useful.