Offset() in Graph (XL2K)

Topic

New Reply

Is it possible to use Indirect() or OffSet() in the data Range for the source data of a graph? I have been trying but just get an error message

Reply | Quote

Replies

You can use Offset with fixed arguments in the Data Range of a chart, but it will be replaced with the result.

To do what you want is rather tedious. As far as I know, you have to set up separate named ranges (that may refer to a formula containing OFFSET, for instance) for the X-values and each of the data series in the chart, then specify these names in the definition of the chart series.

I have attached a simple example.
Change the value in cell E1 to change the source range of the chart.

If you select the chart, then Chart | Source Data…, you will see a fixed range in Data Range, but if you activate the Series tab, you’ll see that the series are based on named ranges.

Next, click outside the chart and select Insert | Name | Define… to see the definition of the named ranges. They refer to formulas with Offset.

Reply | Quote

Thanks Hans.

I think that you are right about it being tedious! It will probably be easier for me to maintain if I just recreate the table for the graph on the same sheet as the graph and use offset to populate that.

Peter

Reply | Quote

Personally, I don’t find it that tedious. Set up a chart and its good. One x, several Ys is not a big deal.

If you creating “identical” workbooks the names will copy. If you are creating “indentical spreadsheets” in the same workbook, you would have to do it multiple times, but the question I will raise: Do you have to have “MULTIPLE IDENTICAL GRAPHS” ie structured identically, but using different sets of data in the workbook?

The way I do this is to create a sheet with the graph data: setup the data as needed and name the ranges with offset, etc to get the graph as desired. The key is using INDIRECT functions to populate the items on this sheet, so this sheet can grab items from multiple sheets based on pulldowns, spinners etc.

You can even use indirect with the offset to change sheets in the named range. I have created on chart in a workbook that can literally contain the data for 100s of different “Identical charts”. Comboboxes (from FORMS) can be added onto the chart to change the year, line item, etc changing the chart “automagically” I have used it to grab data from MULTIPLE sheets with different number of rows in different sheets. The length of the data can be “counted” with INDIRECT also, so given the name of a worksheet, INDIRECT and OFFSET can literally point to any sheet, any column, any length, based on the changing of several sheets.

The result is literally 1 chart that can be used to view 100s of sheets “live” as the user changes selections. No need for chart copying, use the same chart. It also helps in updating the chart, no need to ever recreate more than 1, since all the charts are literally 1 chart.

Steve

Reply | Quote

I am replacing a workbook that has grown up piecemeal over the years, I basically have 10 different report pages with graphs that are repeated about 8 times for different production line. It is rather messy and inefficient so I am resetting it ready for next year. I think that I will probably go your way and use one master set of sheets with

Reply | Quote