Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Paste Special Multiply Formulas not working in Excel 2007 | Excel Worksheet Functions | |||
Paste Special - Formulas - Multiply | Excel Discussion (Misc queries) | |||
EXCEL - PASTE SPECIAL COULD HAVE AN OPERATION TO REVERSE ORDER | Excel Worksheet Functions | |||
Array Pasted from One Worksheet Converts Null Values to Zeros | Excel Discussion (Misc queries) | |||
Use of Paste Special - Operation | Excel Worksheet Functions |