Building a summary report with ONE formula.

**Speshul** · 10-16-2014, 03:56 PM

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.

**Pauleyb** · 10-17-2014, 01:12 PM

I'm missing something. Seems like an interesting trick, but how does this provide a different functionality than Copy->'Paste Formulas'? In my simple test, I was able to highlight all of the formulas, copy, and then paste in a new workbook.

**Speshul** · 10-22-2014, 09:19 AM

This method allows more flexibility. I may not have explained the possible applications that make it more useful than copy/pasting a template row of formulas...let me try to give a couple examples of this...

Expanding the formula more, but using the same basic concept, we can do a lot more;

Assuming the blue parts of these formulas are using the method described in the first post;

Add in another CHOOSE parameter on Row, if for example you want the formula type to change for row 6;
=CHOOSE(CHOOSE(ROW(),1,1,1,1,1,2),FORMULA_FOR_ROWS_1:5,FORMULA_FOR_ROW_6)
Or if you want them to change on every row after row 6 you would just use a =if(row()>6,FORMULA,FORMULA)

You can do something like this to have formulas which are in blocks of rows, say for rows:
1 Formula A
2 Formula B
3 Formula C
4 Formula A
5 Formula B....
To achieve this you could use something like;
=CHOOSE(MOD(INT(ROW(2:2)+1),3)+1,Formula_A, Formula_B, Formula_C)

Or if you want the Formula to create a header row for you as well,
=IF(ROW()=1,CHOOSE(COLUMN(),"Header1","Header2","Header3","Header4"),FORMULAS)

I'm not sure what the limit would be in terms of how many parameters, but using the CHOOSE function combined with ROW/COLUMN, in theory you could write a single formula that places 30 different formula styles per row, placing headers on every eleventh row, placing blanks on every 30th row, with different sets of those 30 formulas every seventh odd row. You could have some columns be blank, effectively making multiple tables with the same formula. Yes, it would take probably a few hours to make that single formula and it would be longer than this thread, but it would be fun to make and you would only have to make it once, and if need be you could save it in a notepad or paste it in an email to someone without attachments

**Pauleyb** · 10-24-2014, 03:04 PM

Okay - I understand. I think this may be somewhat personal taste and then also somewhat a specific use.

Trying to create an uber-formula that contains multiple formulas, potential header rows, and values would generally (for me) be too much effort and add a level of complexity. As you state, "it would take probably a few hours to make that single formula," but is that time well spent? I would also argue the statement that you would only have to make it once. Too many times I have had to revisit a formula and adjust. I do, however, appreciate your comment about being able to just send the formula to someone in an email without attachments. That would be impressive to the end user to paste and then, as they drag it across the spreadsheet, a fully formatted table with 'multiple' formulas just appears.

Is it cool and a neat trick -yes. But, I would hate to revisit this formula six months later or pass it on to someone for them to parse. Having each cell be its own formula or header row just seems cleaner to me. And if I suddenly need to add a row, my method would be simpler while I would need to be very careful updating a CHOOSE based formula. Plus, Copy->Paste Formulas should 'generally' work.

With that being said, it is something I will keep in my back pocket. I have used a similar technique for named ranges (where the choose function determines the range). I can also think of a few times where this technique may have been a more direct solution - such as using a keyword search to determine which formula is appropriate, e.g. =CHOOSE(MATCH(B23,{"add","multiply"}),C23+D23,C23*D23)

Thanks for sharing. I hope you don't take my comments as unappreciative, I just wanted to understand it.
Pauley