I use Excel to capture geologic data most of which is heavily formatted for
visual display and ease of understanding. When I cut and copy data in cells,
sometimes the format is cut or copied as well. I would like Excel to have a
layer on which the format is applied and the data residing on a different
layer. When the format layer is locked, a cut or copy or paste only affects
data, not the format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

Editpaste special there you have different options like values, formulas
and formats

--
Regards,

Peo Sjoblom

(No private emails please)


"Rock Jock" <Rock wrote in message
...
I use Excel to capture geologic data most of which is heavily formatted for
visual display and ease of understanding. When I cut and copy data in
cells,
sometimes the format is cut or copied as well. I would like Excel to have
a
layer on which the format is applied and the data residing on a different
layer. When the format layer is locked, a cut or copy or paste only
affects
data, not the format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

"Paste Special" works great, if you know about it. Unfortunately, a lot of
end-users don't. When you create, format, and protect a worksheet for use by
others, it would be nice if the formatting would remain protected from people
using "Paste".

"Peo Sjoblom" wrote:

Editpaste special there you have different options like values, formulas
and formats

--
Regards,

Peo Sjoblom

(No private emails please)


"Rock Jock" <Rock wrote in message
...
I use Excel to capture geologic data most of which is heavily formatted for
visual display and ease of understanding. When I cut and copy data in
cells,
sometimes the format is cut or copied as well. I would like Excel to have
a
layer on which the format is applied and the data residing on a different
layer. When the format layer is locked, a cut or copy or paste only
affects
data, not the format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

If as you say you have protected the worksheet, and not unlocked the cells,
then the formatting is protected. In 2002 onwards you are also given a lot
of control over what is protected and what isnt, in case you wish to allows
users to do certain things.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Elkar" wrote in message
...
"Paste Special" works great, if you know about it. Unfortunately, a lot
of
end-users don't. When you create, format, and protect a worksheet for use
by
others, it would be nice if the formatting would remain protected from
people
using "Paste".

"Peo Sjoblom" wrote:

Editpaste special there you have different options like values, formulas
and formats

--
Regards,

Peo Sjoblom

(No private emails please)


"Rock Jock" <Rock wrote in message
...
I use Excel to capture geologic data most of which is heavily formatted
for
visual display and ease of understanding. When I cut and copy data in
cells,
sometimes the format is cut or copied as well. I would like Excel to
have
a
layer on which the format is applied and the data residing on a
different
layer. When the format layer is locked, a cut or copy or paste only
affects
data, not the format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

True, in that case the formatting is protected, but what if you want to allow
users to input data? The cells would then need to be unlocked. The cells
are still protected against manual formatting changes, but "Paste" overrides
this protection.

"Ken Wright" wrote:

If as you say you have protected the worksheet, and not unlocked the cells,
then the formatting is protected. In 2002 onwards you are also given a lot
of control over what is protected and what isnt, in case you wish to allows
users to do certain things.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"Elkar" wrote in message
...
"Paste Special" works great, if you know about it. Unfortunately, a lot
of
end-users don't. When you create, format, and protect a worksheet for use
by
others, it would be nice if the formatting would remain protected from
people
using "Paste".

"Peo Sjoblom" wrote:

Editpaste special there you have different options like values, formulas
and formats

--
Regards,

Peo Sjoblom

(No private emails please)


"Rock Jock" <Rock wrote in message
...
I use Excel to capture geologic data most of which is heavily formatted
for
visual display and ease of understanding. When I cut and copy data in
cells,
sometimes the format is cut or copied as well. I would like Excel to
have
a
layer on which the format is applied and the data residing on a
different
layer. When the format layer is locked, a cut or copy or paste only
affects
data, not the format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

If you unlock the cells then they are not protected against manual
formatting changes.

Agreed though, there is no easy way to do what you want.

Regards
Ken...................

"Elkar" wrote in message
...
True, in that case the formatting is protected, but what if you want to
allow
users to input data? The cells would then need to be unlocked. The cells
are still protected against manual formatting changes, but "Paste"
overrides
this protection.

"Ken Wright" wrote:

