Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Offset() in Graph (XL2K)

    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Offset() in Graph (XL2K)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset() in Graph (XL2K)

    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

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Offset() in Graph (XL2K)

    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

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Offset() in Graph (XL2K)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •