A problem.

[ONC]

Hello.

I wonder if anyone could possibly help me with a problem.

I have to create a two wage slips for and assignment. I have a database as a seperate sheet (not workbook) with the names of the people, how much they get paid etc. I am having two problems with this.

The first is that I am using Vlookup to get all of the information filled in when you type the persons surname. This part is easy. However I would like to have a list box with all of the names for part-time, than another for full-time (on the other wage slip). How do I do this?

I can only get the full list of names by naming the surname cells "Surname" and going Data|Validation|List, they typing "=surname".

How could I lookup the names and check them against the part-time/full-time column in the same database, then only display the relevant names?

The second problem Is that I have a middle initial box, but some people don't have one, and when I use the lookup function it shows up as "0" for people without one.

I would much appreciate any help you could offer with these problems.

Thanks very much.

P.S If anyone needs the .xls file to help give a better picture I will send it on over

 

[Ian Mac]

the second problem can be done in many ways.

1) Tools>Options>View then uncheck the Zero Values box. this will only do if you don't need 0's anywhere else on the sheet.

2) use an if statement =if(vlookup(lookup_value,table_array,col_index_num,range_lookup)=0,"",vlookup(lookup_value,table_array,col_index_num,range_lookup))

3) use conditional formating Format>Conditional Formating>Cell Value = 0 and use a colour that is the same as your background for the Font colour.

With regards to the first problem, you could use a pivot table or filter the list. There are a number of more complicated formula and VBA based answers but I'm not sure what solution you'd like.

If you want to send the book with more details, I'll be happy to look at it baring in mind that it is a project of yours I'd rather give you pointers!