Chart Offset Function

[macro12]

Hello,

I am having trouble with using an offset function for a graph (budgeted and actuals). The budgeted numbers come from the budget spreadsheet and are depended on the Summary tab E5 and E6. Similarly, the actual numbers come from the TO # Actuals tab "total amount based on clin (Row 9, 14, 20, etc) I tried using name defined ranges but kept receiving an error message. Can someone please help?

[dflak]

Here is my shot at it. I'm using helper columns on the To 2 Actuals page. You can put these on a hidden sheet if you wish.

Cell Q1 has the formula:=MATCH(Summary!E6,A:A,) - this gets the row where the selected CLIN is found. In the case of CLIN 2 this is row 11.

Cell Q2 has the formula: =MATCH("Total Amount",TA_range,0) - where TA_Range is defined as =OFFSET('TO 2 Actuals'!$A$1,'TO 2 Actuals'!$Q$1,0,20,1) - I'm making the assumption that the Total Amount line will be within 20 rows of the CLIN row. In the case of CLIN 2 this is 3 rows down from the CLIN title line.

Cell Q3 has the formula: =Q1+Q2 - this is the actual row that we want to plot.

Finally we have the range to plot =OFFSET('TO 2 Actuals'!$A$1,'TO 2 Actuals'!$Q$3-1,1,1,12) - I called this TA_Plot.

This should get you started.

To switch between plotting To 2 Actuals and To 3 Actuals, I suggest the following define TA_To2 as I defined TA_Plot, define TA_To3 the same way, except for the T03 Actuals page.

Then define a new named range called TA_Actual = if (Summary!$E$5 = "TO_2", TA_To2, TA_To3) - plot this named range.