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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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,526
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?
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,030
Messages
5,835,038
Members
430,333
Latest member
jaypatel1298

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top