Excel 2007 and sheet protection

[Zbish]

Hi
Using Excel 2007
Have generated a worksheet collecting data, which is then charted on subsequent sheet tabs - this all works fine till...........

Once sheet is protected the charts no longer update as new data is added to the data worksheet
All the columns where the data is stored are unprotected
Have checked of the - Select unlocked cells
Have checked of the - Use Auto Filter - required to sort data at latter stage

Any suggestions would be greatly appreciated

Thanks

 

[GlennUK]

Not enough information about how you've set this up. Say how the data flows from the collecting sheet to the charts ... and which sheets are protected, etc etc.

 

[Zbish]

Hi Glen
Appreciate you taking the time

The 'data' entry sheeet is protected
The columns requiring input have cells marked as un locked

Under the 'Protect Sheet' dialogue box - have the following selected......
Select Unlocked Cells
Use Auto Filter

The charts are basic line charts spun off into there own sheets and not protected

The charts update fine when the data sheet isn't protected

Thanks again

 

[GlennUK]

I can't see how protecting the source area prevents charts elsewhere updating. I must be missing something in your descriptions ... maybe someone else here can spot the problem.

 

[stefankemp]

Hi, try pressing ALT+F5, or F9 to refresh/recalculate. Let me know if that works.

 

[Zbish]

Hi Glen
Appreciate you taking the time

The 'data' entry sheeet is protected
The columns requiring input have cells marked as un locked

Under the 'Protect Sheet' dialogue box - have the following selected......
Select Unlocked Cells
Use Auto Filter

The charts are basic line charts spun off into there own sheets and not protected

The charts update fine when the data sheet isn't protected

Thanks again

 

[Zbish]

Hi Stefan

Appreciate you taking the time

Reagretfully that idea of recaluculating hasn't worked
Did try it before posting for assistance, but thanks anyway

 

[GlennUK]

Describe the processes you do ( when the sheet is unprotected ), that change the data and update the chart automatically. And mention any special objects you've created to aid in your chart definition ( defined names maybe? ).

 

[Zbish]

Hi Glen

The 'data' sheet is an Excel table
Rows are added as required
Pressing either the 'enter' or the 'tab' keys in the last column takes you to column A of the next row
As you type in the data the chart(s) updates without any issues
The colour themem is light blue and dark blue I note this as when.......

I select the columns containing the cells I need unlocked - so data may be entered
Then I protect the sheet selecting from the dialogue box
Select Unlocked cells and
Use Auto Filter
No Password is selected
Now when I press either the 'enter' or the 'tab' keys in the last column ti takes you to column A of the next row as expected
Entering the data and pressing the enter key column B is selected and the row in highlighted in white as opposed to the blue clour scheme noted earlier and the chart doen't update

So that's the process

Again I thank you for taking the time

 

[GlennUK]

That is the first time in this thread that you've said that you were using a Table. Use dynamically defined names to source your data series instead.