Rolling Chart

[Peter From The UK]

Hello

Not sure where to post this as I believe it could be VB or maybe Functions

I have a table with many columns, but, of which I only use 3. This is for
left hand and right hand items in the list and for a main sub heading as:

Sub heading 1
C1R1 Total C5R1 Total C9R1
C1R2 % C5R2 % C9R2

An soforth as below

Sub heading 2
C1RX Total
C1RX %

Sub heading TOTAL
C1RX1 Total
C1RX2 %

The problem arises from having to export the data from a database into an
Excel spreadsheet and the number of rows are not always the same. For
example Sub heading 1 may have 20 rows today but 25 rows tomorrow but only
10 rows the next day but the columns will never change

From these totals I have to plot a simple bar graph. I know that I will have
to export them to a template to enable the graph to be plotted or maybe have
a VB module which I can call each time the export has been done.

This is where a further problem arises. I have very little VB experience (I
am still learning, but very quick due to the needs). One the macro/function
is working how would I run this from a personal macro book

Any ideas or help would be greatly appreciated to maintain my already bald
head

Thanks in advance to anyone who can help

 

[Bob Phillips]

Peter,

Have you any experience of dynamic ranges? These might solve your problem.

For instance, if you have Sales in column A with a heading row, you can
define the range (Insert>Name>Define...) as rngSales, with a RefersTo value
of =OFFSET($A$1,,,COUNTA($A:$A),1), and use this range name in the chart
data definition, such as =Sheet1!rngSales.

Importing the data will not affect the name, and your charts should
dynamically reflect the data.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peter From The UK]

Bob

Thanks for the example. I don't have ant experience with dynamic ranges but
I will soon learn. I will try what you have shown and see how I get on. Many
thanks for that. I will let you know via the group what happened

Peter