?
Solved

Excel: retrieving a cell's old value?

Posted on 2003-02-28
7
Medium Priority
?
1,163 Views
Last Modified: 2008-03-03
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
Comment
Question by:kee415
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
7 Comments
 
LVL 13

Expert Comment

by:cri
ID: 8046875
You can, with VBA, provided it is a manual input. Please state the purpose because perhaps a simpler solution can be found.
0
 

Author Comment

by:kee415
ID: 8047164

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
 
LVL 13

Accepted Solution

by:
cri earned 200 total points
ID: 8047261
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
Industry Leaders: 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!

 
LVL 13

Expert Comment

by:cri
ID: 8047284
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
 
LVL 13

Expert Comment

by:cri
ID: 8090150
Any luck ?
0
 
LVL 13

Expert Comment

by:cri
ID: 8401161
Sighhh...
0
 

Expert Comment

by:SpideyMod
ID: 8618718
Force Accepted.

SpideyMod
Community Support Moderator @Experts Exchange
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question