help: paste special with an operation (add, multiply,...) convertseverything to values, pasted formu


J

jf.usenet

Hello, I have just converted to MS Office 2007. When working with the
new Excel, I ran into a problem. I can no longer perform operations
while pasting and preserve the pasted link or formula. Whatever I'm
pasting always gets converted to values, no matter what combination of
paste special options I select. There is no difference between
selecting in Paste Special, in the Paste section, "Formulas" or
"Values" when combined with any operation from the "Operation" section
below. I know this worked in previous versions of Excel.

For example, if cell A1 contains the value 5, cell B1 has a formula =$A
$1, and C1 has the formula = 10, and I copy cell B1 and want to add
(via operation) the formula in B1 to the contents of cell C1, no
matter what I do the link is always pasted as value. The result after
performing paste special/formulas/add is always C1=(10)+5. The problem
is that I want this to be C1=(10)+$A$1. The result is the same when I
do paste special/values/add, which is very strange and very
frustrating for me. This worked in previous versions of Excel, but now
I cannot understand what’s going on.

I work with a lot of complex spreadsheets where I frequently need to
add a cell reference to existing formulas, allowing me, for example,
to later change one value in one cell (A1 in this example) and have
all the formulas with the link to this cell be automatically adjusted.
For example, I may want to increase everything by say 5%. I would
normally add to all the cells at the end of whatever formula or value
there might be (via paste special/formulas and multiply operation) a
formula to the end, simply adding “*$A$1.” Then, if I want no
modification, I would leave cell A1 = 1. If I want to increase
everything by 5% and see what the result would be, I would simply put
105% in cell A1. Unless I find a way how to copy this “*$A$1,” the
only way for me to accomplish this would be add this manually into all
the cells, which is an impossible task given the size of my files. I
cannot just paste/multiply by the value *105%, because that would just
hard-code the 105% into the formulas, creating even more problems for
me when I want to change it to a different number later.

Does anyone know if this is a bug in Excel, or if this is how it’s for
some reason supposed to be? Is there a way around this? Is this
documented somewhere? I have tried searching, but have not found
anything addressing this. As I said, this worked fine in Excel 97 and
probably other versions. Thanks in advance for any help.

John
 
Ad

Advertisements

J

jf.usenet

Hello, I have just converted to MS Office 2007. When working with the
new Excel, I ran into a problem. I can no longer perform operations
while pasting and preserve the pasted link or formula. Whatever I'm
pasting always gets converted to values, no matter what combination of
paste special options I select. There is no difference between
selecting in Paste Special, in the Paste section, "Formulas" or
"Values" when combined with any operation from the "Operation" section
below. I know this worked in previous versions of Excel.

For example, if cell A1 contains the value 5, cell B1 has a formula =$A
$1, and C1 has the formula = 10, and I copy cell B1 and want to add
(via operation) the formula in B1 to the contents of cell C1, no
matter what I do the link is always pasted as value. The result after
performing paste special/formulas/add is always C1=(10)+5. The problem
is that I want this to be C1=(10)+$A$1. The result is the same when I
do paste special/values/add, which is very strange and very
frustrating for me. This worked in previous versions of Excel, but now
I cannot understand what’s going on.

I work with a lot of complex spreadsheets where I frequently need to
add a cell reference to existing formulas, allowing me, for example,
to later change one value in one cell (A1 in this example) and have
all the formulas with the link to this cell be automatically adjusted.
For example, I may want to increase everything by say 5%. I would
normally add to all the cells at the end of whatever formula or value
there might be (via paste special/formulas and multiply operation) a
formula to the end, simply adding “*$A$1.” Then, if I want no
modification, I would leave cell A1 = 1. If I want to increase
everything by 5% and see what the result would be, I would simply put
105% in cell A1. Unless I find a way how to copy this “*$A$1,” the
only way for me to accomplish this would be add this manually into all
the cells, which is an impossible task given the size of my files. I
cannot just paste/multiply by the value *105%, because that would just
hard-code the 105% into the formulas, creating even more problems for
me when I want to change it to a different number later.

Does anyone know if this is a bug in Excel, or if this is how it’s for
some reason supposed to be? Is there a way around this? Is this
documented somewhere? I have tried searching, but have not found
anything addressing this. As I said, this worked fine in Excel 97 and
probably other versions. Thanks in advance for any help.

John

Hi - can anyone help or suggest anything? I can't believe I would be
the only one with this issue....or am I missing something?

Thanks in advance,
John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top