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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Excel named range into separate cells

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
johnnyg123
Asked:
johnnyg123
2 Solutions
 
Ryan ChongCommented:
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
 
Rob HensonIT & Database AssistantCommented:
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
 
johnnyg123Author Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now