Slight problem.

ONC · Apr 16th, 2002, 07:09 AM

Ok, I can't get my head around this problem that I have.

Here goes.

I have a few worksheets within the same workbook. One of the sheets has a list of employee names, addresses, contract etc. The other has the wage slips. I have two sheets for the wage slips, one for full time and the other for part time.

I have managed to name the data within the employee records so its called "employee data". I have then used a validation list to lookup this data. Now its displays the surname of the employee. Once the surname is displayed, I have lookups to get the first name, address, pay etc.

However I have two problems. The first problem is that the validation list shows all of the surnames, part and full time. Is there anyway of only showing one, so you can't select the wrong type?

The second problem is that some people have the same surname, and a validation list will only recognise the first one of the two. How can this work?

I'm not sure if anyone can help but this is out of my league. Any help would be much appreciated.

If I haven't explained properly I can send the workbook over. For a copy of the workbook just reply.

Thanks LOADS!

ONC · Apr 16th, 2002, 09:42 AM

anyone?

IML · Apr 16th, 2002, 09:49 AM

Can you use a unique identifier such as employee number, social security, etc. If so, you could create a list that looks up whether that employee number is full or part time. Based on this you could use the indirect function as your validation key with lists named "Part-time", "Full-Time".

ONC · Apr 16th, 2002, 11:33 AM

that sounds like an Idea, but it also sounds confusing. What do I need to do to get that to work code wise.

Thanks for the help

IML · Apr 16th, 2002, 11:51 AM

I was thinking along the lines of formulas, I can't help with code. But the heart of the problems sounds like you don't have unique identifies.

ONC · Apr 16th, 2002, 12:28 PM

Hehe, sorry. Thats what I mean by code.

I'm not sure what to do for the forumla and what these keys are in excel (know of them in access)

IML · Apr 16th, 2002, 12:48 PM

I don't fully understand, but here is what I would propose

To get around the multiple surname, add a unique identifer such as your companies employee coded to the left hand column of you r name worksheet. Instead of having them choose surname, choose the employee name. You can do this by naming your list and using this as the source in your valdiation. Now simply key your vlookup off the employee number (instead of the surname)
=vlookup(employee#, your name worksheet, column, 0)

To restrict only parttime users on the partime sheet and vice versa, add a full or parttime column on your name sheet. Sort by this and name part time employee numbers, for example "P_time". On the parttime time sheet where they are to select their employee number, use valdiation. Select list and type in =P_time in the source box.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 16th, 2002, 07:09 AM	#1
ONC New Member Join Date: Apr 2002 Posts: 6	Ok, I can't get my head around this problem that I have. Here goes. I have a few worksheets within the same workbook. One of the sheets has a list of employee names, addresses, contract etc. The other has the wage slips. I have two sheets for the wage slips, one for full time and the other for part time. I have managed to name the data within the employee records so its called "employee data". I have then used a validation list to lookup this data. Now its displays the surname of the employee. Once the surname is displayed, I have lookups to get the first name, address, pay etc. However I have two problems. The first problem is that the validation list shows all of the surnames, part and full time. Is there anyway of only showing one, so you can't select the wrong type? The second problem is that some people have the same surname, and a validation list will only recognise the first one of the two. How can this work? I'm not sure if anyone can help but this is out of my league. Any help would be much appreciated. If I haven't explained properly I can send the workbook over. For a copy of the workbook just reply. Thanks LOADS!

Apr 16th, 2002, 09:42 AM	#2
ONC New Member Join Date: Apr 2002 Posts: 6	anyone?

Apr 16th, 2002, 09:49 AM	#3
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	Can you use a unique identifier such as employee number, social security, etc. If so, you could create a list that looks up whether that employee number is full or part time. Based on this you could use the indirect function as your validation key with lists named "Part-time", "Full-Time".

Apr 16th, 2002, 11:33 AM	#4
ONC New Member Join Date: Apr 2002 Posts: 6	that sounds like an Idea, but it also sounds confusing. What do I need to do to get that to work code wise. Thanks for the help

Apr 16th, 2002, 11:51 AM	#5
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	I was thinking along the lines of formulas, I can't help with code. But the heart of the problems sounds like you don't have unique identifies.

Apr 16th, 2002, 12:28 PM	#6
ONC New Member Join Date: Apr 2002 Posts: 6	Hehe, sorry. Thats what I mean by code. I'm not sure what to do for the forumla and what these keys are in excel (know of them in access)

Apr 16th, 2002, 12:48 PM	#7
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	I don't fully understand, but here is what I would propose To get around the multiple surname, add a unique identifer such as your companies employee coded to the left hand column of you r name worksheet. Instead of having them choose surname, choose the employee name. You can do this by naming your list and using this as the source in your valdiation. Now simply key your vlookup off the employee number (instead of the surname) =vlookup(employee#, your name worksheet, column, 0) To restrict only parttime users on the partime sheet and vice versa, add a full or parttime column on your name sheet. Sort by this and name part time employee numbers, for example "P_time". On the parttime time sheet where they are to select their employee number, use valdiation. Select list and type in =P_time in the source box.