+ Reply to Thread
Results 1 to 11 of 11

Creating a drop down list excel

  1. #1
    Registered User
    Join Date
    08-12-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Creating a drop down list excel

    Hi,

    Im a newbie and trying to figure out how to do a hopefully a simple task within excel.

    I was wondering if there are any templates or if someone could help me make a template for the thing i need.

    I have a list of 60 people at work that all have to do 3 seperate tests every 6 months each one has differnt dates for the tests..

    and was looking for a drop down list of the people i can select for the month coming up to print out and has the dates tests are due on.. for the 3 seperate tests.. and if possible if the test is coming up withing a month it turns yellow. and if it is over due it turns red..

    hope that makes sense..

    Hope to hear from you soon..

    Many thanks

    Robin

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

    Re: Creating a drop down list excel

    Dropdown lists can be created using Excel's Data Validation capabilities and the coloring of the cells can be done with conditional formatting. Hard to give you more unless you upload (Go advanced>Manage attachments) a spreadsheet showing how you want your data organized.
    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

  3. #3
    Registered User
    Join Date
    08-12-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Creating a drop down list excel

    Quote Originally Posted by ChemistB View Post
    Dropdown lists can be created using Excel's Data Validation capabilities and the coloring of the cells can be done with conditional formatting. Hard to give you more unless you upload (Go advanced>Manage attachments) a spreadsheet showing how you want your data organized.
    Thanks i have attached an example of what im after but no idea how to do it..

    Many thanks

    RObin
    Attached Files Attached Files

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

    Re: Creating a drop down list excel

    So on each of those 3 other tabs, there will be a vertical list of the 60 names (say Col A) and next to it, the due dates for that specific test?

  5. #5
    Registered User
    Join Date
    08-12-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Creating a drop down list excel

    yes thats correct.. sorry forgot to do them..

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

    Re: Creating a drop down list excel

    Also, all of your dates are entered as text in your example. Is that the way they are in your workbook? (i.e. with 22nd and 1st, etc). That makes things more difficult since Excel compares numbers to see if a date is approaching or past. :/

  7. #7
    Registered User
    Join Date
    08-12-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Creating a drop down list excel

    no dates can be put at 12/2/2010 sorry just habbit of writting dates like that

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

    Re: Creating a drop down list excel

    Okay, Step 1, the dropdown list
    Go to one of the other sheets with names and select the entire name list.
    In the "name box" ( the little white box where it will probably say "A2"), delete what's in there and type NameList. Then hit enter.
    Now select the range in "Main" where you want the dropdown and select Data Validation (under data menu). Choose "List" and type in = NameList
    There should be dropdowns of all the names now.

    Step 2. Pulling the dates
    You'll use VLOOKUP. In Main!C12 of your example, it would be
    =VLOOKUP(B12,FIRE!$A$2:$B$71,2,FALSE)
    That can be dragged down. (The $ lock the cells in place so the range doesn't change as you drag or copy).
    Do the same with D12 and E12. The tab in D12 contains a space so will need to have single quotes around it like so 'think 21'!
    Format the cells as dates if you need to.

    Step 3. Conditional Formatting
    (to be continued)

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

    Re: Creating a drop down list excel

    Conditional Formatting
    Select your date range in Main (in your example C12:E13). Go to Conditional Formatting (I think under formatting menu). Select Formula and type in

    =C12-TODAY()<=0 then select the formatting (red fill, bold font)

    Repeat for condition 2 with
    =C12-TODAY()<= 30 with yellow fill and bold font
    30 will be 30 days, adjust as you like.

    Does that make sense?

  10. #10
    Registered User
    Join Date
    08-12-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Creating a drop down list excel

    Thnaks for getting back to me.. but im not having any luck with it think i have gone wrong somewhere.. are you able to maybe do an example one for me, and i can change the details slightly?

    Thanks again

    Robin

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

    Re: Creating a drop down list excel

    Okay, here's your file back at ya with the changes I made.
    I added the conditional formula for Green (>30 days) and fixed the other two because

    =C12-TODAY()<=0 (for example) would turn a cell red even if blank.
    so the formula is now =AND(ISNUMBER(C12),C12-TODAY()<=0)

    I could guide you if you were more specific as to what was "not working." Let me know.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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