+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    12-03-2013
    Location
    ontario,canada
    MS-Off Ver
    Excel 2003
    Posts
    9

    Exclamation 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.
    Last edited by BlueSpinyShell; 12-03-2013 at 04:40 PM.

  2. #2
    Forum Contributor
    Join Date
    05-07-2013
    Location
    apple valley ca
    MS-Off Ver
    Excel 2007
    Posts
    110

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

    can u upload a sample sheet of what you already have?

  3. #3
    Registered User
    Join Date
    12-03-2013
    Location
    ontario,canada
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    payroll.xls
    hopefully this attachment worked.

    basically all im looking for is this. on sheet2 i will have my master list, each route will have a fixed value of whats its worth. so on sheet 1 which will be my payroll form, i want to be able to pick any route from a drop down list and in the next cell it will put that assigned value in automatically for me so i dont have to type it.

    ex/ A1 = route1 A2= $1

    so when i choose route 1 in cell B1, it will auto fill in cell B2 with a dollar value for me

  4. #4
    Forum Contributor
    Join Date
    05-07-2013
    Location
    apple valley ca
    MS-Off Ver
    Excel 2007
    Posts
    110

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

    in cell F6.. put this in the formula.. and copy down. your profile sais your using 2003 so i changed the formula slightly so you don't get "N/A" errors.

    =IF(ISNA(VLOOKUP(E6,Rates!$A$3:$B$10,2,0)),"",(VLOOKUP(E6,Rates!$A$3:$B$10,2,0)))

  5. #5
    Registered User
    Join Date
    12-03-2013
    Location
    ontario,canada
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    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.
    Last edited by BlueSpinyShell; 12-04-2013 at 03:52 PM.

  6. #6
    Registered User
    Join Date
    12-03-2013
    Location
    ontario,canada
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    Maybe 2 more questions on the formua

    1. 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.

    2. 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

  7. #7
    Forum Contributor
    Join Date
    05-07-2013
    Location
    apple valley ca
    MS-Off Ver
    Excel 2007
    Posts
    110

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

    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 ..
    Last edited by xwarlock10x; 12-04-2013 at 06:27 PM.

  8. #8
    Registered User
    Join Date
    12-03-2013
    Location
    ontario,canada
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    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
    Last edited by BlueSpinyShell; 12-05-2013 at 08:55 AM.

  9. #9
    Registered User
    Join Date
    12-03-2013
    Location
    ontario,canada
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    figured it out, just made the whole colume A on the second sheet as my routes. my other question is what if i wanted to link another field for rates, say usa and cdn rate. would i initially just add another entry to the formula

    =IF(ISNA(VLOOKUP(E6,Rates!$A$3:$B$10000,2,0)),"",(VLOOKUP(E6,Rates!$A$3:$B$10000,2,0)))

    =IF(ISNA(VLOOKUP(E6,Rates!$A$3:$B$10000,2,0:$A3:$C$10000,2,0)),"",(VLOOKUP(E6,Rates!$A$3:$B$10000,2,0:$C$10000,2,0)))

  10. #10
    Forum Contributor
    Join Date
    05-07-2013
    Location
    apple valley ca
    MS-Off Ver
    Excel 2007
    Posts
    110

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

    i thnk what your asking is if Vlookup can lookup in two separate locations.. the answer is no.. you can add to the current table in the "rates" sheet but not look up in a separate table.

    This formula will result in an error
    =IF(ISNA(VLOOKUP(E6,Rates!$A$3:$B$10000,2,0:$A3:$C$10000,2,0)),"",(VLOOKUP(E6,Rates!$A$3:$B$10000,2,0:$C$10000,2,0))) but if you want to add different info in the C and D columns and return the info you can.. take a look at this i changed the drop downs for you and added extra columns
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    12-03-2013
    Location
    ontario,canada
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    that is exactly what i was looking for, you are awesome i have usa and cdn routs and partial routes pay out so that works perfect. thanks again.

  12. #12
    Registered User
    Join Date
    12-03-2013
    Location
    ontario,canada
    MS-Off Ver
    Excel 2003
    Posts
    9

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

    thanks again for all the help, spread sheet looks good now, only have 2 small questions

    1/ is there a way to lock the spreadsheet so the column and rows width cant be changed?
    2/ is there way to have excell auto save on a regular basis say every 5 mins. i was planning on copying and pasting the spread sheet for approx 40 drivers and putting page breaks between them for when i print them. but i wanted to see if there was an auto save feature incase i close it by accident or power outtage.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Drop down list payroll
    By fishbol in forum Excel General
    Replies: 8
    Last Post: 07-15-2013, 10:03 AM
  2. Replies: 1
    Last Post: 07-28-2012, 08:03 AM
  3. Spread sheet linking cells
    By motolife in forum Excel General
    Replies: 1
    Last Post: 02-08-2011, 07:27 PM
  4. Linking spread sheets to a master list
    By Shsantos in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-28-2010, 03:18 PM
  5. Creating drop down list from values in column
    By jram in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-30-2009, 03:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1