Topic: Offset() in Graph (XL2K) @ AskWoody

Offset() in Graph (XL2K)
Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Offset() in Graph (XL2K)
- This topic has 4 replies, 3 voices, and was last updated 21 years, 7 months ago.
Author

Topic
New Reply

WSBat17
AskWoody Lounger

August 14, 2003 at 7:08 am #392001

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

Viewing 0 reply threads
Author

Replies
- WSHansV
  AskWoody Lounger
  
  August 14, 2003 at 7:47 am #702208
  
  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
- WSBat17
  AskWoody Lounger
  
  August 14, 2003 at 8:25 am #702213
  
  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
  
  WSsdckapr
  AskWoody Lounger
  
  August 14, 2003 at 9:27 am #702223
  
  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
  
  WSBat17
  AskWoody Lounger
  
  August 14, 2003 at 10:45 am #702234
  
  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
Viewing 0 reply threads

Reply To: Offset() in Graph (XL2K)
You can use BBCodes to format your content.
Your account can't use all available BBCodes, they will be stripped before saving.

Your information:
Name (required):

Mail (will not be published) (required):

Website:

Cancel

Plus Membership

Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.

AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.

Welcome to our unique respite from the madness.

It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.

Offset() in Graph (XL2K)

Plus Membership

Search Newsletters

Search Forums

View the Forum

Search for Topics

Recent Topics

Recent blog posts

My Profile

Key Links

Remembering Woody

Offset() in Graph (XL2K)

Plus Membership

Search Newsletters

Search Forums

View the Forum

Search for Topics

Recent Topics

Recent blog posts

My Profile

Login and Registration

Key Links

Remembering Woody