# Help with multi level referencing

1. ## Help with multi level referencing

Hello everyone, this is my first post so hopefully I do a good enough job describing my issue. See my attached workbook to better understand my question. In this workbook I have the template and two examples of the results I would be looking to be able to have automatically calculated.

I am wanting to have the "Final" column blue cells set to reference "Investment A" cells for each "Month" up until the month of change (D2) which I want to be able to adjust at any time to get different results. On the month that equals (D2) I want the "Final" column blue cells to start referencing "Investment B".

So far this is simple, I run into problems with the fact that I always want the point where the reference changes to begin the "Investment B" reference from the first month. For example if I select the change to happen at month 5 I want month 5 to reference "Investment B" "Month" 1 and then the remaining final cells to reference the following Investment B rows.

To take it one step further I would like the "Investment B" reference to actually be affected by another conditional reference shown in column "Investment B+A at time of switch". I want this to add "Investment B"s starting value with whatever the value of "Investment A" is one month before the switch. This will then impact the values for the cells I would like to reference after the switch.

So in summary I want to be able to select any month for a switch to occur in referencing two different columns. I want the post switch reference to always begin at the first cell of the new reference rather than the corresponding cell in the column. I would also like the reference, post switch, to include the value of the reference one row before the switch into its calculation. I want to be able to change only the yellow cells to get different results and I need help with formulas/functions for all of the blue cells.

I have really been struggling to describe what I am going for so hopefully there is some excel wizard on here that can translate all this for me. If not please ask questions.

Thanks All!

2. ## Re: Help with multi level referencing

For part I:

Investment_A =Sheet1!\$G\$6:\$G\$15
Investment_B =Sheet1!\$H\$6:\$H\$15
SumAB =Sheet1!\$I\$5:\$I\$15
SwitchMonth =Sheet1!\$D\$2

C8:C15
``Please Login or Register  to view this content.``
(I don't quite get part II yet)

There are currently 1 users browsing this thread. (0 members and 1 guests)