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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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?
 
Upvote 0
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
 
Upvote 0
range

is called 'forecast' or actual range is A5 - N8578
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
OK

all working, absolute genius, thanks very very very much
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top