Creating a payroll spread sheet with drop down list linking to values

I am trying to attempt to create a payroll spreadsheet with certain aspects and with multiple worksheets. so what im trying to accomplish is this

1/ on my main worksheet would have the payroll template there would be 2 columns (1) would be "routes" (2) would be the dollar value of that route.

2/ i want to be able to pick from a drop down list in colume (1) which will have various routes such as A,
B, C, D, E etc

3/ once i have selected a route in colum (1) i want to have the dollar value that is associated to that route to show up in column (2)

ex/ route A worth $1
route B worth $2
route C worth $3

so in column (1) i pick route "A" then in colum (2) it will show up as "$1" (dollar sign not needed)

- as for multiple worksheets, i will have my 1st worksheet as my payroll template and the 2nd worksheet will have my drivers names and routes and values of the routes which will be a master list of all my data

is there any way to do this, i have managed to create a dropdown list but thats about it, i have not been able to link any values together.

alright thanks i will give it a try and see how it goes.

works awesome, thank you, so my next question is, im trying to decipher the formula you used, i will have to create a new master list. which values in the =IF(ISNA(VLOOKUP(E6,Rates!$A$3:$B$10,2,0)),"",(VLOOKUP(E6,Rates!$A$3:$B$10,2,0))) do i need to change for the second sheet or 3rd sheet etc which ever sheet i use to hold all my raw data.

if your master list is on a sheet named "Rates" and the layout is the same as the one you uploaded you should be fine.. but if the data base is larger then A3:B10 then youll have to change these numbers.. you can create a "blank sheet" and copy it over and over for different employees. you can also expand the "rates" look value from A3:B10 to $A$3:$B$100 or something like that. this will allow you to up add extra routes and rates.

the formula break down is like this.. IF(ISNA(VLOOKUP(E6,Rates!$A$3:$B$10,2,0)),"",(VLOOKUP(E6,Rates!$A$3:$B$10,2,0)))

the first half of the formula ... IF(ISNA(VLOOKUP(E6,Rates!$A$3:$B$10,2,0)),"" is saying "if there is a N/A error in the lookup, return the value of nothing thus the double quotes ("") if there is no error then do the lookup (the second half) ...(VLOOKUP(E6,Rates!$A$3:$B$10,2,0)))

to break it down further..(in my own words).... it's saying look up value in cell E6, the value can be found in the table on sheet called "Rates" the table is located in cells A3:B10, (the dollar signs is saying look only here) so it looks like this $A$3:$B$10 . and once you find the value return the value in column 2 (in this case its the B column) and then the "0" is saying return an exact match not an approximate match..

hope this helps..



update to you last post.

your question .... "How do create and infinite formula for my drop down list on routes so when I want to add more. As it stands I just made my list and named the range cells routes and through vaidation option."

answer.. in your data tab at the top.. under datavalidation.. you have the source being "routes" hit ctrl/F3 and the names manager will pop up.. double click the routes name and a edit window will open.. change the source from =Rates!$A$3:$A$10 to =Rates!$A$3:$A$1000
this will give you more entry space for the routes (1000 entries to be exact)

and your second question
"How do I make an infinite for that vlook up formula you gave me so it matches with my route list

Basically any time I add more routes and dollar value to my master list I dont have to go and keep editing the formula "

answer
change the formula IF(ISNA(VLOOKUP(E6,Rates!$A$3:$B$10,2,0)),"",(VLOOKUP(E6,Rates!$A$3:$B$10,2,0))) to look like this =IF(ISNA(VLOOKUP(E6,Rates!$A$3:$B$10000,2,0)),"",(VLOOKUP(E6,Rates!$A$3:$B$10000,2,0)))

this will give you 10000 entries to look up.. all you have to do is add to the "Rates" sheet and it will automatically work for you ..

thank you again, gave you a rep .

ok so i couldnt get the dropdown list on sheet1 payroll to show more than the a3 to a10 cellS.

when i clicked on the dropdown cell on sheet one and going to data / validations the ctrl + f3 function did not work and trying to change =routes changing it to =Rates!$A$3:$A$1000 came back as a error saying i cant take data from another worksheet

i still cant expand the routes on sheet2 to show more than cells a3 to a10 on sheet1 dropdown