If as you say you have protected the worksheet, and not unlocked the
cells,
then the formatting is protected. In 2002 onwards you are also given a
lot
of control over what is protected and what isnt, in case you wish to
allows
users to do certain things.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Elkar" wrote in message
...
"Paste Special" works great, if you know about it. Unfortunately, a
lot
of
end-users don't. When you create, format, and protect a worksheet for
use
by
others, it would be nice if the formatting would remain protected from
people
using "Paste".

"Peo Sjoblom" wrote:

Editpaste special there you have different options like values,
formulas
and formats

--
Regards,

Peo Sjoblom

(No private emails please)


"Rock Jock" <Rock wrote in message
...
I use Excel to capture geologic data most of which is heavily
formatted
for
visual display and ease of understanding. When I cut and copy data
in
cells,
sometimes the format is cut or copied as well. I would like Excel to
have
a
layer on which the format is applied and the data residing on a
different
layer. When the format layer is locked, a cut or copy or paste only
affects
data, not the format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to
the
suggestions with the most votes. To vote for this suggestion, click
the
"I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader
and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

Ok, just to clarify. When you protect a sheet you have the option of
allowing users to format cells or not. I use Excel 2002 at home and 2003 at
work, so I can't speak for older versions. If you choose to not allow users
to format cells, it prevents manual changes when protected, but "Paste" still
overrides this protection. There is no way to prevent this and still allow
users to input data that I know of. There's probably a VB solution, but I'm
not really inclined to spend time on that.

"Ken Wright" wrote:

If you unlock the cells then they are not protected against manual
formatting changes.

Agreed though, there is no easy way to do what you want.

Regards
Ken...................

"Elkar" wrote in message
...
True, in that case the formatting is protected, but what if you want to
allow
users to input data? The cells would then need to be unlocked. The cells
are still protected against manual formatting changes, but "Paste"
overrides
this protection.

"Ken Wright" wrote:

If as you say you have protected the worksheet, and not unlocked the
cells,
then the formatting is protected. In 2002 onwards you are also given a
lot
of control over what is protected and what isnt, in case you wish to
allows
users to do certain things.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"Elkar" wrote in message
...
"Paste Special" works great, if you know about it. Unfortunately, a
lot
of
end-users don't. When you create, format, and protect a worksheet for
use
by
others, it would be nice if the formatting would remain protected from
people
using "Paste".

"Peo Sjoblom" wrote:

Editpaste special there you have different options like values,
formulas
and formats

--
Regards,

Peo Sjoblom

(No private emails please)


"Rock Jock" <Rock wrote in message
...
I use Excel to capture geologic data most of which is heavily
formatted
for
visual display and ease of understanding. When I cut and copy data
in
cells,
sometimes the format is cut or copied as well. I would like Excel to
have
a
layer on which the format is applied and the data residing on a
different
layer. When the format layer is locked, a cut or copy or paste only
affects
data, not the format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to
the
suggestions with the most votes. To vote for this suggestion, click
the
"I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader
and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

No. If the cells in question have been formatted as 'locked' and you
'choose' to allow users to 'not' format cells, which means doing nothing
because that is the default option anyway, then they are unable to
manually change data or formatting, or to effect any paste operation.

If in 2002/3 a cell is locked and you initiate the default protection, then
they can select locked and unlocked cells and that is it.

The only thing I can think of that works the way you describe is Data
Validation, which works fine when entering manually, but a paste operation
blows away the validation criteria. This however, is different to
'protection' as most people would understand it.

If in fact it is DV that you are referring to then agreed, compared with how
most people I believe would expect it to work, it is flawed.

Regards
Ken......................



"Elkar" wrote in message
...
Ok, just to clarify. When you protect a sheet you have the option of
allowing users to format cells or not. I use Excel 2002 at home and 2003
at
work, so I can't speak for older versions. If you choose to not allow
users
to format cells, it prevents manual changes when protected, but "Paste"
still
overrides this protection. There is no way to prevent this and still
allow
users to input data that I know of. There's probably a VB solution, but
I'm
not really inclined to spend time on that.

"Ken Wright" wrote:

If you unlock the cells then they are not protected against manual
formatting changes.

Agreed though, there is no easy way to do what you want.

Regards
Ken...................

"Elkar" wrote in message
...
True, in that case the formatting is protected, but what if you want to
allow
users to input data? The cells would then need to be unlocked. The
cells
are still protected against manual formatting changes, but "Paste"
overrides
this protection.

"Ken Wright" wrote:

