+ Reply to Thread
Results 1 to 6 of 6

I need a cell to reflect the billing rate of a name selected from a dropdown list.

  1. #1
    Registered User
    Join Date
    10-31-2014
    Location
    Michigan
    MS-Off Ver
    Windows 7
    Posts
    2

    I need a cell to reflect the billing rate of a name selected from a dropdown list.

    I'm building a job quoting calculator and I need a cell to reflect the billing rate of the employee that I choose from the dropdown list in the adjacent cell e.g. if I select the name Jim from the dropdown list, his rate of 50 automatically populates in the cell to the right. I am using two sheets, the first is the clean and pretty calculator that looks like a form and the second that contains all of the data fields for the dropdown lists that I have on the first sheet (employee names, their billing rates, job types, etc). I've figured out how to use an IF statement but I need to be able to select from 20+ employees and don't want to type out a mile long IF statement in order to make it work. I've just started looking into VLOOKUP but I'm fairly confused and I'm not sure if that's what I need. Can anyone help me out with what I need to make this work?

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: I need a cell to reflect the billing rate of a name selected from a dropdown list.

    Hi,

    VLOOKUP could do the trick.

    Maybe you could upload a sample file to adjust the formula you need.
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: I need a cell to reflect the billing rate of a name selected from a dropdown list.

    Yes, sounds like you need the VLOOKUP. Let's say your calculator is on Sheet1 and your data is on sheet2. Names are in Col A starting in A2 with billing rate in column B and data goes from row 2 to 50

    In the proper cell in sheet1 with the dropdown name in C5
    =VLOOKUP(C5, sheet2!$A$2:$B$50,2, FALSE)
    Can you modify that for your use?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Contributor
    Join Date
    09-17-2014
    Location
    UK
    MS-Off Ver
    2007
    Posts
    117

    Re: I need a cell to reflect the billing rate of a name selected from a dropdown list.

    Does an employee name appear in your data more than once?
    If no, then a vlookup should work ok.

    If you can provide sample data in the format you are using (a dummy copy of your workbook), should be easy enough to do.

    Remember to change any confidential data in your example before you upload as an attachment

  5. #5
    Registered User
    Join Date
    10-31-2014
    Location
    Michigan
    MS-Off Ver
    Windows 7
    Posts
    2

    Re: I need a cell to reflect the billing rate of a name selected from a dropdown list.

    I've uploaded a template and removed personal info. On sheet 1 (Calculator) the dropdown list of employees is in column E and their rates are in Column F. I need F to auto populate the employees rate based on which employee is selected from the dropdown in column E. The employee rates are on sheet 3 (Staff Info). Their names are in Column B and rates are in Column I. For our purposes you can ignore sheet 2 (data field) as that is relevant to other parts of the calculator that I deleted for privacy. I tried playing with that VLOOKUP formula that was mentioned but couldn't quite get it to work.

    Many thanks for the comments and help!

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2019
    Posts
    1,796

    Re: I need a cell to reflect the billing rate of a name selected from a dropdown list.

    Hi,

    in F9 in Calculator Sheet


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Cheers

+ 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. Replies: 2
    Last Post: 03-31-2014, 01:32 AM
  2. [SOLVED] Macro to jump to a cell that matches the text selected from a dropdown list.
    By agalvan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-24-2014, 06:28 PM
  3. Clear cell contents based on value selected in a dropdown list
    By dpleventhal in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-01-2013, 04:45 PM
  4. Remove selected options from dropdown list
    By Badking333 in forum Excel General
    Replies: 15
    Last Post: 11-26-2013, 10:49 PM
  5. How to create excell chart to reflect attendence rate?
    By janiss in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-18-2011, 02:24 AM

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