• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1176
  • Last Modified:

Excel: retrieving a cell's old value?

In Excel, is there a way of retrieving a cell's old value after a new value has been entered into it?

What I want to do is to compare a cell's (say, cell A1) old value with its current value. If it is smaller, then
update cell B1, but if it is larger, then update cell C1 instead.

Kee, excel newbie



0
kee415
Asked:
kee415
  • 5
1 Solution
 
criCommented:
You can, with VBA, provided it is a manual input. Please state the purpose because perhaps a simpler solution can be found.
0
 
kee415Author Commented:

Hey cri:

Yes, it will be a manual input. When a user enters a new value in the cell, I need to compute the difference between the old and new value. Then what follows is simple, ie update either cell B1 or cell C1 based on the difference:

   If (diff > 0)
   then cell B1 = B1 + diff.
   else cell C1 = C1 - diff.

In general, how does one go about fetching an old (overwritten) value from a cell?

-Kee
0
 
criCommented:
Here a first try. It is pretty crude as it uses the Undo function. A more elaborate solution would involve using an auxiliary static array. But its initialisation is somewhat tricky/delicate.

In Excel:
a) Tools|Macros|VisualBasicEditor or Alt+F11

In VBE:
b) Make sure View|ProjectExplorer is visible
c) Click the appropriate_workSheet_ object of your workbook
d) Paste code into code window
e) Debug|Compile (not actually necessary, but it catches some of possible errors)
f) File|Save
g) File|Close to return to Excel


Option Explicit 'Prevent errors due typos

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   'Selective Updating
   'To do: Allow or prevent multiple cell operations
   
   Dim dblNew As Double, dblOld As Double, dblDiff
   Dim iOffset As Integer
   
   If Not Intersect(Target, Range("A1:A9")) Is Nothing Then  '<<Check/Adapt
     With Application
       dblNew = Target.Value
       .EnableEvents = False
       .Undo
       dblOld = Range(Target.Address).Value
       dblDiff = dblOld - dblNew
       If dblDiff > 0 Then
         iOffset = 1
       Else
         iOffset = 2
       End If
       With Target.Offset(0, iOffset)
         .Value = .Value + dblDiff
       End With
       Target.Value = dblNew
       .EnableEvents = True
     End With
   End If
 End Sub

 
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
criCommented:
To improve the above I need to know:

a) Number of input cells ?

b) Is always the same sheet / columns ?

c) Do you want/need to have a documentation for the process, like having the updating written as formula i.e. B1: =2 changed to B1: =2+0.5 ? If yes, is it ok for the user to have the startvalues in B1, C1 writen as formula i.e '=2' instead of '2'

d) Sorry to ask again: Are you kind of iterating something ? Eventually a much better solution could be found, like setting Tools|Options|Calculations: Iterations, or using the Solver or making a homebrew VBA function or ?
0
 
criCommented:
Any luck ?
0
 
criCommented:
Sighhh...
0
 
SpideyModCommented:
Force Accepted.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now