How to tell if a cell is protected

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Barry.Robertson - 24 Feb 2008 19:36 GMT

I have nine workbooks I have combined into one workbook using the
drag-and-drop system as described by Bernard Liengme on 11/25/07. All the
workbooks were single worksheet's that my company uses as forms for personnel
files. I don't want to reinvent the wheel, but I am trying to make some of
the work easier.

What I am doing is setting up all the worksheets to autofill the redundant
information on each sheet, i.e. name, address, phone number, etc. I am using
the formula "=sheet!cell". It is working on some sheets and on others is
does not. On some of the sheets, only certain cells won't accept the formula
while others work just fine.

So, my question is: are some of the cells protected on a different level
than the usual sheet protection and locks (under formatting), and if so,
where do I view that information?

Reply to this Message

Niek Otten - 24 Feb 2008 22:43 GMT

<on others is does not.>
What does that mean? What happens? You do something (what exactly?) and Excel does (how?) or does not react. What exactly happens?

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have nine workbooks I have combined into one workbook using the
| drag-and-drop system as described by Bernard Liengme on 11/25/07. All the
[quoted text clipped - 11 lines]
| than the usual sheet protection and locks (under formatting), and if so,
| where do I view that information?

Reply to this Message

Barry.Robertson - 25 Feb 2008 00:14 GMT

Niek,

Lets say I have a workbook with three sheets in it: A, B, and C. On
sheet A I have a cell where I enter the persons name and once I enter that
name, on sheets B and C, the name is automatically inserted into the cells
for the name. The formula I'm using for this function is: =sheet!cell.

In reality, I have a workbook that I made and opened up several other
workbooks, that were all single worksheet workbooks, and used drag-and-drop
to move the worksheets into the new workbook. My first worksheet is called
"Info." On the Info sheet, I have cells that I fill in with the persons
name, address, etc. When I enter that information, like the example above, I
want the name to befilled in on the other worksheets. I have twelve
worksheets altogether.

On the interview sheet, the cell that gets filled in with the name is
D30. With this information, the formula to automatically enter the name in
the name cells on the other sheets is: =Info!D30 . Now, on some of the
sheets the formula works perfeect and does not show in the cell after I hit
enter. On other worksheets I enter the formula and it remains showing. When
I go back to the Info sheet and enter a name it does not autofill on the
sheet where the formula still shows.

For example, sheet two's name cell has the formula =Info!D30 entered
into it and is not showing after hitting enter. When I type Bob Smith on the
Info sheet it automatically fills in Bob Smith on sheet two. However, on
sheet three in the name cell the formula is still visible and when I go to
the Info sheet and type Bob Smith the name doesn't automatically fill in on
sheet three's name cell.

I my quest for an answer, I thought I read it's possible to protect
cells while still unprotecting the sheet. I thought I read you use a macro
or some code to accomplish the task. What I would like to know is this: Is
it possible to lock the cell as I just described, if it's possible how do I
find out if it's protected, and if it's not secretly protected how do I fix
the problem I'm having. Finally, I noticed on the worksheets I'm having the
problems with that I can't use Shift+Tab to move backwards on the sheet.

Sorry this is so long but I hope it's clear and concise.

> I have nine workbooks I have combined into one workbook using the
> drag-and-drop system as described by Bernard Liengme on 11/25/07. All the
[quoted text clipped - 11 lines]
> than the usual sheet protection and locks (under formatting), and if so,
> where do I view that information?

Reply to this Message

Mike Rogers - 25 Feb 2008 01:14 GMT

Barry

The reason your formula is still visible is because those cells are formated
for "TEXT". You can not re formate them and exit out and have the formula
work. You need to clear the formula and formate as <General> or <Number> and
re-enter the formula. (There are other ways but this is the easiest to
understand and do)

Mike Rogers

> Niek,
>
[quoted text clipped - 51 lines]
> > than the usual sheet protection and locks (under formatting), and if so,
> > where do I view that information?

Reply to this Message

Barry.Robertson - 25 Feb 2008 18:49 GMT

Thanks Mike, that did the trick. I appreciate the help all of you put into
these discussion groups.

> Barry
>
[quoted text clipped - 61 lines]
> > > than the usual sheet protection and locks (under formatting), and if so,
> > > where do I view that information?

Reply to this Message