Slight problem.

ONC

New Member
Joined
Apr 6, 2002
Messages
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!
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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".
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top