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)" wrote:

> 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.

[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" wrote:

> Have you tried using an intermediate data set that references your input data
> and plot the intermediate data set?
>
> "Richard Thorneycroft (Husky Products)" wrote:
>
> > 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]

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)" wrote:

> Hi BJ,
>
> Sorry I don't understand what you have suggested. I'm not an advanced user
> at all, sorry!
>
> Thanks,
>
> Richard
>
> "bj" wrote:
>
> > Have you tried using an intermediate data set that references your input data
> > and plot the intermediate data set?
> >
> > "Richard Thorneycroft (Husky Products)" wrote:
> >
> > > 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.

[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

"bj" wrote:

> 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)" wrote:
>
> > Hi BJ,
> >
> > Sorry I don't understand what you have suggested. I'm not an advanced user
> > at all, sorry!
> >
> > Thanks,
> >
> > Richard
> >
> > "bj" wrote:
> >
> > > Have you tried using an intermediate data set that references your input data
> > > and plot the intermediate data set?
> > >
> > > "Richard Thorneycroft (Husky Products)" wrote:
> > >
> > > > 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.