+ Reply to Thread
Results 1 to 13 of 13

Drop Down List to Autofill cells

  1. 06-19-2014, 05:25 PM #1
    hstiller
    hstiller is offline
    Registered User
    Join Date
    06-19-2014
    Location
    Indianapolis, IN
    MS-Off Ver
    2007
    Posts
    9

    Drop Down List to Autofill cells

    Hello!!

    I am not sure if this is possible but, I would like to auto-fill cells based on what I select from 2 separate drop down lists.

    For Example:

    1st Drop Down List is located in A1, B1, C1...(already created). Let's say it lists names like: Tom, Ceci, Kilee, etc...

    2nd Drop Down List is located in A2 (already created). Let's say it lists events like : 60 minute, 30 Minute, Class...

    Now, is it possible to select "Tom" from the 1st drop down list, and "60 Minute" from the 2nd drop down list, & have the adjacent cell populate a specific number I have designated for "Tom, 60 minute" on sheet 2? Also, is it possible to have this happen every time I select a different item from each drop down list for the entire column?

    I have uploaded a sample workbook as well.

    Thank you,

    Hannah
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 06-19-2014, 05:30 PM #2
    gak67
    gak67 is offline
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Drop Down List to Autofill cells

    Try this in C7:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.
    Reply With QuoteRegister To Reply
  3. 06-19-2014, 05:32 PM #3
    gak67
    gak67 is offline
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Drop Down List to Autofill cells

    The above assumes that the different trainers have different rates. Your sample data does not reflect that. You don't need as complex a formula if it's all the same.
    Reply With QuoteRegister To Reply
  4. 06-19-2014, 05:46 PM #4
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Drop Down List to Autofill cells

    yep see attached
    formula is
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    edit since the jobs for everyone are the same you can just use the list below tom and use this shorter formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    i presume later everyone will have different fees
    Attached Files Attached Files
    Last edited by martindwilson; 06-19-2014 at 05:54 PM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  5. 06-19-2014, 09:40 PM #5
    hstiller
    hstiller is offline
    Registered User
    Join Date
    06-19-2014
    Location
    Indianapolis, IN
    MS-Off Ver
    2007
    Posts
    9

    Re: Drop Down List to Autofill cells

    This is exactly what I needed!!

    Would I be able to use this same formula if I added names to the list of trainers in the future?
    Reply With QuoteRegister To Reply
  6. 06-19-2014, 09:42 PM #6
    hstiller
    hstiller is offline
    Registered User
    Join Date
    06-19-2014
    Location
    Indianapolis, IN
    MS-Off Ver
    2007
    Posts
    9

    Re: Drop Down List to Autofill cells

    Also, correct. Each trainer will have different fees in the future, so the longer formula is necessary
    Reply With QuoteRegister To Reply
  7. 06-19-2014, 10:16 PM #7
    gak67
    gak67 is offline
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Drop Down List to Autofill cells

    Quote Originally Posted by hstiller View Post
    Would I be able to use this same formula if I added names to the list of trainers in the future?
    If you replace the "New Hire" in row 1 of the Trainer & Fee Ref. List tab it will update the dropdown list. If your list goes beyond column Z you would need to update the formula for your data validation list.
    Reply With QuoteRegister To Reply
  8. 06-19-2014, 10:25 PM #8
    hstiller
    hstiller is offline
    Registered User
    Join Date
    06-19-2014
    Location
    Indianapolis, IN
    MS-Off Ver
    2007
    Posts
    9

    Re: Drop Down List to Autofill cells

    So sorry, one more question. I checked each of the session types from the drop down lists, and the only one that is not auto filling is the "Office Work" choice for any of the names in the trainer drop down list.

    I really appreciate your help!
    Reply With QuoteRegister To Reply
  9. 06-19-2014, 10:27 PM #9
    hstiller
    hstiller is offline
    Registered User
    Join Date
    06-19-2014
    Location
    Indianapolis, IN
    MS-Off Ver
    2007
    Posts
    9

    Re: Drop Down List to Autofill cells

    So sorry, one more question. I checked each of the session types from the drop down lists, and the only one that is not auto filling is the "Office Work" choice for any of the names in the trainer drop down list.

    I really appreciate your help!
    Reply With QuoteRegister To Reply
  10. 06-19-2014, 10:33 PM #10
    gak67
    gak67 is offline
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Drop Down List to Autofill cells

    It works for me with the formula as I posted it in the sample sheet you uploaded. Can you upload again with it not working and I'll see if I can work out why.
    Reply With QuoteRegister To Reply
  11. 06-19-2014, 10:52 PM #11
    hstiller
    hstiller is offline
    Registered User
    Join Date
    06-19-2014
    Location
    Indianapolis, IN
    MS-Off Ver
    2007
    Posts
    9

    Re: Drop Down List to Autofill cells

    Yep! Here is a more recent version...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 06-19-2014, 11:26 PM #12
    gak67
    gak67 is offline
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Drop Down List to Autofill cells

    You have changed your data validation list. It used to refer to the Trainer & Fee Ref. List tab and now it is a list you have typed up. The reason it is not working is that in the Trainer & Fee Ref. List tab it is "Office Work " and in your list it is "Office Work". There a 3 ways I can think of to fix it. 1) add the trailing space to the data validation list, 2) remove the trailing space from the Trainer & Fee Ref. List tab or 3) use a reference to the data in the Trainer & Fee Ref. List tab as your data validation list (I would also remove the trailing space just to tidy it up). I have done a tidy up of the data for you and reworked the formula to match. See attached.Payroll Tracker (2).xlsx
    Reply With QuoteRegister To Reply
  13. 06-19-2014, 11:43 PM #13
    hstiller
    hstiller is offline
    Registered User
    Join Date
    06-19-2014
    Location
    Indianapolis, IN
    MS-Off Ver
    2007
    Posts
    9

    Re: Drop Down List to Autofill cells

    This is exactly what I needed. That you for your help and for clarifying my confusion!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 to Autofill cells
    By jdominga in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-30-2014, 02:58 AM
  2. Drop Down List Autofill
    By Rita in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  3. Drop Down List Autofill
    By Debra Dalgleish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 09:05 AM
  4. Drop Down List Autofill
    By Rita in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  5. Drop Down List Autofill
    By Rita in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-24-2005, 03:38 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1