Excel 2007 and sheet protection

Zbish

Board Regular
Joined
Apr 12, 2003
Messages
100
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

Board Regular
Joined
Apr 12, 2003
Messages
100
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

Board Regular
Joined
Mar 11, 2010
Messages
136

ADVERTISEMENT

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

Zbish

Board Regular
Joined
Apr 12, 2003
Messages
100
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

Board Regular
Joined
Apr 12, 2003
Messages
100

ADVERTISEMENT

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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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

Board Regular
Joined
Apr 12, 2003
Messages
100
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

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,992
Messages
5,526,109
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top