Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# LOOK UP AND INSERT ON CHANGE

Posted on 2011-10-09
Medium Priority
247 Views
Hi Experts

In cell C3 on sheet "Sht 1 - Table 1" I need a formula or VBA code to look at cell C4 (numbers between 1 & 200) and based on that number look at sheet "Employees - Table 1" A2:A201 and match that number then insert into C3 the corrosponding name in that row in "Employees - Table 1" but from column B.  EG below:

SHEET "Sht 1 - Table 1"
A            B                 C         D      E           F
1
2
3                              ROB DENNIS                BRETT GIFFORD
4                              3                                 5

SHEET "Employees - Table 1"
A                  B
1      1          JOHN SMITH
2      2          JOHN SMITH JNR
3      3          ROB DENNIS
4      4          JOHN SMITH SNR
5      5          BRETT GIFFORD

So "ROB DENNIS" will be put into C3 based on the number in C4
and "BRETT GIFFORD" will be put into F3 based on the number in F4

and so on. I have this reocurring in different cells 100 times.
I dont know how to do this but guess it would be a change event in C4 that runs some code??

Cheers

Rob
0
Question by:RobJanine
• 4
• 2

Author Comment

ID: 36940392
please note that on sheet "Employees - Table 1" the numbers in Column A (Employee ID numbers) may not be in order.
0

LVL 9

Expert Comment

ID: 36940411
0

LVL 7

Expert Comment

ID: 36940447
Not a problem at all...

I reordered the employees table a bit to show you the order doesn't matter.
Type any number that also exists in the employees table into row 4 on the first sheet, and the correct name will appear in row 3 directly above.

Let me know if I've missed something here
EE-Example.xlsx
0

Author Comment

ID: 36940459
0

LVL 7

Accepted Solution

m4trix earned 2000 total points
ID: 36940465
my previous example was basically the same thing. Here's the same with your version:
Book1-v2.xlsx
0

Author Comment

ID: 36940466
Thankyou....works great.....keep an eye out I have another question shortly but I think it may be quite hard.

Cheers and thanks again.

Rob
0

Author Closing Comment

ID: 36940467
Thankyou
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month15 days, 15 hours left to enroll