How can I keep my bar charts formatting when data is refreshed?

[Richard Thorneycroft (Husky Products)]

Hi all,

I am running Excel 2002.

I have a spreadsheet with a database query from a Navision database. From
the data I have created a bar chart on a seperate sheet and whenever the data
refreshes the chart loses it's formatting.

Any ideas of how I can retain the formatting?

Many thanks in advance for your help.

 

[bj]

Have you tried using an intermediate data set that references your input data
and plot the intermediate data set?

 

[Richard Thorneycroft (Husky Products)]

Hi BJ,

Sorry I don't understand what you have suggested. I'm not an advanced user
at all, sorry!

Thanks,

Richard

 

[bj]

I am not familar with the navision data query,
but I assume the data is generated on a paricular data sheet in a particular
format.
I assume you have been making your chart from the data that comes directly
from the query.
What I suggest is that if say the query response comes in on Sheet 1
you use sheet two with equations such as
in A1
=indirect("Sheet1!R"&Row()&"C"&Column(),False)
and copied ot all of the equivelant cells of interest from Sheet 1
Plot from Sheet2
then when the new data comes in on Sheet 1, The graph has a buffer between
it and the new data.

 

[Richard Thorneycroft (Husky Products)]

Hi BJ,

I've figured it out now, I hadn't created the original spreadsheet, but
basically the chart was being created by a macro which was triggered to run
when the data refreshed. I just deleted the macro and the graph, created a
new graph and when it refreshed it kept the changes to the formatting. I had
tried creating a new worksheet and fetching the data from the existing sheet
but obviously due to the macro this didn't change anything.

Thanks for your help with this