Set Excel chart data series range dynamically by a cell's value

1
votes

I have a simple chart in Excel where I plot 2 data series referring to a 3rd one.

What I want to do is to change the range of one of the first data series based on a number in a cell. For example if the number is 1 and the data series is in column A the range of the chart should be A1:A100. Now if I change the number to 10 the chart range should be A10:A100.

This doesn't seem hard but I cant figure it out. I've been searching StackOverflow and the web for a long time but I couldn't find anything that seems to solve my problem.

Thanks in advance to everyone who can help!

1
excelchartsexcel-formula
Could you show some of the Excel formula's you've tried? - Michael Shopsin
Look at Offset function. You could offset from A1 by that cell value -1 - QHarr
Made question text more understandable - Peter Szoldan

1 Answers

1
votes

You can use OFFSET function to determine the range to work with.

1) Variable start:

If E2 held the number, and you are in sheet 1, you could define with:

=OFFSET(Sheet1!$A$1,Sheet1!$E$2-1,0,100-Sheet1!$E$2+1,1)
  1. If E2 was 1 the range would be A1:A100
  2. If E2 was 10 the range would be A10:A100 etc.

2) Variable start and end:

You could extend this and have a variable for the end point - rather than hard code 100, you could use cell F2 to specify the end point, and change the formula to:

=OFFSET(Sheet1!$A$1,Sheet1!$E$2-1,0,Sheet1!$F$2-Sheet1!$E$2+1,1)

Example, with start and end (note that start to end is the height argument of the final range. As you want a single column, the width argument is set at 1):

Start and end in cells

3) Variable start, end and column of interest (i.e. if not just column A):

You could then further extend by specifying the offset columns arguments to shift from working with column A. Let's say we use G2 to specify how many columns to move to the right, we get:

=OFFSET(Sheet1!$A$1,Sheet1!$E$2-1,Sheet1!$G$2,Sheet1!$F$2-Sheet1!$E$2+1,1)

Which is:

Offset columns

Learn more about OFFSET:

Type =OFFSET() into the formula bar and press the fx symbol to expand the arguments:

Offset function

You will see it start with an anchor (reference) cell e.g. A1 and then rows, columns, height, width arguments. Saying from A1 how many rows do you want to move, columns to move, and what height and width should the final range be.

Additional info:

  1. https://support.microsoft.com/en-gb/help/324991/how-to-use-the-offset-function-in-excel
  2. http://www.contextures.com/exceloffsetfunction.html