Solved

Excel named range into separate cells

Posted on 2016-11-07
3
26 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 49

Accepted Solution

by:
Ryan Chong earned 250 total points
Comment Utility
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 31

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
Comment Utility
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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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 …

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now