I have a small extract of a much larger file, attached. The idea is that with each customer group, there are a set of assumptions. Not all of them will be only 13 assumptions. It's fine to assume all of them will be 3 months' worth of columns.
Right now if I need to copy and make another set of customer group assumptions, I have to do the following steps:
- copy the assumptions area towards the right
- name the customer group name on top (for identification purposes...serves no functional value, but that's one of my ideas for how to improve this)
- copy all the pivot data lines associated with a customer group, and then change the amount formula
- right now the way I'm changing the account formula is to go into the first one, tweak the index ranges to match where the newly created assumptions area is, and then copy that down to the rest of the customer group's amount cells.
The inefficiency with the above is that for every customer group (and there are many we are trying to model), it is a back and forth process to find the starting point, make sure the rows height is correct, etc.).
I. Ideally, I'd like to somehow use the Customer Group column in pivot data list, to include in the formula, so that the starting point of the index is actually a result of the formula looking in row 2, to find the match for the customer group name, and then because it's found that location, use that as the starting point for the rest of the Index. I have accomplished finding the cell reference with the formula in L5. That gives me an address of the starting point from which I need to count down X rows (of assumptions) and right 3 columns (for a total of 4 columns in the range). Without going on to part II below, I don't know how to integrate the L5 formula into any of the pivot list Amount formulas from row 23 down.
II. assuming the above can work, then next thing I need is to be able to define a range around it which is not fixed, but rather looks to see what the last non-blank cell is, and starting with the found starting point, goes to that last cell...and then uses 4 columns of width
If either/both of the above, would be great.
Thanks for any ideas.
