Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 01-16-2019, 09:44 AM
anuragrawat123 anuragrawat123 is offline Windows XP Office 2007
Novice
 
Join Date: Jan 2014
Posts: 3
anuragrawat123 is on a distinguished road
Default Get field value on pressing enter at cell in excel


I have a list of Item_Code and its Item_Name in Sheet2 of columns A and B. I want that when I enter Item_Code at B2 cell in Sheet1 and then press ENTER. Then Item_Code is replaced by corresponding Item_Name in Sheet1. How can I achieve this.
Reply With Quote
  #2  
Old 01-16-2019, 11:31 PM
Bumba Bumba is offline Windows 7 32bit Office 2010
Novice
 
Join Date: Jan 2019
Posts: 9
Bumba is on a distinguished road
Default

Have you tried using VLOOKUP?
Reply With Quote
  #3  
Old 01-17-2019, 12:24 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,365
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

If you want to replace the value of a cell with another VBA is required. I will move your thread there
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #4  
Old 01-18-2019, 06:48 AM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Expert
 
Join Date: May 2017
Posts: 424
ArviLaanemets will become famous soon enough
Default

Why not to enter item code into one cell in sheet1, and get proper item name from Sheet2 in adjacent cell? Using VLookup like Bumba did advice! The formula will be something like:
Code:
=VLOOKUP($B2,Sheet2!$A$2:$B$100,2,0)
Reply With Quote
  #5  
Old 01-18-2019, 07:41 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,365
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

@Arvi
The OP asked that the existing vale of a cell be replaced by another one. I don't think vlookup will help
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #6  
Old 01-20-2019, 06:18 AM
alpha alpha is offline Windows 10 Office 2010 64bit
Novice
 
Join Date: Jun 2018
Posts: 18
alpha is on a distinguished road
Default

If your codes and names are on sheet2 in columns A and B, beginning in row 2:
- Copy the code below.
- Rightclick on the sheettab of sheet1 and click "View code".
- Where the cursor is flashing, rightclick and choose "Paste".
- Now you can drop codes in Sheet1 columnB, beginning in B2.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a As Integer
If Not Intersect(Range("b2:b30"), Target) Is Nothing Then
a = Application.CountIf(Sheets(2).Range("a2:a30"), Target)
If a > 0 Then
Target.Value = Application.VLookup(Target, Sheets(2).Range("a2:b30"), 2, 0)
End If
End If
End Sub

Last edited by alpha; 01-21-2019 at 12:37 AM.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to enter value based on value of another cell onemorething Excel 3 12-06-2017 09:26 AM
MS Word Horizontal Line Disappears after pressing Enter from end of line MikeWhite Word 5 01-20-2017 03:39 PM
Addresses in To... field disappear when pressing send BigFinger Outlook 0 02-26-2015 06:01 AM
default cursor movement after pressing enter, ms office 2013, win 8 sansousea Excel 0 07-11-2014 09:46 AM
how to enter what i want in cell tasuooooo Excel 4 07-23-2012 05:45 AM


All times are GMT -7. The time now is 03:05 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft