Chart problems with MS Excel.

[OdiN]

Okay so I have a spreadhseet with some charts. They point to some ranges on other sheets. These ranges are queries to an Access database. There are 13 weeks worth of data, and only 13 weeks. The queries are setup so they only return the last 13 weeks worth of data.

Now, this is a shared workbook so that people can enter comments on their particular graphs. It is protected, and the sheets with the queries set to Very Hidden.

The problem is that the graphs change themselves.

Instead of staying with that 13 data point range, they change. Some are at 15, some 17. So it throws off how the graphs look. How does this happen and how can I prevent it from happening? If I set the data to refer to 13 cells then how can it just change itself to 15 or 17 cells? This is very frustrating. Is there something else to hard-set the data source? I don't understand how when I tell it what the data source is, that it can increase the range of that data source by itself.

[rory]

How have you set the data source for the charts? Using the named range representing the querytables from access or using fixed addressing?
Also, when you say the workbooks are shared, do you mean you have used the Tools-Share Workbook feature? If so, I would put good money on your workbooks corrupting at some point.

[OdiN]

Yeah I have the Share Workbook feature on. Kinda have to with this one as multiple people have to get into it to update it within a relatively short time. They each work on different sheets though so there's no conflicts in data entry.

I have the data source like so:

='Graph Data'!$A$2:$F$15

[OdiN]

No other ideas anyone?

[rory]

Well apart from the possibility that Sharing the workbook is screwing it up (and if it isn't, it will), what properties do you have set for the querytables in terms of overwriting cells, or inserting new rows?

[OdiN]

Well apart from the possibility that Sharing the workbook is screwing it up (and if it isn't, it will), what properties do you have set for the querytables in terms of overwriting cells, or inserting new rows?

They just overwrite what's there. The queries in Access show only a max of 13 records.

[OdiN]

Also, some info about the workbook.

Everything is protected. The only cells that can be updated are for comments about the graphs. All of the data sheets are hidden in the project, and cannot be accessed so it's hard to see how they are getting messed up. Users can't touch the graphs. Only a few text cells.

[majaro]

May I suggest you post a sample workbook if possible?

[OdiN]

May I suggest you post a sample workbook if possible?

I can't post it as it contains confidential information.

[rory]

Which version of Excel are you using for this?

[OdiN]

2003 SP2

[OdiN]

Also, I've tried going to each of the data sheets, selecting all of the cells below the 13th data point and deleting them all. Maybe there was some formatting or something that excel thought was data there? I probably won't know if that fixes it for a while though.