#### CrackerJack$

##### New Member

- Joined
- Sep 7, 2006

- Messages
- 2

After almost a week of searching the net and almost, but not quite, finding the answer, I've decided to bring my query to this board!!!

I am responsible for the financial reporting of multiple entities where I work. I have created ONE Excel spreadsheet where each entity has its own tab that contains the detailed numbers and roll-ups.

On the analysis tabs, I want to let the user be able to select, via roll-down menus the entities, month-end, period under review and comparable to use. I have that part covered.

I want to use ONE formula, preferably VLOOKUP, to do this. Why one formula: good hygiene is all!

The data table for an entity consists of months presented in columns.

If a user selects a quarterlypresentation, the formula would be:

{=SUM(VLOOKUP(D15;F14:S17;{2;3;4})} where {2;3;4} returns the sum of columns 2,3 and 4 in the table. Notice the brackets before and at the end of the formula (not quite sure why...)

PROBLEM:

I would like to link to a cell that contains either {2} or {2;3;4} or {2;3;4;5;6;7;8;9;10;11;12;13} representing respectively a month, a quarter or a year and make that portion of the VLOOKUP formula dynamic.

So far, the only way I have found is to make the formula fixed and multiple by either 1 or 0 to activate or desactivate that sub-portion of the formula.

=SUM(VLOOKUP(D15;F14:S17;{2;3;4}))*E5+SUM(VLOOKUP(D15;F14:S17;{2;3;4}))*E6+SUM(VLOOKUP(D15;F14:S17;{2;3;4;5;6;7;8;9;10;11;12;13}))*E7

It seems too heavy to me...

Would anyone have a better way?

ThX