If as you say you have protected the worksheet, and not unlocked the
cells,
then the formatting is protected. In 2002 onwards you are also given
a
lot
of control over what is protected and what isnt, in case you wish to
allows
users to do certain things.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"Elkar" wrote in message
...
"Paste Special" works great, if you know about it. Unfortunately, a
lot
of
end-users don't. When you create, format, and protect a worksheet
for
use
by
others, it would be nice if the formatting would remain protected
from
people
using "Paste".

"Peo Sjoblom" wrote:

Editpaste special there you have different options like values,
formulas
and formats

--
Regards,

Peo Sjoblom

(No private emails please)


"Rock Jock" <Rock wrote in message
...
I use Excel to capture geologic data most of which is heavily
formatted
for
visual display and ease of understanding. When I cut and copy
data
in
cells,
sometimes the format is cut or copied as well. I would like Excel
to
have
a
layer on which the format is applied and the data residing on a
different
layer. When the format layer is locked, a cut or copy or paste
only
affects
data, not the format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds
to
the
suggestions with the most votes. To vote for this suggestion,
click
the
"I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader
and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions

I apologize if my previous posts weren't more clear. You are right, about
"locked" cells. There are no problems there that I can see. However, all of
my comments have been about "unlocked" cells on a protected sheet.
Formatting for "unlocked" cells can be allowed or not. But when disallowed,
"Paste" disregards the formatting protection. That is the problem that I was
trying to point out.


"Ken Wright" wrote:

No. If the cells in question have been formatted as 'locked' and you
'choose' to allow users to 'not' format cells, which means doing nothing
because that is the default option anyway, then they are unable to
manually change data or formatting, or to effect any paste operation.

If in 2002/3 a cell is locked and you initiate the default protection, then
they can select locked and unlocked cells and that is it.

The only thing I can think of that works the way you describe is Data
Validation, which works fine when entering manually, but a paste operation
blows away the validation criteria. This however, is different to
'protection' as most people would understand it.

If in fact it is DV that you are referring to then agreed, compared with how
most people I believe would expect it to work, it is flawed.

Regards
Ken......................



"Elkar" wrote in message
...
Ok, just to clarify. When you protect a sheet you have the option of
allowing users to format cells or not. I use Excel 2002 at home and 2003
at
work, so I can't speak for older versions. If you choose to not allow
users
to format cells, it prevents manual changes when protected, but "Paste"
still
overrides this protection. There is no way to prevent this and still
allow
users to input data that I know of. There's probably a VB solution, but
I'm
not really inclined to spend time on that.

"Ken Wright" wrote:

If you unlock the cells then they are not protected against manual
formatting changes.

Agreed though, there is no easy way to do what you want.

Regards
Ken...................

"Elkar" wrote in message
...
True, in that case the formatting is protected, but what if you want to
allow
users to input data? The cells would then need to be unlocked. The
cells
are still protected against manual formatting changes, but "Paste"
overrides
this protection.

"Ken Wright" wrote:

If as you say you have protected the worksheet, and not unlocked the
cells,
then the formatting is protected. In 2002 onwards you are also given
a
lot
of control over what is protected and what isnt, in case you wish to
allows
users to do certain things.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------


"Elkar" wrote in message
...
"Paste Special" works great, if you know about it. Unfortunately, a
lot
of
end-users don't. When you create, format, and protect a worksheet
for
use
by
others, it would be nice if the formatting would remain protected
from
people
using "Paste".

"Peo Sjoblom" wrote:

Editpaste special there you have different options like values,
formulas
and formats

--
Regards,

Peo Sjoblom

(No private emails please)


"Rock Jock" <Rock wrote in message
...
I use Excel to capture geologic data most of which is heavily
formatted
for
visual display and ease of understanding. When I cut and copy
data
in
cells,
sometimes the format is cut or copied as well. I would like Excel
to
have
a
layer on which the format is applied and the data residing on a
different
layer. When the format layer is locked, a cut or copy or paste
only
affects
data, not the format.

----------------
This post is a suggestion for Microsoft, and Microsoft responds
to
the
suggestions with the most votes. To vote for this suggestion,
click
the
"I
Agree" button in the message pane. If you do not see the button,
follow
this
link to open the suggestion in the Microsoft Web-based Newsreader
and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions