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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

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,122,588
Messages
5,597,049
Members
414,117
Latest member
marblepoint

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