Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default help: paste special with an operation (add, multiply,...)converts everything to values, pasted formulas & links are lost

On Feb 25, 6:16*pm, wrote:
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Paste Special Multiply Formulas not working in Excel 2007 el_guapo Excel Worksheet Functions 1 September 25th 08 08:30 PM
Paste Special - Formulas - Multiply [email protected] Excel Discussion (Misc queries) 4 February 4th 08 03:45 PM
EXCEL - PASTE SPECIAL COULD HAVE AN OPERATION TO REVERSE ORDER sk Excel Worksheet Functions 2 May 1st 07 12:10 AM
Array Pasted from One Worksheet Converts Null Values to Zeros BK Waas Excel Discussion (Misc queries) 0 November 22nd 06 06:21 PM
Use of Paste Special - Operation Avadivelan TCS Excel Worksheet Functions 10 October 12th 06 02:33 PM


All times are GMT +1. The time now is 10:36 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"