Altering data found through a lookup

lynxbci

Board Regular
Joined
Sep 22, 2004
Messages
201
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi,
I have a list of data approx 9000 lines long (32 columns), and have a seperate sheet that allows a user to input a code. This then retrieves their data item using vlookup(). I would like to let the user then amend the contents of one of the fields, thus updating the original data list. Is this possible?

thanks
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It would be possible using the Worksheet_Change event procedure linked to a cell in which the user entered a new value.

In which cell does the user input the code used by VLOOKUP, where would they enter the new value, and what column do you want to update?
 

lynxbci

Board Regular
Joined
Sep 22, 2004
Messages
201
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
answers

I have put both the data sheet and the lookup on one sheet now. So the lookup reference is in A2. Then in cell H2 i want to input an actual figure. Also would like to put a comment in I2. The actual would then go into column H and the comment into column I on the data list. Trying to keep the refernces simple and uniform.....as ever.

Kev
 

lynxbci

Board Regular
Joined
Sep 22, 2004
Messages
201
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

range

is called 'forecast' or actual range is A5 - N8578
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Right click the sheet tab and choose View Code. Paste this into the window on the right:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Long
    Dim c As Integer
    If Intersect(Target, Range("H2:I2")) Is Nothing Then Exit Sub
    r = WorksheetFunction.Match(Range("A2").Value, Range("forecast").Columns(1), False)
    c = Target.Column
    Range("forecast").Cells(r, c).Value = Target.Value
End Sub

Press Alt+F11 to return to your worksheet and try it out by entering something in H2 or I2.
 

lynxbci

Board Regular
Joined
Sep 22, 2004
Messages
201
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

thanks

Ok thanks, just need to work out why the program doesn't initiate when i change a field. That should be automatic shouldn't it? I have put a stop in the routine to check, but it doesn't actually run it. Is there a setting to change so that worksheet_change routines (or any other) run automatically?

Sorry to be a pain.
 

lynxbci

Board Regular
Joined
Sep 22, 2004
Messages
201
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
OK

all working, absolute genius, thanks very very very much
 

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,494
Hi Andrew,

Once you've updated the Lookup table, dont you have to re-instate the formula in the changed cell in H2:I2, or am I misunderstanding?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,736
Messages
5,573,921
Members
412,555
Latest member
mark84
Top