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
7
Medium Priority
?
247 Views
Last Modified: 2012-06-22
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
Comment
Question by:RobJanine
  • 4
  • 2
7 Comments
 

Author Comment

by:RobJanine
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

by:pritamdutt
ID: 36940411
Please upload a worksheet with sample data..
0
 
LVL 7

Expert Comment

by:m4trix
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:RobJanine
ID: 36940459
0
 
LVL 7

Accepted Solution

by:
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

by:RobJanine
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

by:RobJanine
ID: 36940467
Thankyou
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

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…

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question