Reusable drop down menu to populate data

[G33kman]

Ok I'm making a job cost sheet for my boss so that he can enter in the information for a job and have it calculate out the total for him.
I'm trying to make this as fool proof and simple as possible since he's not very good with computers.

I'd like to create a drop down list so that he can select an employee name and it will automatically input the pay rate in the cell to the right.


So in my sheet I have created the drop down list in column (G) with the list of employees in the "dataValidation" worksheet.

I want him to be able to select an employee in the (G) column (i.e. G4) and then have it insert the correct pay rate in the the (H) column (i.e. H4) so that when he enter the hours (in the I column) it will calculate out the total in column J.

He needs to be able to reuse this drop down list also for multiple employees going to a job so I'm guessing I'm going to need to use a macro somehow?

Any help would be great!

[VBA FTW]

I would just write IF statements in column H. Something like =IF(G4=dataValidation!$A$2,dataValidation!$B$2,IF(G4=dataValidation!$A$3,$B$3,"No Match Found"))

You'll need to expand this IF statement to look at each employee name cell. I can help you with that if you need assistance.

You could also write VLOOKUP formulas in column H as well to look up the appropriate rate for an employee, this option may be easier to work with if you need to add/delete rows from the dataValidation sheet in the future

[jaslake]

Hi G33kman

In the attached I've modified the definition of your Named Range "Employees"...it's now Dynamic...as you add Names the Named Range expands to include them. I've also added Data Validation to Column G of Pricing Sheet.

This Code is in the Worksheet Pricing Module

Please Login or Register  to view this content.

I also got rid of the fancy formatting on Sheet datavalidation...hide it...your Boss does not need to see this sheet. However, YOU need to maintain it.
Try it...let me know of issues.

[G33kman]

Ahh!!! SO close! Lol Ok I have pretty much got this sheet complete but I had to move the payrate for the employee to a different sheet so that when other people are using it they can't see these prices. I copied over the data but when I select an employee from the drop down it still inserts the rate to the right of that column...
I'll upload what I have and if someone could help that would be great.

BTW Thanks Jaslake for the solution I wouldn't have been able to figure that out on my own... Lol

[jaslake]

Hi G33kman

I'm not clear on your issue...see if this handles it

Please Login or Register  to view this content.