Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-16-2019, 09:44 AM
anuragrawat123 anuragrawat123 is offline Get field value on pressing enter at cell in excel Windows XP Get field value on pressing enter at cell in excel Office 2007
Novice
Get field value on pressing enter at cell in excel
 
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 Get field value on pressing enter at cell in excel Windows 7 32bit Get field value on pressing enter at cell in excel Office 2010
Novice
 
Join Date: Jan 2019
Posts: 26
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 Get field value on pressing enter at cell in excel Windows 7 64bit Get field value on pressing enter at cell in excel Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

If you want to replace the value of a cell with another VBA is required. I will move your thread there
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #4  
Old 01-18-2019, 06:48 AM
ArviLaanemets ArviLaanemets is offline Get field value on pressing enter at cell in excel Windows 8 Get field value on pressing enter at cell in excel Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
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 Get field value on pressing enter at cell in excel Windows 7 64bit Get field value on pressing enter at cell in excel Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

@Arvi
The OP asked that the existing vale of a cell be replaced by another one. I don't think vlookup will help
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #6  
Old 01-20-2019, 06:18 AM
alpha alpha is offline Get field value on pressing enter at cell in excel Windows 10 Get field value on pressing enter at cell in excel 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
Get field value on pressing enter at cell in excel 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft