Offset formula for chart (Excel 2003)

Topic

New Reply

I want to use a single chart (bar or line) to dynamically display 1 set of data from a table. The user selects the # of years (1-5) they want to view and the chart should only display those columns of data. I tried to use OFFSET formula but got stuck. Attached is my sample.

Year=3 (selectable by user)
Data set 1 = n1
Data set 2 = n1 n2
Data set 3 = n1 n2 n3
Data set 4 = n1 n2 n3 n4
Data set 5 = n1 n2 n3 n4 n5

In this case the chart would display ‘data set 3’ which represents Year 3.

Thnx, Deborah

Reply | Quote

Replies

You can create a defined name ChartData that refers to

=OFFSET(Sheet1!$C$7,0,0,1,Sheet1!$C$4)

Select a chart, then select Chart | Source Data.
Activate the Series tab, then set the Values reference to =SingleDataset.xls!ChartData.
Click OK.

Reply | Quote

Is there a reason why this technique would not work for Excel 2007. I have that version (which I do not like) now on one of my computers – and cannot get the offset formula to work. I keep getting a “That function is not valid” or “The formula you typed contains an error…” message. I’ve triple-checked the syntax (and even pasted in Hans’ as a quadruple check).

Reply | Quote

Does the attached version work in Excel 2007?

Reply | Quote

Yes – it does work. And I see how it’s setup. And I can even duplicate it on my 2003 machine and then open it on my 2007 machine. But still cannot figure out how to create it on the 2007 version.

Reply | Quote

I’m sorry, I can’t help you with that; I don’t have Excel 2007 yet.

Reply | Quote

I finally tried it with the filename prefixed to the range

=’5-744343-SingleDataSet.xls’!ChartRangeData

And that worked. Did not realize that the filename had to be used – especially if only one workbook is open. Sorry for the wild goose chase.

Reply | Quote

Ah yes, that’s confusing. It usually works too if you prefix the defined name with the name of the worksheet:

=Sheet1!ChartRangeData

Excel will automatically substitute the workbook name when you click OK.

Reply | Quote

FWIW, there seem to be a few issues with charts in 2007 if you use a defined name that starts with the word ‘Chart’, so it seems to be best to use another name, like ChtData instead.

Reply | Quote

Thanks for the tip. I am having lots of issues transitioning to 2007: pivot tables; external connections; AND charts.

Reply | Quote

Excellent works like a charm – of course. I always struggle with Offset. At first I didn’t include the worksheet name in the defined name, I didn’t remember having to do that before.

// Thnx, Deborah

Reply | Quote

I would use a dummy row of data to graph against (please see attached)
I created the dummy row directly under you original data and then used this formula =IF(COLUMN()-2<=$C$4,C7,"") to get the data points. The graphs backgrounds correspond to the rows' whose data they are using.

Reply | Quote

Hans beat me to the other option. I wanted to add the one caveat of using the defined name version. Using this method will make the Y axis will be dynamic as well. Unlike the X axis, you cannot specify a min and max value for the Y axis (or at least I couldn’t find a way).

Reply | Quote