Paste Special automatically

  • Thread starter Thread starter Javier Gomez [SBS MVP]
  • Start date Start date
J

Javier Gomez [SBS MVP]

Hi!

I was wondering if the following is possible:

Cell A1 = has a formula that changes with time
Cell B1 = can be blank or have an X

I would like to setup someway that Cell A1 stops calculating (saves the last
value) when I put an X on cell B1. Sort of copy + paste special the value of
the cell A1 when I put X on another cell.

Any ideas on how to acomplish this?
 
One way:

Choose Tools/Options/Calculation and check the Iterations checkbox. This
will prevent a Circular Reference error.

In A1, enter

=IF(B1="X",A1,NOW())

replacing NOW() with your formula that changes with time.
 
I almost forgot that I need this to be able to work in a range if
possible... so:

A1 stops calculating when there's an X on B1,
A2 stops calculating when there's an X on B2... and so on.
 
Thanks... that was fast!

I have 2 more questions:

1) Would Excel recalculate the formula each time it is opened or the value
is just stored there as long as there is an X on the cell?

2) Is the Iterations setting something stored in the workbook? If I try to
open the workbook on another machine it would keep working? If not, can I
force it to change it somehow?
 
1) XL will calculate it only when B1 changed or when a calculate all
command was given, or whenever the sheet calculates if you're using a
volatile function. It really doesn't matter since it will only return
its existing value if B1 is X.

2) Yes - iterations is stored in the workbook.
 
Hi Javier

Not sure you would a column full of circular references. If not try this
method.

Right click on the sheet Name tab and select "View Code".

Paste in the code below

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 And UCase(Target) = "X" Then
Target.Offset(1, -1) = Target.Offset(1, -1).Value
End If
End Sub



***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Thanks!

--
Javier [SBS MVP]

<< SBS ROCKS !!! >>

JE McGimpsey said:
1) XL will calculate it only when B1 changed or when a calculate all
command was given, or whenever the sheet calculates if you're using a
volatile function. It really doesn't matter since it will only return
its existing value if B1 is X.

2) Yes - iterations is stored in the workbook.
Click to expand...
I
 
Hi Dave!

First of all, thanks for your suggestion. The problem with this is that when
I use this method... whenever I put an X on the B column it stops
calcualting all on the A column (and this should be independent like B1 can
only affect A1, B2 only affect A2 and so on). I do not know enough VBA to
fix that... would you mind helping me?

Thanks a lot... you guys are great!
 
Sorry, my fault. I had the Row Offset out by 1. Try the code below in
its place


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column = 2 And UCase(Target) = "X" Then
Target.Offset(0, -1) = Target.Offset(0, -1).Value
End If
End Sub

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Hi!

Thanks... but I still see the same behavior. Now that I test it more
throughly... I think is more complicated here because each time I open the
workbook it recalculates and doesn't keep the value that it had when I put
the X.

Cheers,
 
Back
Top