Graphs with differnet amounts of data

[antony moseley]

Hi,

I have tried follow the guidlines on the website http://peltiertech.com/Excel/Charts/Dynamics.html under the Chart the Last 12 Months Dynamically part.

However it hasnt quite worked for me - i have attached a copy of what i have done. I think i can see what has gone wrong, it is looking upwards for data, rather than downwards but i do not know how to fix it

Any help would be appreciated

Thanks

Antony

[Andy Pope]

Try this revision

ChtCats: =OFFSET(data!$A$4,MAX(1,COUNTA(data!$A:$A)-chtLen),0,MIN(COUNTA(data!$A:$A)-1,chtLen),1)

[antony moseley]

Hi,

thanks for that.

I have tried to write a macro to build the graph automatcially aswell

Please Login or Register  to view this content.

however i am unsure as to why it needs to pull the name of the xls, as i need this to be dynamic as every day i would like to be able to run the macro to create the graph.

Any ideas on how i get round this?

Thanks

Antony

[Andy Pope]

Please Login or Register  to view this content.

[antony moseley]

Hi Andy,

thanks for the above.

I have also macroed to write the defined names, however it puts the " " at the begining and end of the wording and therefore doesnt like it - is there a way around this?

Thanks

Antony

Please Login or Register  to view this content.

[Andy Pope]

try

Please Login or Register  to view this content.

[antony moseley]

Hi,

I have done all of the above, and so at the back end of the macro i have the below. However it bombs out at the XValues bit. If i run the other part of my macro without the below, then run the below bit separately it works - it is a right head scratcher.

Let me know if you need any further info

Antony

Please Login or Register  to view this content.

[Andy Pope]

Works for me. See attached. Run the Test macro.

[antony moseley]

HI andy,

it works for me fine as two parts, but not as a whole. I cant down load anything at work, so will have to check tonight

Antony

[antony moseley]

Hi Andy,

I think I may has spotted the issue - I agree the macro works fine in your work book and if I copy it into another workbook it works ok as well, the issue comes if I try and run the Marco whilst it is one workbook against data in another work book, which is going to cause me an issue as I would ideally like to save this Marco in my personal, as I am going to need to be able to run it daily.

Any ideas????

Thanks

Antony

[Andy Pope]

Try changing the Thisworkbook references to Activeworkbook.

Please Login or Register  to view this content.