Solved

Excel named range into separate cells

Posted on 2016-11-07
3
56 Views
Last Modified: 2016-11-08
Trying to determine the best way to accomplish the following in an excel 2010 worksheet

I have a employeeid and employee name combination for about 20 Employees

I would like to have the employee name in a data validation

Is  there a way to pick the name and have it go in to say cell A2 and have the id go into cell a3????


I'm open to any option
0
Comment
Question by:johnnyg123
3 Comments
 
LVL 50

Accepted Solution

by:
Ryan Chong earned 250 total points
ID: 41878088
try something like this
=IFERROR( INDEX(Sheet2!$A:$A, MATCH(B2,Sheet2!$B:$B,0)),"")

Open in new window

see attached sample for more info.
28981593.xlsx
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 41878496
I read that as you have the ID and name combined in one cell but you want to be able to split them into two cells so that you can use the list of Names as Data Validation source.

What format is the combined data? Id then Name or name then ID? Is there a consistent length to the ID so that we know where to split the string in two?

You can use various text functions to split combined strings into component parts:

=LEFT(String, n)   returns n characters from the left of the string.
If the string is ID then Name and you know the ID is 8 characters, LEFT(String,8) would give the ID only

=RIGHT(String, n)   returns n characters from the right of the string.
If the string is Name then ID and you know the ID is 8 characters, RIGHT(String,8) would give the ID only

=FIND(text value, within search text, start)   returns the position value of the text value within search text using the start point as point zero.
If the string is ID and then Name but the ID is not consistent length but is followed by a space, =FIND(" ",Text,1) would give the position of the space so a combination with LEFT would then give   =LEFT(String,FIND(" ",String,1)-1)   ie give everything up to the first space  (the -1 ensures you don't get the space as well).
0
 

Author Closing Comment

by:johnnyg123
ID: 41879020
Thanks for the Posts

The combination of your suggestions led me to my final solution

I ended up separating out name and ID into 2 separate ranges.

One called EmployeeName   and one called EmployeeID

Then created another range that combined the 2 called EmployeeInfo

Used the EmployeeName for the datavalidation to select name

Used the following in the EmployeeID column to get the id to populate

=IFERROR(VLOOKUP(A6,EmployeeInfo,2,FALSE),0)

Thanks Again!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

776 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