Using formulas to vary the reference to the series value?

[DashboardNovice]

I have a data set with actual hours up to the current date, then forecasted hours beyond then. I have a graph that I want to display only the actual hours. Therefore as time goes one the cell reference corresponding to the actual hours to plot will grow over time one week at a time (I have one week per column).

Because of this, I would like to graph to automatically adjust to accommodate the growing range reference corresponding to actual hours.

After initially graphing the hours, I went into Select Data Source, Edit which brought up the window called Edit Series. In the field called "Series Values" there is a field where you can enter the range for the data series. Since this is a field, I figured you could enter a formula just like with Data Validation and Conditional Formatting.

Is this possible? I tried a formula with the OFFSET function and it said that the formula wasn't valid.

Edit: When I click on different cells then check the Name Manager, the formula seems to adjust based on the current cell selected. So if it is possible to use the OFFSET function when referring to the series range, what cell should I set relative and absolute cell references relative to?

 

[Hui]

No it's not possible directly in the dialog


What you do is setup s Named Formula that adjusts, typically using the Offset of Inex functions
Then reference the Nzmed Formula in the Series dialog
You must include the Sheet name
Eg: =Sheet1!myData

 

[DashboardNovice]

No it's not possible directly in the dialog


What you do is setup s Named Formula that adjusts, typically using the Offset of Inex functions
Then reference the Nzmed Formula in the Series dialog
You must include the Sheet name
Eg: =Sheet1!myData

That makes sense. The only part that I'm uncertain about now is defining the OFFSET function. I presume I would use a named range since the OFFSET function returns a range. Which cell should be selected when I define this or does it not matter?

 

[Hui]

It doesn't matter which cell is selected
But use $ signs in the function as appropriate

 

[DashboardNovice]

It doesn't matter which cell is selected
But use $ signs in the function as appropriate

I have the following formula:

=OFFSET(D13,COUNTA(B14:B26),0,1,26)

Excel appears to be ignoring my last argument (26). I want to return a range that is 1 row high, and 26 columns across. Excel seems to think that I am asking for a 1 x 1 result.

I just can't figure out what is wrong. Any suggestions?

 

[Hui]

Add it to a Named Formula
Then in the Name Manager click on the formula section
It will show you the selected range

What values are in B14:B26

I would also add as many $ signs as possible to lock it down as much as possible

 

[DashboardNovice]

Add it to a Named Formula
Then in the Name Manager click on the formula section
It will show you the selected range

What values are in B14:B26

I would also add as many $ signs as possible to lock it down as much as possible

I can't upload the original sheet because it contains company data so I created a new sheet with fictitious data with the same outline. Cell D4 in yellow is where I have my OFFSET function. The OFFSET function should return a range reference of D13:R13, because the most recent week completed is 04.10.2015 (cell F2). The numbers in blue are forecasted values.

On the first sheet I want to plot only the actual hours. So next week, I would drop in the hours for 04.17.2015, cell F2 would update to 04.17.2015 (I don't have the formula in there right now) and the chart would automatically reference D13:S13.

I feel like I am overlooking something with the OFFSET function.

I tried the following:

1. In the Edit Series box I reference cell D4 on the 2nd tab. This didn't work.
2. I created a named range called ActualHours with a reference to =OFFSET(D5,COUNTA(B6:B13)+1,0,1,MATCH(F2,$D$5:$U$5,0)) and Excel didn't accept this formula.

 

[NARAYANK991]

Hi ,

See the file.

Narayan