Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Odd Formula copying Behavior
I have a very odd situation going on with excel 2007, and thought I would try
a post here, before I try a total Office re-install: I have an excel spreadsheet with a variety of formulas in the rows. Starting today, when I copy/paste a formula from one row, to a row immediately below it (using drag copy), rather than the formula being copied as it has always done, instead (and here is the odd part), it seems to paste the result of the formula I am copying into that cell (no more formula). Additionally, if instead of dragging to copy just one cell, I drag to copy to several cell rows, it works as normal. Very strange. Same applies if I use Ctrl-C, Ctrl-V copy, etc. Hope that makes sense. About ready to do an uninstall, reinstall, before a total system rebuild, if that doesn't work. But if there is an easy fix, would appreciate any insights. Thanks! PatK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Odd Formula copying Behavior
Hi,
First thing I would check is whether Manual recalc is on - choose Formulas, Calculation Options, Automatic. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "PatK" wrote: I have a very odd situation going on with excel 2007, and thought I would try a post here, before I try a total Office re-install: I have an excel spreadsheet with a variety of formulas in the rows. Starting today, when I copy/paste a formula from one row, to a row immediately below it (using drag copy), rather than the formula being copied as it has always done, instead (and here is the odd part), it seems to paste the result of the formula I am copying into that cell (no more formula). Additionally, if instead of dragging to copy just one cell, I drag to copy to several cell rows, it works as normal. Very strange. Same applies if I use Ctrl-C, Ctrl-V copy, etc. Hope that makes sense. About ready to do an uninstall, reinstall, before a total system rebuild, if that doesn't work. But if there is an easy fix, would appreciate any insights. Thanks! PatK |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Odd Formula copying Behavior
Hey, Shane! thanks for the reply!
Yes, Calc is set to automatic, vs manual. This is the strangest behavior I have ever seen. Drag copy down one cell...you get the calculated result (in this case, vlookups pulling content from another page), and the formula completely goes away, and in it's place is the "value" from the vlookup. But, if I drag/copy down 2 rows, or more, it works just fine. (by drag copy, I mean holding the little black box in the lower corner of the selected cell, and dragging downward). I am open to trying anything! Thanks again! pat "Shane Devenshire" wrote: Hi, First thing I would check is whether Manual recalc is on - choose Formulas, Calculation Options, Automatic. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "PatK" wrote: I have a very odd situation going on with excel 2007, and thought I would try a post here, before I try a total Office re-install: I have an excel spreadsheet with a variety of formulas in the rows. Starting today, when I copy/paste a formula from one row, to a row immediately below it (using drag copy), rather than the formula being copied as it has always done, instead (and here is the odd part), it seems to paste the result of the formula I am copying into that cell (no more formula). Additionally, if instead of dragging to copy just one cell, I drag to copy to several cell rows, it works as normal. Very strange. Same applies if I use Ctrl-C, Ctrl-V copy, etc. Hope that makes sense. About ready to do an uninstall, reinstall, before a total system rebuild, if that doesn't work. But if there is an easy fix, would appreciate any insights. Thanks! PatK |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Odd Formula copying Behavior
Ok...I figured it out. A few weeks back, I had found a neat set of code out
here that someone wrote to track rows that had changed (code pasted below). That code was tied to the worksheet itself. It appears that, with that code implemented, it caused the problem that I saw. not sure exactly what, in the code caused this . Here is code. I might post this in that same thread in the programming group, should someone else experience this. The row: IF Target.Cells.Count 1 Then Exit Sub probably explains why it did not occur when I copied/pasted (changed) more than one cell. Would like to figure this out, tho, as the code really does work nice in keeping a history. Appreciate all who may have stared at this.... PatK CODE: Private Sub Worksheet_Change(ByVal Target As Range) Dim myRow As Long Dim myVal As Variant If Target.Cells.Count 1 Then Exit Sub With Application .EnableEvents = False .ScreenUpdating = False myVal = Target.Value .Undo myRow = Sheets("History Sheet").Cells(Rows.Count, 3).End(xlUp)(2).row Intersect(Target.EntireRow, ActiveSheet.UsedRange).Copy _ Sheets("History Sheet").Cells(myRow, 3) Sheets("History Sheet").Cells(myRow, 2).Value = Now Sheets("History Sheet").Cells(myRow, 1).Value = .UserName Target.Value = myVal .ScreenUpdating = True .EnableEvents = True End With End Sub "PatK" wrote: Hey, Shane! thanks for the reply! Yes, Calc is set to automatic, vs manual. This is the strangest behavior I have ever seen. Drag copy down one cell...you get the calculated result (in this case, vlookups pulling content from another page), and the formula completely goes away, and in it's place is the "value" from the vlookup. But, if I drag/copy down 2 rows, or more, it works just fine. (by drag copy, I mean holding the little black box in the lower corner of the selected cell, and dragging downward). I am open to trying anything! Thanks again! pat "Shane Devenshire" wrote: Hi, First thing I would check is whether Manual recalc is on - choose Formulas, Calculation Options, Automatic. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "PatK" wrote: I have a very odd situation going on with excel 2007, and thought I would try a post here, before I try a total Office re-install: I have an excel spreadsheet with a variety of formulas in the rows. Starting today, when I copy/paste a formula from one row, to a row immediately below it (using drag copy), rather than the formula being copied as it has always done, instead (and here is the odd part), it seems to paste the result of the formula I am copying into that cell (no more formula). Additionally, if instead of dragging to copy just one cell, I drag to copy to several cell rows, it works as normal. Very strange. Same applies if I use Ctrl-C, Ctrl-V copy, etc. Hope that makes sense. About ready to do an uninstall, reinstall, before a total system rebuild, if that doesn't work. But if there is an easy fix, would appreciate any insights. Thanks! PatK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy formula result (text) only - without copying formula | Excel Discussion (Misc queries) | |||
copying the Hyperlink function result without copying the actual formula | Excel Worksheet Functions | |||
Very odd behavior | Excel Discussion (Misc queries) | |||
PERCENTRANK in array formula: strange behavior | Excel Discussion (Misc queries) | |||
Strange behavior in INDEX(..., MIN(...)) - array formula | Excel Discussion (Misc queries) |