Copy Chart from one area to another without fixed cells

Everything you ever wanted to know about dynamic charting is contained in these two articles.

http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
http://www.utteraccess.com/wiki/inde...namic_Charting

I will regurgitate some of the content here as applied to the specifics of this requirement.

I created a sheet called dashboard to select the customer and to display the chart.

Cell B1 has a List-type data validation using a named dynamic range called
Customer_List which is defined as =OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A)-1,1).

The offset command, done “from scratch” has 5 arguments
- Start cell
- Rows to go down
- Columns to go right
- Number of rows to return
- Number of columns to return

So this range points to the following:
- Start in Cell A2 on the data sheet
- Go down zero rows and over zero columns (so we are still in A2)
- Return a range COUNTA(A:A)-1 rows deep and 1 column wide

The reason for the minus 1 in the COUNTA is that we don’t want to count the header column. With the date provided, this works out to Data!A2:A5. As you add or delete customers to Column A, the dynamic range will change to cover them.

I assumed that the X-axis of your chart is fixed to 12 months and that you will probably be copying and pasting data in. This range is defined as My_Date and it is set to =Data!$D$1:$O$1

The easiest way to set a fixed range is to highlight the range, go to the name box (upper left, below the menu, next to the formula box – it normally shows the topmost, leftmost cell selected) – then type in the name you want to assign. Or you can use the name manager and add it that way.

This brings us to the parameters sheet. I use a parameter sheet to do “scratch” calculations. It contains helper cells and intermediate calculations that keep future calculations from getting too complex.

The parameters sheet can be hidden if you wish.

Cell B1 on the parameter page merely reflects the named range Customer_Name. I assigned this static name to Cell B1 on the Dashboard sheet since I anticipated using it in several places in the workbook.

Cell B2 contains the formula: =MATCH(B1,Data!A:A,0). This formula gets the physical row on the Data sheet on which is found the customer name.

Cell B3 also reflects Customer_Name. I use this cell for the chart title. You may want to concatenate it with some other text, perhaps something reflecting the date range, for a fancier title.

There is one final dynamic range: My_Value =OFFSET(My_Date,Parameters!$B$2-1,0)

From scratch, the offset command has 5 arguments. However, you can also use offset to define a new range in terms of an existing range.

In this case we want to get the value of Cell B2 on the parameters sheet (which varies based on the customer selected) and go down that many rows minus 1 and shift over zero columns. The reason for the -1 is that MATCH starts counting from 1 whereas OFFSET starts counting from zero.

So if we select Customer B, Match tells us it’s on row 3. So the value range to plot is 2 rows below My_Date and zero columns over.

My_Date and My_Value are plotted on the chart using the techniques in the article referenced above.

So when you select a customer, it changes the Match that changes the offset that changes the row that the chart looks at to plot.