CODEWITHSUNDEEP
excelexcel-2003excel-formulavba
user1222280
user1222280

Reputation: 17

Excel 2003: Extracting results of calculations

I would like to know if there is a way to link a cell such that it holds the results of a calculation permanently so that even when the input to the calculation is changed, the previous result is still saved.

To further illustrate, for example, when lets say my calculation is x+2=y and x = 1 in the first case. I want to be able to link a cell to save the result y=3 and also the input x =1, even when I change the value of x.

A possible solution to do use an IF condition and a PASTE SPECIAL command together, but I am not sure how to programme it such that it is automated. Is there a way to do this using formulas? Or must I venture into VBA?

Upvotes: 0

Views: 101

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149325

A possible solution to do use an IF condition and a PASTE SPECIAL command together, but I am not sure how to programme it such that it is automated. Is there a way to do this using formulas? Or must I venture into VBA?

Here is one way without the need of PASTE SPECIAL command. Paste the code in relevant Worksheet code area. Please refer to the snapshot 1. This is just an example. Please change it as per your requirements. What the code does is, it checks if the user entered any Value of X (Cell A2) and if the user did then capture it captures the value of Y(Cell B2) and stores it in Col D. Please refer to snapshot 2.

enter image description here

Snapshot 1

enter image description here

Snapshot 2

And this is the complete code. One piece of caution though. Do not try this code without the ".EnableEvents = False" or without the error handling in your main sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    
    On Error GoTo Whoa
    
    '~~> Check if any value was entered in cell A2
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
        End With
        
        '~~> Get the next available line in Col D for output
        LastRow = Range("D" & Rows.Count).End(xlUp).Row + 1
        
        '~~> Save the value
        Range("D" & LastRow).Value = Range("B2").Value
    End If
    
LetsContinue:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

HTH

Sid

Upvotes: 1

Afshin Moazami
Afshin Moazami

Reputation: 2098

You can copy the value of cell y and paste - values only - in another cell. Right Click -> Paste Special --> Select 'values'

Upvotes: 0

Related Questions