dynamic chart data

[RobbieC]

Hi there, I have 2 columns of data that are used in my chart. They are on Sheet 'Calcs' and are columns K6:K60 & M6:M60.

The 'Chart data range' is =Calcs!$K$6:$K$60,Calcs!$M$60:$M$60

This is fine if I have the both columns to row 60, but most of the time is less.

I have worked out the length of the column K & M in a seperate cell named 'chtLen' in cell V1 using: =SUMPRODUCT(MAX((K6:K60<>"")*ROW(K6:K60)))

This returns the number of entries in the column, for example '49'

So, I'm now hoping that I can replace the 'Chart data range' with something like:

="Calcs!$K$6:$K$" & chtLen, "Calcs!$M$6:$M$" & chtLen

If you can point me in the right direction to get this working, I'd be very grateful

Many thanks

Rob

 

[mart37]

Make from your table an Excel Table (Format as Table).
Make the graph based on the Excel Table.
When you update the table the graph shall update automatically.

 

[mart37]

When you wil refer to a range name you have to use INDIRECT.
But you can't use INDIRECT in a graph.
So you have to create a range name for the total array.
example:
array1=INDIRECT("'Calcs'!$K$6:$K$"&chtLen)
array2=INDIRECT("'Calcs'!$M$6:$M$"&chtLen)
Now you can use this names in a graph.
But you need also to refer to the workbookname.
example: 'workbookname.xlsx'!Array1

 

[RobbieC]

Hi Mart, thanks for this. I've had a think...

This Workbook is designed as a template to be saved as 'whatever', so having the graph refer to the workbook name will be problematic. I was just trying to automate this so that people didn't have to fiddle with 'chart data' after inputting the data

The only way 'round it at the moment is to manually change the 'Chart Data Range' to the final row number in the column(s). Otherwise, the graph goes squiffy.

I shall have to go back to the drawing board

Thanks again Mart