Solved

Excel named range into separate cells

Posted on 2016-11-07
3
77 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 52

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 33

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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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 …

739 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