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
Please Login or Register to view this content.
or, broken down,
Please Login or Register to view this content.
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.
Please Login or Register to view this content.
Broken down so you can see the formulas;
Please Login or Register to view this content.
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.
Bookmarks